Monday, August 29, 2011

Crystal Reports : Displaying the Day of Week from a Date

A client asked: I would like to display the day of the week on the detail line along with the date. How do I do this?

Using the example of CREATEON in Goldmine Contact1, you would do the following:

Create a formula using the DayOfWeek function called DayPosition as follows. This will create the numerical position of the date (e.g. 1=Sun, 2=Mon, etc)

DayOfWeek ({Contact1.CREATEON})

Then create a formula called DayName. This will add the name to the report (Mon, Tue, Wed). Use the function WeekDayName and add the DayPosition as the function detail

WeekdayName ({@DatePosition})

Drag the DayName formula to the detail row next to the CREATEON date. It will list the weekday name of the CREATEON date.

Crystal Reports : Enabling Landscape Size

This happened to me the other day. I went to File Printer Setup and the Landscape option was greyed out.
I found the default printer to be the "Microsoft XPS Document Writer". Once I changed it to an actual, physical printer, the Landscape option was available again.

Crystal Reports : Viewing the SQL Query

Every Crystal Report (Selection Criteria) can be rendered into it's constituient "SQL Query".

Note: this will only work on SQL Servers.

Once your report is in place and working as desired, select (from the top-level menu)
Database SQL Query.

You will get a little window that contains the SQL Code used to select the records from the database. You can copy and paste this text into, say, a SQL Query Designer. This is incidentally also a nice way a build SQL Queries using Crystal Reports.

Crystal Reports : Formatting fields as Currency

Any numeric field can be formatted as "currency" by:
1. Right-click on the field itself
2. Select "Format Field"
3. Go to the "Common" tab
4. Select your Style (i.e. thousands seperator, etc)
5. Click the checkbox labeled "Display Currency Symbol"

Crystal Reports : Suppressing and/or Revealing Report Sections

While designing a report, you may find it helpful to Suppress (hide) sections, easily Revealing them later.
Within Crystal, right-click on the Section name on the left hand section of the designer.
Then select Hide or Suppress, depending on your need.
Hide will still allow you to "Drill Down" and see the section in question.
Suppress will not allow you to reveal the section at all until you right-click on it and select Show.

QuickBooks: Aging Reports – Due Date vs. Invoice Date

I’m Using QB Pro 2010. When I receive a bill from a vendor, I enter it into the Accounts Payables as of the invoice date and set the terms for that vendor, i.e. Net 30 and the due date. However, on the Unpaid Bills report I noticed that it ages from the invoice date, not the due date. Is there any way to show aging from the due date?

Yes there is.
Go to Edit>preferences>reports and graphs>company tab>mark bubble for age from due date.

QuickBooks: Internal Spoilage Tracking by Job

I recently learned of a printer that must sometimes re-run jobs for picky customers. His expenses are already counted in the bottom line (payroll, toner, paper, utilities), but he wanted to track the estimated costs of these spoiled jobs so that he can see which clients are too much trouble to keep. He knew that any of the extra costs related to the job would inflate the client’s expenses.

The solution:
- A new item was created called “Spoilage”
- It was linked to a revenue account that never gets used.
- The item had a price of $0.

Every time they re-ran a job for a customer, they would issue an internal sales receipt for that customer, citing the item “Spoilage” and the quantity being the estimated dollar amount of the extra cost

These sales receipts did not affect the receivables or statements, and he was able to run Sales by Customer Detail reports filtered just for this Spoilage item. The Qty column provided the internally-calculated estimated costs of these spoiled jobs.

QuickBooks: Request Funds Via Pending Sales Receipts

Some clients must send out and track requests for deposit funds from clients. When the funds come in, these clients post the funds as a sales receipt.

Some clients already uses estimates and sales orders for other purposes, and needed an easy workaround to track deposit requests.

Here is a simple method:
- Record deposit requests as pending sales receipts, with the “due date” field renamed “request date.” See an example in the screen shot below.
- When the funds arrive, the pending sales receipt is marked “final” and the date of the receipt is changed to the date that the funds arrive while the “request date” remains unchanged

QuickBooks: Print Modified Invoices Without Saving Changes

This is one of those situations where a software company can’t win. People complain about the way it works, so you change the program to fix the problem. Then, other people complain about the fix, and wish it worked the old way. Fortunately, Intuit made this an option so you can have it either way. The issue? Being able to make a change to an invoice and print it without saving the change.

Current versions of QuickBooks are set up, by default, to always do an automatic “save” of an invoice (etc.) before you can print it. A few years ago you weren’t forced to do this, you could make a change and print it, but then not save it.

There are many reasons why you would NOT want to allow this….. specifically, employee bad behavior.
• An employee creates a bogus invoice and prints it, but doesn’t record it. Hand the invoice to a customer, who pays in cash. Employee pockets the payment and the company never has a record. Of course, there are SEVERAL problems here (accepting cash like this?), but there are businesses that run this way.
• An employee creates a valid invoice and saves it. Then some changes are made, to either add services or change the rate. The customer pays the altered amount and the employee pockets the difference.

To help protect the companies from this employee bad behavior Intuit changed QuickBooks to make it automatically save before printing.

However, some people complained about it and ask how to get around it. So, for those of you who need the ability to print a modified invoice without saving it, use the following steps:
1. Log in to QuickBooks as the Admin user.
2. Select Edit then Preferences and select the General preference.
3. Select the Company Preferences tab, and un-check the box Save transactions before printing.
4. Click OK to close the window.

QuickBooks will warn you that this isn’t secure, but it will let you make the change.

QuickBooks: How to record a vendor refund

A new client in the construction industry asked how to enter a vendor refund in QuickBooks to offset the credit that they have on their books.

The Situation:
The construction industry has to use the payables system to track the multitudes of invoices and credit issued by suppliers. They then compare the supplier statement with the balance in the payables each month to confirm accuracy.

In this case, the construction client was paid in full to a supplier. They then returned product which resulted in a credit memo being issued by the supplier. They recorded the credit. They also stopped purchasing product from the vendor, and requested a refund for the return. The vendor issued a check.

The following entry will clear the vendor credit and record the deposit
- From the banking menu, choose record deposit.
- Enter the vendor refund check using the vendor name in the transaction and Accounts Payable in the “from account box”
- Then go to vendors - pay bills - click on the "bill" that was created by the vendor deposit
- Apply the vendor credit memo to the "bill" created by the transaction above.

This will result in a zero transaction (the deposit will clear the vendor credit).

Saturday, August 27, 2011

MSCRM 2011: Adding your Outlook Contacts to Microsoft Dynamics CRM 2011

A client recently asked, “How can we get our Outlook Contacts into Microsoft Dynamics CRM 2011?”

After the Microsoft Dynamics CRM 2011 Outlook client is installed and configured, you can use the Add Contacts Wizard to copy your Outlook contacts to Microsoft Dynamics CRM 2011.

The following information was found on the Microsoft site:

The Add Contacts Wizard takes your Outlook contacts and creates contact records in Microsoft Dynamics CRM. The wizard also links your Outlook and Microsoft Dynamics CRM contact records.

Outlook does not include different contact forms for people and businesses. You enter both in the same type of contact form. In Microsoft Dynamics CRM, information about people and information about businesses are kept in different types of records:

*Contact. These represent individuals who purchase products or services, or who are employees of accounts. A contact may also be a person who is involved in a business transaction, such as a supplier or a colleague.
*Account. These represent companies that your organization does business with, either as customers or suppliers. Accounts can have contacts and sub-accounts associated with them and can be used to track competitors and vendors.

The Add Contacts Wizard adds your Outlook contacts as Microsoft Dynamics CRM contact records.

Before you start adding your contacts to Microsoft Dynamics CRM, do some optional organizational and clean-up tasks in Outlook to make it easier for you to select contacts. This also helps make sure that Microsoft Dynamics CRM adds the information correctly. Perform all of these tasks in Outlook:

* Set up color categories and use them to sort your contacts. At least use categories to mark your private contacts or mark contacts that you do not want to add to Microsoft Dynamics CRM. Or, you could use categories to group contacts that you want to have in Microsoft Dynamics CRM that are not from one company or domain.
* Verify that you used the name and spelling in the Company field consistently.
* Add a new Contacts folder to sort your contacts. For example, use a folder to hold all of your contacts that are businesses and not people.
* Remove or merge duplicate entries.
* Add contacts from other e-mail systems, such as Hotmail.
* Mark your private contacts as private.
For more information, search Outlook Help for "color categories," "contacts," and "duplicates."

To run the Add Contacts wizard:
1. If you have Outlook 2007 installed, on the CRM menu, click Add Contacts.
If you have Outlook 2010 installed, click File. Then, click the CRM tab, click Import Contacts, and then click Add Contacts.
2. Review the overview, and then click Add Contacts.
3. If you have more than one contacts folder, select which Outlook contact folder you want to use and click Add Contacts. Otherwise, go to the next step.
4. Select how to group your contacts.

* Company Name. This is a free-form text field in the Contact form. Grouping requires an exact match. For example, Microsoft Dynamics CRM will not group together contacts with "Microsoft" and "Microsoft Corp" for their company.
* E-mail Domain. This is the part of an e-mail address after the @ symbol. For example, in the e-mail address, "" is the domain. You can group all of contacts with e-mail addresses with the same domain together.
* Categories lists any colors and labels that you have set up to organize items in Outlook.

5. By default, Advanced options are shown.

* Use Company Name to be the Account. This check box is selected by default. If the Outlook contact record contains a company name, Microsoft Dynamics CRM creates or links an existing Microsoft Dynamics CRM account record to the contact record.
* Track all communications for added contacts. Select this check box if you want to track and link e-mail messages to and from the contact to the Microsoft Dynamics CRM contact record. This also links appointments.

6. If you want, click Advanced to switch to the Basic Import options.
7. Clear the check boxes of the items or groups of contacts that you do not want to add.If you want to review the list of specific contacts, click the item or group in the list. When you are done, click the Close button.
8. If you selected the Basic Import option, you can select an account record to link to the contact record in Microsoft Dynamics CRM. In the Set Account column, click the row for the contact record that you want to link. Then, click the arrow to make the link one of the following ways:

* Leave the field blank (Not Set). If you want link the contact with an account record later in Microsoft Dynamics CRM, open the contact record and then select an account or parent customer record to link to the contact record.
* Click Use Company Name to have Microsoft Dynamics CRM create and link an account record based on the company name field of the Outlook contact record.
* Click Pick Existing Account to select an account or contact record to link to the new Microsoft Dynamics CRM contact record.

9. If you selected the Basic Import option, in the list, under Include Communication, clear the check box of each item if you do not want to track and link e-mail messages and appointments to and from the new contact record in Microsoft Dynamics CRM.
10. Click Add Contacts.Microsoft Dynamics CRM adds your contacts. A summary page displays showing the total number of added contacts, accounts, e-mail messages, and appointments.
11. Click Close.

MSCRM 2011: Creating a Dialog Process

In Microsoft Dynamics CRM 2011, there is now a new process that can be defined called a Dialog.

Dialogs present a wizard-like UI and always require user input to start and run to completion. They always run synchronously. They cannot be triggered automatically. They must be started by a user. They can be run on one record at a time and can call child dialogs and pass information to them.

Dialogs provide the ability to create wizard-style processes inside Dynamics CRM. Here are a few examples of what you can do with dialog processes:
* Create guided selling or service processes, with a series of questions guiding representatives through otherwise complex sales/service processes.
* Create wizard-style substitutes for traditional forms-based record creation.

The basic constructs of the new Dynamics CRM 2011 dialog processes are one or more pages, which provide the visual experience for the user. Each page can contain multiple sets of prompts and responses. Prompts and responses always go together. Pages, and prompt/response pairs, are the most fundamental difference between workflow and dialog processes: they present the user interface and gather the information that the rest of the dialog process can use to do whatever it needs to do.

In the example below we will create a simple dialog to create a case record. The dialog process will be written for the Account entity, with the goal of simplifying the process of creating a new case record for an account that already exists in your Dynamics CRM.

1. Click Settings on the site map, and then click Processes in the Process Center section.
2. Immediately above the Processes grid, click New.
3. In the Create Process dialog, provide the following information:

a. In the Process Name field, type Create Case for Account.
b. In the Entity drop-down, select Account.
c. In the Category drop-down, select Dialog.
d. In the Type section, select the New blank process option.

4. Click OK. The Process Design form opens.

Note: There is no Options for Automatic Workflows section. This is because dialogs must be started by a user. The As an on-demand process option is selected by default. If you de-select that option, and save and close the dialog…you will notice when you open it again that it’s been selected again.

Adding Pages, Prompts and Responses
1. In the Step Editor, click the line that says “Select this row and click Add Step“.
2. Click the Add Step drop-down, and select Page. A Page component will be added.
3. With the cursor on the highlighted “Select this row and click Add Step” line, click the Add Step drop-down, and this time select Prompt and Response.
4. Click Set Properties. The Define Prompt and Response dialog appears. Provide the following information:

a. In the Statement Label field, type Case Title.
b. In the Prompt Text field, type Enter a title for the case.
c. In the Response Type drop-down, select Single Line.

5. After entering the information, click Save and Close. The dialog closes and you return to the Step Editor. Notice that the text you entered in the Prompt Text field is automatically added in the Description field for the step. You can change it in either the Define Prompt and Response dialog or the Step Editor.
6. Click Add Step, and select Prompt and Response again. This time, provide the following information in the Define Prompt and Response dialog:

a. In the Statement Label field, type Case Description.
b. In the Prompt Text field, type Enter a description for the case.
c. In the Response Type drop-down, select Multiple Lines (Text Only).

7. Click Save and Close. You will return to the Step Editor.

Creating a Record with Information Gathered in a Dialog Process
1. Click on the Page step, and click inside the text box, where it says “Type a description here”.
2. Type Gather Case information.
3. Make sure the Page step is still selected (the entire Page block should be highlighted in blue), then click Add Step and select Create Record.
4. Click the drop-down to the right of Create and select Case.
5. Click Set Properties. The Process form editor opens.
6. With the cursor in the Title field, click the Look for drop-down in the Dynamic Values section of the Form Assistant, and select Case Title in the Local Values section of the list.
7. Click the Add button in the Dynamic Values section.
8. Click OK. The Title field is populated with Response Text(Case Title), which when the dialog runs will contain the text the user entered when prompted for Case Title.
9. Click inside the Customer field. In the Look for drop-down, select Account, which is the primary entity for the dialog process. Notice that Account is automatically populated to the second drop-down list. Remember we are selecting a value for the Customer lookup field on the Case form, and the only values that can be entered are account or contact lookups.
10. Click the Add button.
11. Click OK. The Customer field is populated with Account(Account). This is process design notation for “the account field (from the account entity)”.
12. Click inside the Description field. In the Look for drop-down, select Case Description in the Local Values section, and then click Add.
13. Click OK. The Description field is populated with Response Text(Case Description).
14. Click Save and Close to return to the Step Editor.
15. Click inside the Create action’s Description field, and type New Case.
16. Click Save and Close, to return to the Processes grid. With the Create Case for Account dialog selected, click Activate.
17. In the Process Activate Confirmation dialog, click OK, and your new dialog is ready to run! Go to a specific Account record and select Run Dialog.

MSCRM 2011: Using Quick Find to find records in a View

Even with the sorting features in views, sometimes it can be time consuming to manually look for a particular record, especially if the view contains a large number of records. To help address this concern, Microsoft Dynamics CRM includes a Quick Find feature that allows you to search for records by using keywords or wildcard characters. You can find the Quick Find search box above the grid and to the right of the view selector. To use it, enter a search phrase and press Enter on the keyboard or click the button with the magnifying glass to start the search. Even though Quick Find is simple to use, there are a few tips and tricks that will help you find records more efficiently.

*Your system administrator can configure Microsoft Dynamics CRM to search for matching records across multiple columns. For example, you could search for particular contacts by name, phone number, or email address. You can even include custom data fields as part of the search criteria.

*When you enter search text, Microsoft Dynamics CRM will search for the value as it is entered. By default, it will not search for partial records. For example, if you search for a phone number by entering 555-1212 and the contact’s phone number is (312) 555-1212, Microsoft Dynamics CRM will not consider that a match. It will return only those records that have 555-1212 as the start of their phone number.

*Of course, there will be times when you don’t know the exact value you’re searching for. In these cases, you can enter an asterisk (*) as a wildcard character in your Quick Find search. In the previous example, if you did not know the exact phone number, you could search for *555-1212 and Microsoft Dynamics CRM would find the (312) 555-1212 matching record, plus any other records that ended with 555-1212.

You can enter the wildcard character anywhere in your search criteria: at the beginning, in the middle, or at the end. If you can’t find the record you’re looking for, be sure to try different combinations with the asterisk wildcard. Note that the Quick Find feature is not case sensitive in its searches.

*If you start a Quick Find search when you’re working with a specific view, such as My Active Contacts, you might expect that Microsoft Dynamics CRM would search for matching records only within the My Active Contacts view. However, Quick Find always searches for matching records across all active records for that entity. Quick Find ignores inactive records.

To filter records within a specific view, you can click the letters that appear at the bottom of the view (also referred to as the index bar). Clicking a letter will update the view to show only those records whose entry in the current sort column starts with the selected letter. For example, if you’re looking at the My Active Contacts view with the records sorted by City and you click B in the index bar, Microsoft Dynamics CRM will show you only those records in which the city starts with the letter B. If you then click the Full Name column to sort by that field and click the letter C in the index bar, Microsoft Dynamics CRM will update the My Active Contacts view to show only those records in which the Full Name entry starts with the letter C.

Friday, August 26, 2011

HEAT – Import and Export Auto Tasks

You can import AutoTasks that have been exported to a HEAT Export File (.hef), so that you can share AutoTasks between systems. A .hef file can contain one or many AutoTasks.

You can also use the Import feature to create and test AutoTasks on a test system before implementing them in a live system.

To Import an AutoTask:

  1. From the Call Logging menu bar, select AutoTask>Import. The Import AutoTask dialog box opens. 
  2. In the View section, select the View (audience) for whom you will be importing the AutoTask. If you click Team, select the appropriate team from the drop-down list.
  3. In the list of AutoTasks (filtered by View), select the folder to which you will import the AutoTask.
  4. Click Browse to navigate to the .hef file to be imported.
  5. Click Import.
Note: If you choose to import an AutoTask that already exists in your system, HEAT checks for any differences in the AutoTasks. If no changes are detected, HEAT automatically copies over the original AutoTask. If a difference is detected, HEAT prompts you, asking if you want to: import over the original, create a new AutoTask (with an appended name), or cancel the operation.

HEAT – Using the Favorites Tab in the Info Center

Use the InfoCenter Favorites tab to quickly access the Call Records, Call or Customer Groups, AutoTasks, files, or Internet links you frequently use. Favorites can be organized into folders.

Available Functions

Right-click on the Favorites tab to add favorites, organize favorites (including adding folders, moving, renaming, and deleting favorites or folders).

To Add Favorites

  1. The Favorites tab is initially empty. Right-click and select Add to Favorites. The Add a Favorite dialog box opens. 
  2. Type a name for the favorite. 
  3. In the Create in field, specify a folder or click New Folder to create a folder. 
  4. Select the type
  5. Type a description (optional). 
  6. Click Add.

HEAT – Setting a custom background

The picture function is a background effect, placing a picture on the background of the form.

HEAT supports the following file types for images and pictures: .jpg, .bmp, .gif, .png, and .tif. A user must possess the appropriate security right (Custom Image Import) to add pictures.

To Add a Picture to the Form Background:

  1. Open the form with which you want to work. The Form Editor opens, displaying the form.
  2. Right-click the form and select Format drawing. 
  3. Click the Fill Effects button, then click the Picture tab. 
  4. Deselect the No picture fill effects check box.
  5. Click the Select Picture button, navigate to the desired picture, then click Open. The picture previews in the Picture section, with the file path displayed below it. You can also see the picture in the lower right Sample section. The Sample will change depending on the options selected below the picture. 
  6. Modify the picture as needed using the options below the picture and the Image Control button: 
  • Clip picture to shape - Resizes the picture to the size of the form (picture may appear distorted or may be cropped). 
  • Clip picture aspect ratio - Maintains picture proportions and resizes to the form size (picture may be cropped). 
  •  Keep picture original size - Tiles the picture (picture may be cropped). 

7. Click OK to close the Fill Effects dialog box.

8.  Click OK. The picture displays on the background of the form.

HEAT - Set Tab Order on Forms

You can set a tab order for controls to specify the order in which a user proceeds through controls when the Tab key is pressed.

To Set Tab Order:

  • Open the form with which you want to work. The Form Editor opens displaying your form.
  • From the Administrator menu bar, select Form>Set tab order. A small box with a number appears next to each control on the form. Click once on the control you want as your first entry (the number changes to 1), then proceed through the controls until a tab order is assigned.
  • You can test the tab order by selecting View and Test.

Thursday, August 18, 2011

GoldMine : Setting the Default Search Field

Once a user is comfortable using GoldMine, they typically search for Contacts using the same field. I personally use Contact. Others prefer LastName. Some even like to use Company.

A nice touch is to set your default Search By field, so GoldMine "remembers" the last field you searched.

1. Select Tools | Options
2. Select the Lookup tab
3. Select "Remember lookup by", next to "Default lookup by field"
4. Ok your way out

Now GoldMine will remember the last Search field you used, even if you close and restart. Try it!

GoldMine : Using the Lookup.Ini to fill in "default" values

One of the most powerful yet under-leveraged functions in GoldMine is the LOOKUP.INI, which has been available since the good old days of GoldMine for Windows. Think of it as a script file that GoldMine uses to update fields automatically. A "poor-mans" SQL trigger, if you will. It can also be used to "default" values on new contact records.

Let us suppose we want to make every newly entered record a "Prospect" by putting that value in our Key1 field.

If you have no LOOKUP.INI, then we must create one. Open Notepad and paste in the following text:



The NewRecord entry under the [AutoUpdate] header specifies what fields should be evaluated when a new record is created.

Then the process skips to [Key1], the first item in our list.

When evaluating Key1, GoldMine then uses the Otherwise value, as no other instructions exist under [Key1].

As Otherwise=Prospect, the value of "Prospect" is autofilled into the Key1 field upon record creation.

You can add more default fields/values to your Lookup.Ini thusly;

NewRecord=Key1, Key2


Otherwise=House Account

When you are finished creating your Lookup.Ini, save it into your GoldMine folder with the specific name "Lookup.Ini", then restart GoldMine to affect the changes.

ProTip: Lookup.INI instructions are also processed when importing contact records.

GoldMine : Using the Lookup.Ini to auto-fill conditional values

Let us consider the Key1 update. Let us now suppose we want to auto-fill the name of whoever created the record based upon who was logged into GoldMine at the time. And since GoldMine usernames can only be 8 characters long, we sometimes want to see the "real name"

This is how it's done;


JHILL=Justin Hill
CBABKA=Corey Babka
GMARKS=Gene Marks

Again, our NewRecord entry triggers the Key1 "block".

Then GoldMine "looks at" the &username macro (which returns the GoldMine username currently logged in).

Then, based upon THAT value, returns an item in the list. If no such username is found, our Otherwise is a "catch-all", marking them as Unknown.

ProTip: For a list of GoldMine dBase expressions, Google "GoldMine dBase Expressions" and look for "Using dBase Expressions" by FRS.

GoldMine : Using conditional colors in GoldMine fields

One of the truly unsung features of GoldMine is the ability to conditionally change a data value color based on what the data value is.

Let us take the Source field for example. We'll imagine that if a Contact Records' Source field is set to "Direct Mail", then it should be in red.

We need to first get into the Field Properties:

1. Right-click on the Source field (on the word "Source", not inside the field)
2. Select Field Properties (your GoldMine user must have "master" rights to do this)
3. Go to the Color Tab

Now we need to provide the expression to control the color based on Field Value.

4. Click on the Expression radio button near the bottom, under "Data Color"
5. Paste the following text into the Expression textbox:

iif(CONTACT1->source="Direct Mail", 255, 0)

6. Ok your way out

Now, whenever "Direct Mail" is chosen for Source, the Field Value will be in red.

ProTip: The "255" represents the color red in the above expression. To see the numbers for the other colors, simply use a "Fixed Color". Select the color you want; you'll notice that the corresponding color number is displayed in the Expression textbox.

GoldMine : Quoting only part of an email while responding

I love GoldMine e-mail. Having a running conversation in a clients' History tab is invaluable to me. But sometimes an e-mail conversation can run long and every reply I make quotes the entire body text by default.

Did you know that if you select a certain block of text in an e-mail body, GoldMine will only quote that in the response? Try it!

1. Open an e-mail from your Inbox
2. Before you hit Reply, select the body text you'd like to see in the response
3. Now hit Reply

You'll notice that your Reply e-mail is now only quoting the selected text.