Tuesday, December 28, 2010

Crystal Reports : Exporting to Excel cleanly

To export to Excel, click on the little "envelope" icon on the toolbar.
Select Destination: Disk File
Now drop down the Format list box. You'll see two choices for Excel. One should read "Excel", the other, "Excel (Data Only)"
In general, Crystal does a great job of exporting to a spreahsheet. However, it does take some pretty serious liberties with cell spacing and sizing. But if you're looking to act upon the data in the spreadsheet after exporting, you should try the Data Only format, as it will export none of the cosmetic touches of the report.

Crystal Reports : Using an (all) selection as a parameter

Let us consider our {?UserID} parameter, which accepts multiple values.
Let us further consider that the end-user will need to occasionally print the report for all UserID's. They could simply add each UserID to the parameter before printing the report, but in cases of many UserID's, this becomes cumbersome.
A better solution is to give an "all" option in the parameter choices, then use the following code:

if {?UserID} not like "*all*" then {SALES.USERID} = {?UserID} else true

Here is what's happening:
IF our parameter DOES NOT have he word "all" within it, we execute the Selection Criteria normally. The trick here is that our ELSE clause ends in TRUE, which means that we "skip over" this part of the Selection Criteria, therefore returning ALL UserID's.

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 : Formatting percentages

When creating a formula that should return a percentage value, you will need to format the field correctly to get the "%" sign to properly show.
Right-click on the field in question, select Format Field.
Click the "Display Currency Symbol" checkbox.
Click the "Customize" button.
Click the "Currency Symbol" tab.
Change your currency symbol from "$" to "%".
Then change the "Position" to "-123%".
This forces the percentage symbol to appear at the end of the numerical value.
This will give you percentages thus: "87.22%".

GoldMine : Number of records to display

What I've observed when clients upgrade to the "new" GoldMine (8.X and above), they sometimes need to change the Number of Records to Display.

The reason being is usage habits, usually. If an end-user is doing a lot of scrolling through long lists (versus searching for more particular values), they may be getting to the end of their "lists" and thinking that contacts are missing.

Go to Tools | Configure | System Settings.

Click on the "Display" tab.

Increase the number of "Maximum records shown in contact search center..."

The default is 2000. You can change it to whatever you like, just be mindful higher values may cause a performance degradation.

GoldMine : GoldMine Reports and the last person to touch them

It's not often I run into clients that use a lot of GoldMine Reports, by which I mean the reports available through Go To | Reports | Reports. But those that use them, use them a lot.

Youc an also change the default behavior of any report by:

Right-click on the report and select "Properties".

Go to the "Options" tab. Depending on the type of report selected, this tab will let you specify things like Username, Activity Types, and Date Ranges. Feel free to experiment with this, but what you need to realize is that these configuration settings persist. So, the next person to use the same report will need to go to Options again and select parameters appropriate to their need.

GoldMine : Contacts without e-mails

In retrospect I should have included this in last month's tips as I'm sure some of you sent out Christmas communications.

The veterans out there immediately understand this is a somewhat difficult proposition. E-mail addresses in GoldMine are not on a "one-to-one" relationship with the contact record. What this really means is that the email address field is not available within a Filter.

So we need to build a Group based on a Query. First we will build our query.

Go to Tools | SQL Query and use the following query:


Click "Query", click through a few records to make sure the resutls are correct.

Now click on the "Groups" tab and click on the New Group button.

Give your Group a friendly name, click Ok.

Select "SQL Query Records", Next.

At this point you can click through the wizard (there are some sorting options you can experiment with) and Finish.

Once your Group is built, it is basically interchangeable with a Filter as far as GoldMine functionality goes. Keep in mind that Groups are static, however, and must be manually refreshed whereas Filters do not.

GoldMine : Setting tabstops for field order

I am a big fan of keystrokes as opposed to mouseclicks. So when I'm entering in contact information you had better believe I'm using the TAB key to navigate between fields.

To set up the TABstop order for your fields, go into Screen Design mode:

Right-click on the contact record and select "Screen Design". Your contact record should now show a grid. Single-clicking on a field will select it. Then a doubleclick will bring up your field properties, which is what we're after.

Within the Field Properties, go to the "Layout" tab. Now change the "Field Order Position".

What I find helpful is to print a screen shot of you GoldMine contact record, and then with a pen, write in on each field what you'd like the position (order) to be. Only then start editing your field properties. This way, you're less likely to get lost in your tabstop order (I know I do).

GoldMine : Viewing currently logged in users (and other licensing considerations)

Those of you with multi-user installations know; GoldMine works on concurrent licensing. Meaning that if you have 10 licenses, you can log any 10 people in at once. So, typically, open seats are precious and need to be monitored.

I find the easiest way to determine who's logged in is to go to

Tools | Users Settings

You'll notice a column in the username list that indicates if the user is logged in ("in" or "out").

Alternatively, you may go to the Calendar, then click on the "Peg Board" tab. This will also show you who is logged in, and other information, like inactivity time. Here you may also configure which users you are looking at by right-clicking anywhere and selecting "Users".

All this administration aside, I actually like the way GoldMine does it's licensing. Because it's based on concurrent usage, you have the ability to define as many usernames as you'd like. So it's often that I see "non-user" usernames, like TRUCK1 or CONF1 (Conference room) to help people schedule against things that aren't exactly people.

Monday, December 27, 2010

MSCRM: Notes in Microsoft Dynamics CRM 4.0

The Notes field enables you to enter Notes about the Entity in free form. Generally if you have standardized data about an Entity and you want to record similar information across the Entities, it is a better idea to add a new field to the Entity interface via customization rather than use the Notes. The reasons for this are:

•There is no built-in ability to query on the Notes field.
•Usually a better idea to standardize the entry (via a drop-down) and have a common location that you can control to

To enter a note, navigate to the Notes tab (generally, the last tab) on the main Information tab and select Click Here to Add a New Note. By default, the title of the note is Note created on by . To change the title, save the Note either by selecting Save from the top menu options or by clicking somewhere else on the form ( this automatically saves any entered data); then double-click the Note icon to open the Note form, where you can make changes to the title of the Note as well as the Note contents.

MSCRM: Removing Certain Members from a Marketing List

A client asked: I want to initiate a campaign for all of my customers (Accounts) in the Southwest region but I do not want to include those customers that have placed an order in the last 3 months. Is this possible?

The following steps detail the way to do this:

1. In the campaign - open the Marketing List that you are working with.
2. Click on Manage Members and click Use Advanced Find to Remove Members.
3. This will open Advanced Find. Drop down on Accounts and related (Order) created in Last three Months
4. This will then remove those accounts from your Marketing List.

MSCRM: Viewing Original Lead Notes in a Contact or Account

Sometimes it would be very helpful to be able to view the notes that were logged in a lead right in the created account or contact after a lead is qualified. Unfortunately other than going back to the original lead there is no way to easily achieve this. Following are the steps to show the originating lead’s notes in an iFrame in the account or contact screen.

1.Customize the account or contact form and add a new tab and section called Originating Lead Notes and then add an iFrame called IFRAME_LeadNotes. Set the URL of your iFrame to about:blank and uncheck tick Restrict cross-frame scripting. Set the iFrame to automatically expand to use the available space.
2.Add the following JavaScript code to the form’s onload event:

var lookupItem = new Array;
lookupItem = crmForm.originatingleadid.DataValue;
if (lookupItem != null)
crmForm.all.IFRAME_LeadNotes.src="/_controls/notes/notesdata.aspx?id="+ lookupItem[0].id + "&ParentEntity=3&EnableInlineEdit=false&EnableInsert=false";
3.Publish your changes and you are now able to view the original lead’s notes in your account or contact form.

MSCRM: Marketing Campaigns made Easy

Once you have a target marketing list created in Microsoft Dynamics CRM you can create a campaign by identifying the common items you need to track such as the type of campaign, start and end date, budget, etc. and then planning. With the CRM campaign windows there are areas to track Campaign Planning Tasks, Campaign Activities, Campaign Responses and Campaign Literature.

Campaign Planning Tasks are used to track everything that needs to be done for a campaign, and help you properly organize a campaign launch. For example, if you were attending a tradeshow to promote your product or service to healthcare organizations, you would include tasks for securing a booth location, identify collateral to print, arrange travel, etc. As a rule of thumb, most will use tasks to track anything that does not have a cost or marketing list associated with it, and instead will use Campaign Activities for tracking those items.

Although Campaign Activities are similar to Planning Tasks, Campaign Activities include more detailed information about budgets and vendors required to execute the campaign. You are also able to associate marketing lists to Activities, so you can bulk distribute Activities such as phone calls all at one. This helps save time and streamlines who is responsible for what to ensure a successful campaign.

As each Planning Task and Campaign Activity is completed the campaign begins to take shape and you can start to track the campaign progress and performance right within Microsoft Dynamics CRM. This can include tracking campaign responses, completion and status of tasks and activities, budget to actual, ROI, and more. Microsoft Dynamics CRM seamless integration with Excel also allows you to export campaign information easily and share it with others.

Another key area of campaigns within Microsoft Dynamics CRM is the Sales Literature area, where all collateral and materials that are being used for the campaign can be stored right within the campaign. Expiration dates can also be specified for the literature to ensure it is not used after the campaign is over. This helps sales teams have quick access to all the information for a campaign when it comes to follow-up, etc.

Marketing Campaigns in Microsoft Dynamics CRM can be very useful in managing the next campaign in your organization.

MSCRM: Using Automatic Mappings in Data Import

One of the great features in Microsoft Dynamics CRM 4.0 is the ability to import data from a CSV file. An easy way to load the data is to use the auto mapping function. Not only does the auto mapping feature expedite the import process, it will still allow users to map lookup and picklist values. For a lookup value, the user may identify the display name of the attribute, or its GUID, and for picklists, one may utilize the display value, or its designated integer.
Some of the important points to bear in mind when attempting to utilize the auto map functionality:

1. In order to have CRM automatically map the data for your import, make sure your import columns match EXACTLY to the attribute display name. An easy way to do this is to do a blank export from a view, or an advanced find, to create an Excel template for your source file. This will ensure you have column headings matched exactly to the attribute display names.
2. Make certain the related lookup records or picklist values exist in CRM prior to import. The import functionality in CRM will not create associated records.
3. You can use the display value or the actual value of the lookup or picklist record. For lookup relationships, the display name will be the primary attribute and the record GUID will be its value. For picklist attributes, the display name will be the name shown to users in the picklist, and its value will be an integer.
4. Make certain you have all required fields in your source data file.
5. Save your source data file in the CSV format.
6. If the display name (for either the lookup or the picklist) has duplicates, the record will not be imported. You will receive an error similar to A duplicate lookup reference was found. You can avoid this however by specifying the GUID of the related record, or the integer of the picklist value. Fortunately, only the record with the duplicate name will fail, not the entire import.

Tuesday, December 21, 2010

QuickBooks: Printing 1099’s from QuickBooks

A client just realized that it will soon be time to send out 1099’s to the vendors and a 1096 to the IRS and hoped it was a simple process in QuickBooks.

Actually, the printing is the easy part, once all the accounts are properly reviewed and mapped.

Verify your 1099 vendors
1. Go to the Reports menu, choose Vendors & Payables, and then click Vendor Phone List.
2. Click Modify Report at the top of the report.
3. In the Modify Report window, on the Display tab, select Eligible for 1099.
4. Click OK.
5. Review the report on the screen for vendors marked as "No" in the Eligible for 1099 column.

If you need to send Form 1099-MISC to any of these vendors, you must set them up as 1099 Vendors.
== If you found any missing 1099 vendors, open the vendor's record, and on the Additional Info tab, select Vendor eligible for 1099 and enter the vendor's tax ID number.
== On the Address Info tab, make sure the vendor's name and address follow postal guidelines.

Verify your 1099 accounts
1. Go to the Reports menu, choose Vendors & Payables, and then click 1099 Detail.
You see the vendors you designated as eligible for 1099-related payments and the accounts you selected for tracking 1099-related payments.
2. Click Print for a paper copy of the report in its current form.
You'll need this for comparisons.
3. Click the second 1099 Options drop-down list at the top of the report and choose All allowed accounts.
4. On your computer screen, look for accounts that are missing from the paper copy of the report.
5. If an account is missing from the paper copy of the report, and you have been using the account to track 1099-related payments, set up the account as a 1099 account.

QuickBooks: Using Sales Receipts for PayPal Payments

A QuickBooks Retail version client asked: What do I need to understand about the Enter Sales Receipts window when I’m recording PayPal payments?

Enter a sales receipt to record point-of-sale payments when you receive full payment at the time of the sale. Sales receipts include payments by cash, check, or credit card. You can enter individual sales or, if you don't need to track each sale individually, use this form to summarize your day's sales activity.

The total paid for the specific item (product or service) is recorded in full on the first lines of the form.

Optional – Create a subtotal line so that you know that you’ve accurately recorded the total payment of items.

The next line is recorded as an other charge item linked to the expense account for Bank / PayPal fees with a negative amount. It is important to use the negative amount to reduce the total received.

The bottom line is the amount you received from PayPal (total minus fee).

QuickBooks: “Other” on the Financial Reports

A QuickBooks Pro client asked me recently why the profit and loss report showed “Other” when there were subaccounts set up.

The reason is that when a chart of account has subaccounts, posting should only be made to the subaccount. You can always tell which accounts are sub accounts, because they are located under the Parent account, and are always indented.

Fix the entry:
When you see the "- Other" on a report, double click on the amount until you trace it back to the original transaction. Reclassify it to a correct sub account.

QuickBooks: Finding Duplicates

A client asked about a good way to look at all invoices sequentially to see if duplicates, etc?

There are two ways to locate duplicate entries

1) In the Customer Center, click on the Transactions tab, then select invoices. You'll see all the invoices on the right -- click the column headings to sort.

2) Customize the Missing check report to use the A/R account instead of the checking account to get a nice invoice register.
Start by going to: Reports / Banking / Missing Checks
Then: Modify Report
Filter – Account to be Accounts Receivable
Filter – Transaction type to be Invoice
Filter Date – You decide
Hit OK

QuickBooks: Inactive Accounts

A client recently mentioned that she was going to clean up her accounts at the end of the year and get rid of all the inactive accounts. She wanted to make sure that it was OK to delete them since they were old and no longer necessary.

The answer is simple: QuickBooks will not allow accounts to be deleted that have been used in a transaction, and do not delete legitimate transactions.

Inactive accounts are a normal and natural process in QuickBooks. If the account is brand new, and there are only a few transactions created in error, they could be deleted, and the account itself can then be deleted.

All others, should be made inactive, and then hidden from view.

Here’s the method for the Chart of Account
1) Always make sure the account first has a zero balance.
2) Highlight the account, right click, and select Make Account Inactive.

What Happens: The account disappears from the Chart of Accounts list. It is still there, only hidden. You can find it by clicking the button at the bottom of the Chart of Accounts, called Account. Then click Show Inactive Accounts.

Here’s the method for a list such as customer or vendor:
1) Double-click the list entry.
2) In the Edit window, select the “Account is inactive checkbox.”
The name of the checkbox depends on the type of list. For example, if you're making a class inactive, the checkbox is labeled Class is inactive.
3) Click OK.