Wednesday, February 27, 2013

Crystal Reports : Consolidating your Buckets

Let us suppose further that we are creating a crosstab object to show us where the INVOICES land in date buckets. You would want to specify each ROW of the crosstab as your summary (i.e. COUNT or SUM). You would need, then, to specify your COLUMNS within the crosstab as a single, unified function that will "kick out" the correct "bucket name" based on INVOICES.DATE.

We will call our unified crystal function DATEBUCKET and the code would look like this:


This code consolidates our date buckets into one unified crystal function that can be added as a Group or a Crosstab column. Obvoiusly, the bucket names are "kicked out" as described above. These will ultimately be the names of your groups.

Again, watch your math to guard against double bucketing!

Crystal Reports : Bucketing Problems

Double bucketing; it is a scourge upon the hapless Crystal Reporter!

Another technique to use when bucketing is to force Crystal to use mutually exclusive "automagical" functions. A good example of this is to use the YEAR() function to group any date by it's corresponding year.

So, if we break out INVOICES.DATE by grouping by YEAR(INVOICES.DATE), we can be assured that any one invoice can only belong to one specific year. Therefore we avoid double bucketing.

But the needs of the client are paramount; some businesses like "strange logic" to be applied to their buckets, depending on usage requirements.

An example: a client needs all invoices cut in December 2009 to belong to year 2010, yet anything previous should belong to year 2009.


2010 Bucket:

This code above includes all invoices with a year of 2010 AND also the ones from December 2009.

So we can see that sometimes the "automagical" crystal date function do not always meet the expectations of the client.

Know your manual date math! And beware the perils of double bucketing!

Crystal Reports : Grouping or "Bucketing" Data

The "Bucketing" series of articles are oldies but goodies.... I'm seeing this a lot in the field again as folks are cranking up serious sales reporting as the first quarter nears to a close.

When grouping (or "bucketing") data by a date field, it is important to make sure that your groups are mutually exclusive. Let us suppose that we are breaking down rows from a table called INVOICES based upon INVOICES.DATE. We just want to see which invoices are "out" thirty days, and which are "out" sixty days.

So, if our "30 Days" group is calculated thus:
(give me invoices that are between zero and thirty days old)

Our "60 Days" group must necessarily exclude day 30 and begin on day 31:

The potential danger here is making our second bucket start on day 30, a common mistake. This would mean that any invoice with a date landing on day 30 would belong to both buckets, therefore skewing our results.

This cannot be overstated. Crystal known nothing about your date buckets, just the code that you provide it.

A better solution could be to use the "automagical" Crystal functions AGED0TO30DAYS or AGED31TO60DAYS, both of which are available under the "Date Ranges" function tree in the formula editor.

I actually prefer the "manual" technique as described above, as it allows you to specify exactly what ranges are being used. (i.e. some clients like to use 40/80/120 instead of 30/60/90)

Crystal Reports : Changing Groups using the Expert

One of the most powerful features of Crystal is the ability to Group data.
And once you have some summaries working for you, groups, then, become synonymous with Dimensions. Think pivot tables.
It is a very common thing to change the dimensions of a report.
In Crystal, select Report | Change Group Expert.
From here, you can select a Group and use the arrow [mouse] buttons to change the "level" of the Group.
Clicking "Options" will allow you to change the Group Field, effectively picking a different dimension.

Crystal Reports : Using Formulas as Groups

One of the most effective techniques I've learned is how to group on a formula.
Think of it this way: the logic of the formula can be whatever you want it to be. So, any "high-level" corporate logic can be expressed here.
Take for example a table called HISTORY in which are listed history items per user.
Let us create a formula that will "kick-out" a value based on HISTORY.DATE.

IF {HISTORY.DATE} > CURRENTDATE - 30 THEN "History within 30 Days" ELSE
IF {HISTORY.DATE} < CURRENTDATE - 30 THEN "History beyond 30 Days" ELSE

Now this formula can be used as a Group.

You will notice that the last line is an ELSE {HISTORY.DATE}. This is because if a {HISTORY.DATE} does NOT fall into our formula criteria, then it will be "skipped". The final ELSE works as a "catch-all".

Monday, February 25, 2013

GoldMine : Password Policies

Another new security measure in GoldMine is the ability to set a password policy. This is done system-wide and applies to all users.

To do this, go to Tools | Configure | System Settings.
Click on the "Password Policy" tab.
You can set a minimum length, whether or not to block after three unsuccessful attempts, and also save a password history that cannot be repeated.

To set up password expiration, this is done on a per-user basis.

1. Go to Tools | Users Settings
2. Change the "Valid for Days" value.

You'll notice the "Next Change" date becomes activated after you choose x number of days.

Caveat; I once worked in a place that forced password changes every month. And as you walked around the office, on virtually every desk (usually on a stickynote) was each users password. So I guess the moral of the story is to use with care.

GoldMine : Required Fields

When customizing your GoldMine record layout, you can make any field "required". This means that the user will not be able to navigate away from the Contact Record until the field has been filled.

To do this;

1. Enter Customization Mode by right-clicking on the Contact Record and selecting "Screen Design".
2. Find and right-click on the field you'd like to make Required.
3. Select "Properties".
4. Go to the "Security" Tab.
5. Check the box which says "Required Data Entry".

"Ok" your way back out. Users may need to restart their GoldMine to get the new changes.

GoldMine : Recent Contacts

One of the nicest things about the new GoldMine is the list of recently viewed contacts on the upper left hand side. However, by default, it only tracks five. To increase this number;

Yes, you can set them to show up to 99 contacts:

Select Tools | Options | System tab
Under Navigation | increase Recent items shown
Logout and log back into Goldmine
Open the Contact Search | select/open Contacts

You will see the Recently Viewed items list increase as you open new contacts.

GoldMine : 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 : Viewing two Contacts at Once

Did you know you can view multiple GoldMine records simultaneously?

By default, GoldMine displays one contact record at a time. If you also want

to open other contact windows simultaneously, follow these steps:

Select Window | New Contact Window. This will bring up the Contact List.

From the contact list, select the second contact you wish to see and
double click on that contact.

This opens the second contact window. You will see a second record tab at
the top of your screen. Then, select Window | Tile Horizontally for a side
by side view or Cascade Windows for a Vista-style window display

This option is particularly useful when you wish to compare two contact
records in GoldMine. It saves time by eliminating the need to alternate back
and forth between the two records.

Saturday, February 23, 2013

MSCRM 2011: A Relationship to an Account AND Contact on a Qualification of a Lead

A Client recently asked if it was possible when qualifying a Lead to an Opportunity that a relationship be built from that Opportunity to a Contact AND an Account. The client wants to maintain the history of the Opportunity with the contact even if the contact moves to a different organization.
Typically when a Lead is qualified to an Opportunity, the relationship is built to an Account ONLY when there is data entered in the Company Name field of the Lead.  So if the Company name field has data, a relationship is built to the Account and NOT the Contact. In order to get around this, the following customizations need to be performed:

1)     Hide the Company name field currently on the Lead and replace with a custom field (Organization Name) for the Company/Organization Name to be entered.

2)     Create a Process (Workflow) that is executed when an Opportunity is created and the Originating Lead field on the Opportunity contains data. The workflow will perform the following:

a.     Create an Account with the Account Name = Organization Name from the Originating Lead. Map all of the appropriate fields from the Originating Lead to the Account  in the workflow.

b.     Update the Potential Customer (Contact) Parent Customer to the Account created in a. above with the Account (local value).

c.     Update the Opportunity with the Account created in a. above.

 Note: The Lead should only ever be qualified with the Contact.

In the Opportunity Module, the Parent Customer should always contain a Contact. There will also be a lookup field to the Account (relationship to Account) added to the form that will display the Parent Account of the Contact at the time the Opportunity is created.

This should do the trick of ensuring that a relationship to an Account AND Contact is created to an Opportunity when a Lead is Qualified.

MSCRM 2011: Security Roles

A client recently asked: What are best practices for setting up security in my CRM system?

Security Roles, in Microsoft Dynamics CRM are assigned to a user or a team and they are used to determine what users can and cannot do in a Dynamics CRM organization. I started to write this blog entry on implementing and best practices for Security Roles and came across this article by Richard Knudson. Richard Knudson is a CRM MVP and here does a great job in explaining Security Roles in Microsoft Dynamics CRM 2011:
I hope this article helps to clear up an questions around security roles and their configuration.

MSCRM 2011: Checking the State of a Form

Sometimes you find it is necessary to do certain functionality in CRM depending on whether you are creating a new record, modifying a record etc. The below is the java script to check the state of the form. Necessary coding can then be entered depending on the state of the form. Hope this comes in handy!  

function getFormType ()  {





 var formType = Xrm.Page.ui.getFormType();

 if (formType == FORM_TYPE_CREATE) {

 alert ("This record has not yet been created.");


 else {

 alert ("This record exists in the database.");



Wednesday, February 20, 2013

QuickBooks: Group Items for Quick Sales Form Entry

If you have multiple items that are frequently (or always) sold together, you can create a group item to make it easier to sell the multiple items.

For example you may have a product for which you always add a handling charge or a local delivery charge. Perhaps you have a product for which customers frequently buy an add-on. Instead of entering separate line items every time you fill out a sales transaction, you can create and sell a group item.

This is also a great way to provide a discount to customers who buy multiple items for which they'd pay the individual price if they purchased the items one at a time.

Group items are useful in QuickBooks Pro editions, because only Premier editions provide Inventory Assemblies. In addition, you can use a group even if you're not tracking inventory; a group can contain non-inventory items. If you are tracking inventory, and inventory items are included in the group, the inventory isn’t decremented until you sell the group item (unlike Assemblies, which have to be built in advance and the process of building decrements the inventory).

To create a group, the individual items in the group have to exist in your Items List (including any discount items for groups that provide a discount for buying multiple items). Create the group item using the following steps:
  1. With the Item List open, press CTRL-N to open the New Item dialog.
  2. Select Group from the Type drop-down list.
  3. Enter a name for this group in the Group Name/Number field.
  4. Optionally enter a description.
  5. Select each item that is part of this group, and enter the quantity for each item.
  6. If you're creating a group to reflect a discount for buying multiple items at once, remember to include the Discount item in the group.
  7. Click OK.
The New Item dialog for a group item includes a check box labeled "Print items in group". If you select that option, the individual items are listed on the printed sales transaction. (If any items in the group are sub items, they aren't printed.) If you don't select the option to print the items, a single line item appears on the sales transaction.

QuickBooks: Using Your Company Letterhead

Most people only think of QuickBooks as a financial reporting system but it can do a lot more. You can actually stay in QuickBooks and write letters on your own letterhead with just a little bit of setup.  This article will show you how to create a QuickBooks letter template with your company letterhead.

There will be more articles coming in the following months to help you do as much as possible while staying in QuickBooks.

If your company letterhead already exists as a Microsoft Word Document you can convert your existing letterhead to a QuickBooks Letter Template. It is best to start with a blank letterhead document. You can then edit it as you need to use it. 

1.   Go to Company > Prepare Letters with Envelopes > Customize Letter Templates
2.   Select Convert an Existing Microsoft Word Document to a Letter Template and click Next
3.   Click on the Browse button and select your company letterhead from your existing word documents.  Enter a name for your letter (such as C-Ltrhd(meaning Letterhead for customers) in the popup box then select the type of letter that you would like to create the template for (in this case use Customers).
4.   When you click Next your existing company letterhead on word will open. In this case it will be a blank letterhead document.
5.   QuickBooks should present a dropdown from which you can select the form fields to include which will populate with the specifics from your data file. Select the appropriate fields and insert them into the document. (If the form fields are not available for choosing, you will have to enter them)*. Close it.
6.   QuickBooks will then present a screen that says “Use the New Letter Template” and tell you what it is called and where it is located.
7.   You can use the letterhead template now, or later.

*Future articles will include viewing and editing existing templates, organizing and taking control of your communications using QuickBooks.  Be sure to stay tuned!