Monday, March 31, 2014 Bucket Fields

Categorize Data Quickly with Buckets. Bucketing lets you quickly categorize report records without creating a formula or a custom field. When you create a bucket field, you define multiple categories (buckets) used to group report values.

You can add up to five bucket fields per report, each with up to 20 buckets.

  1. In the Fields pane of the report builder, double-click Add Bucket Field or drag it into the report preview. You can also click a column menu for a field in the report and select Bucket this Field.
  2. Edit the bucket field according to the field type.
    • Numeric Bucket Field
    • Picklist Bucket Field
    • Text Bucket Field Date Formatting

Sometimes you may want to return a formula date field such as Today() in a combination text format.  The standard merge field {!Today} returns today's date in numeric format, such as '3/31/2014'.

How can I create a merge field that will return today's date in the format '25 February 2014'?

"December")&" "&

TEXT(YEAR(TODAY())) Using External ID's

Using External ID’s

Importing Data? Save yourself valuable time by using External IDs.  All relational databases use a unique ID to tie data together.  When importing different types of information (Accounts, Contacts, Opportunities, etc.), create a custom field on each type of record to relate one record to another.

Go to Object, Create new field, choose the Text field type.

Click Next and choose External ID

Once done, using the Data Load, choose Upsert

Choose your External ID

Finish the regular import process.

Thursday, March 27, 2014

Crystal Reports : Formulas and Counting

Using simple Summaries (Insert | Summary) in Crystal is great, don't get me wrong, but they are inflexible. For instance, when summarizing (trying to get a count) on {INVOICE.INVNUM}, ALL invoice numbers are evaluated, including duplicates (unless a Distinct Count is being used).

Which usually is fine, but there is always some sort of exclusion to be made; we only want invoices that DON'T begin with "000150" because it a House Account, etc. And you could handle this within your Selection Criteria handily enough; but let's imagine that it screws up other parts of the report; excluding House Accounts impacts other Subtotals, etc.

So to accomodate this little pickle, we will use a formula to count, one record at a time. This is actually a very popular Crystal Technique and you see it a lot in older, more "veteran" reports.

Create a formula called
IF MID({INVOICE.INVNUM}, 1, 6) <> 000150 THEN 1 ELSE 0

So what the formula is doing is evaluating the first six digits of the Invoice, and if it ISN'T a House Account, it returns 1 (we want to count it), otherwise it returns 0 (we want to exclude it).

Now, create a simple Summary on {@InvoiceCount} and make it a SUM operation, this way you get a count of only the records you want (for the particular task at hand), without sacrificing any existing Selection Formulas or Summary operations.

Crystal Reports : Selection Logic as a Formula

Here is a nice technique to use when your selection criteria starts to get out of control; offload the "heavy lifting" to a formula, then just reference that formula within the Selection Expert.

For instance, let us imagine our Selection Criteria needs to test for multiple things; {CONTACT.STATE} should be either NY, PA or NJ, AND {CONTACT.SOURCE} should be "XMAS 2011" AND {CONTACT.LASTESTIMATE} is at least $100 but no more then $500.

Now, the Selection Criteria for that would look something like this;

Note the use of enclosing parenthesis around the last two conditions; we want the LASTESTIMATE to be BOTH greater than 100 and less than 500.

But you are a Crystal Reporter, and know deep in your heart that someday, someone will need you to change this report. Here is how I would handle the same situation; I would create a separate formula for each condition, thusly;




Now, once those formulas are created (and can be viewed right within the report itself, making troubleshooting very easy), you can use them in the Selection Criteria like this;

{@frmState} = TRUE AND {@frmSource} = TRUE AND {@frmEstimate} = TRUE

This leaves lots of room for compounding, making changes, and plugging in the eventual Parameters. Try it!

Crystal Reports : The Selection Criteria Object

When the Selection Criteria becomes very complex for any given report, I often find it helpful to display the whole Selection Criteria right on the report.
To do this, you need to insert a "Special Field" from the Field Explorer.
Select Insert | Special Field.
Drop down the Special Fields list.
The field we want to insert is Record Selection Formula.
You will also notice that the Group Selection Formula is also available here.

Crystal Reports : Unique Counts

When creating a "Summary", using the Count function may not be desirable. It is often the case that a report must only return the number of unique contact records, as other tables (i.e. History) may contain multiple rows for each customer.
Select Insert | Summary.
Select the fieldname you wish to summarize.
Make sure to select Distinct Count as the Summary Operation.

Crystal Reports : Using a custom selection within 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.

Wednesday, March 26, 2014

GoldMine : Setting Forced Logout

GoldMine licensing works via concurrency. This means that if you own 10 seats, you can have 50 users defined, but only get at 10 in at once. In order to ensure that there are always seats available, you can set up a Forced Logout for each GoldMine user. This will “kick them off” of GoldMine when an inactivity threshold is reached.
1    1. Select Tools | Users Settings.
2    2. Double-click on the user you wish to set this up for
      3. Click the “Forced Logout” button.
4    4. Check the box labeled “Log out user…”, then specify the idle threshold, and optionally the …after this time textbox.
b    5. Ok your way out.

This is also a good way to make sure everyone is out each evening before maintenance or backups are performed.

GoldMine : Copying Dashboards

Did you know that you can make copies of the “canned” dashboards that come with GoldMine? This makes it possible to modify them to your own specifications. 

1    1.     Select Go to | Dashboards.
2    2.    In the left-hand pane, right click on the Dashboard to copy, then select Copy.
3    3.   When prompted to copy data sources, click Yes.
4    4.     You will now have a new Dashboard called “copy_from…”.

Go ahead and take a peek at what’s under the hood!

GoldMine : Using Case Templates

The Cases module has been a little unsung, in my humble opinion. Most of our clients either have a bona-fide service department (with it’s own call logging software) or have no real service tracking and are unsure how to start. A good way to begin this process is to create a few Case Templates to ease into the idea.
To open the Cases tab, select Go to | Service Center.
To start create a new Case Template, click Template | Manage Templates | New.
Go ahead and give the template a name, then fill in what you want to be the “defaults”. Keep in mind that each of these values will be sortable in the Cases list, and it helps to plan out how you are going to track issues beforehand.
To save the template, simply click Save.
Now, to use the new template, just;
1.       Create a new case by clicking on New Case.
2.       Search for the Contact you want to link the Case to.
3.       Once in the Case view, drop down the Template list (in the upper right hand corner) and select your Template.

GoldMine : Constant Contact Integration

In GoldMine versions 2013 and above, Constant Contact integration is available. And it’s a shame that such a powerful feature receives so little “real estate” within the GoldMine application. In a nutshell, the Constant Contact integration allows you to;

-          List your current Campaigns
-          Create a history for each GoldMine contact that receives a Constant Contact e-mail.

To enable the integration, select Go to | Campaigns | Constant Contact | Campaigns. This opens the Constant Contact tab.

1.       Click Configure to enter in your Constant Contact credentials.
2.       Click Refresh Campaigns List to refresh your current Constant Contact campaigns.
3.       Click Download Activity to download Constant Contact e-mail activity. This will create a history for each Contact that’s received a Constant Contact e-mail since the last time you clicked the Download button.

GoldMine : Read and Update Access

You can specify rights for a contact field in GoldMine by following the steps below:

Right-click the field for which rights should be set and select 'Properties'.

'Field Properties' dialog box will be displayed. Click 'Security' tab.

Specify either/or "read" and "update" access for the field, setting the values to a GoldMine user or user group.

Click "Ok" to exit.