Thursday, June 26, 2014

Crystal Reports : Using MAXIMUM() and MINIMUM()

If you are using a date parameter that is Ranged (i.e. a single parameter field that prompts for a starting and ending date), you will probably want to display these dates on the report. This helps prevent confusion as to what data is included in the report. To do this, we will need to create two formula fields, one for the starting date and one for the ending date. The formula fields will use the Minimum and Maximum Crystal functions.

For this example, our parameter field is called {?DateRange}

Formula field #1 (Starting Date) : Minimum({?DateRange})
(Pulls out the minimum value of our range)

Formula field #2 (Ending Date) : Maximum({?DateRange})
(Pulls out the maximum value of our range)

Crystal Reports : Parameters with Multiple Values

When using a parameter that accepts multiple values, you will notice that you can't just drag it onto the report. We must pass it to the "Join" Crystal Function to "split" the values out.

Our parameter field is called {?UserID}, and accepts multiple values.

Create a formula with the following code:

Join({?UserID}, ", ")

This will return all UserID values within the {?UserID} parameter, separated by commas.

Crystal Reports : Age Calculation

One of the many things you will want to do with a bona-fide date field is calculate age.
Crystal makes it easy for us here; as long as we're comparing two date type fields, we can perform simple math.
Let us suppose that the date field in question is {CONTACTS.SOURCEDATE}, which represents when a particular prospect was imported into our database.

1. We can see how many records were imported in the last seven days

{CONTACTS.SOURCEDATE} <= CurrentDate() - 7

2. Or how "old" any particular record is, expressed in days


3. To calculate the age in years, we must use the Crystal DateDiff function:

DateDiff("yyyy", {CONTACTS.SOURCEDATE}, CurrentDate())

The "yyyy" specifies we want the answer in years; "m" and "d" are also options (months and days, correspondingly)

Crystal Reports : Subtotaling Formulas

Let us suppose we have an Invoices report. This report is subtotaling a value called {INVOICES.TOTAL}, which is simply a "raw" dollar amount for each invoice. You could simply use the "Insert Summary" menu item under "Insert" in Crystal. However, this limits you if you ever need to modify the way Invoices are subtotaled.

It would be better to create a formula field called {@frmTotal} and then Summarize that.

The code for {@frmTotal} is simply {INVOICES.TOTAL}. Depending on your back-end database value, you may need to convert it with TONUMBER{INVOICES.TOTAL}.

The reason for this is simple; now that we have control over how an Invoice total is expressed, we can do anything we want, including multiplying each invoice by a "markup", like this:


Which returns each Invoice Total plus three percent of itself. Because you are still Summarizing {@frmTotal}, you need only change this in one place.

This is good practice as any Crystal Reporter knows that report usage has a way of changing. Leaving yourself "wiggle room" is always a good idea.

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 : 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 : Carry Over Completion Notes

If you're using Automatic Followups in GoldMine, then you might want to set up your History Notes to "flow" to the followup activities;

1. Go to Tools | Options
2. Click on the Schedule Tab
3. Check the box next to "Carry over completion notes when scheduling follow-up calls"

Having this checked will copy the Notes from the currently completed item to the Automatically Scheduled Followup Item.

GoldMine : Securing Picklists

Did you know that you can configure any one particular Pick List to make it easy (and consistent) for users to enter in data?

1. Bring up the Pick List for any field you like by clicking into the field and hitting F2.
2. Click on the "Setup" button.
3. Here are a few of the most popular options:
a. Unchecking "Allow Blank Input" will force the end user to enter in a value.
b. Checking "Force Valid Input" will force the end user to enter in a value that matches a Pick List item.
c. Pop-up when selected does what it says; pops up automatically when the end user moves to the field.

I have a lot of clients that protect the integrity of the data model with just a few modifications to existing picklists. My advice is to start slow with this process; try configuring a few fields at first, then expand as you get a feel for how the changes affect the end users.

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. Enhanced Lookups

Would you like to expand your search capabilities in your lookup fields?  Enabling Enhanced Lookups can make that happen.

To turn on Enhanced Lookup in your org, go to Setup -> Customize -> Search -> Search Settings, then enable Enhanced Lookup and choose which objects should have access to this feature.

Once you’ve turned on this feature, you will have the option to select All Fields when you’re in the Lookup window to search by any field on the object, such as Account Number or Email Address.

With this feature, you get the power of universal search even in your lookup windows! Dashboard Components Through Chatter

Would you like to post a snapshot of a dashboard component to Chatter?  A snapshot is a static image of a dashboard component at a specific point of time when the dashboard last refreshed.

First Enable Feed Tracking for Dashboards:
  1. Go to Setup - > Customize - > Chatter - > Feed Tracking.
  2. Look for the Dashboard object and click Enable Feed Tracking

Next Enable Dashboard Component Snapshots in order to Post to User or Chatter Group:
  1. Go to Setup - > Customize - > Reports & Dashboards - > User Interface
  2. Look for Chatter Options and click Enable Dashboard Component Snapshots

If you have enabled both options above, go back to your dashboard, make sure to refresh, then hover your mouse over a dashboard component and you’ll see an arrow at the top right of the chart component:

Click on either and add an optional comment.

Below are screenshots of a post to a Dashboard feed and to a Chatter Group: Messages and Alerts

Want to get a message or an announcement out to your entire organization?  Use Salesforce’s message and alerts.
  1. Go to Setup -> Customize -> Home -> Home Page Components.
  2. Click Edit next to Messages and Alerts. Enter your message to everyone and click Save. You can also use HTML if you’d like.
  3. Now, go to Home Page Layouts, edit the Home Page Layout, and make sure Messages and Alerts are turned on. Click Next and place your message where you’d like it to appear for your team.
  4. Now go to User Interface (it should be the last item in the Customize header), check Show Custom Sidebar Components on All Pages and click Save.

Monday, June 23, 2014

MSCRM 2013: Fun With Advanced Find

If you're a CRM guru, you already know that Advanced Find is your best friend when it comes to getting a handle on what's happening in your CRM.  You can get lots of answers, you just have to know what to ask and how to ask it.

Some examples-

Want to see what's been going on with your organization's Open Opportunities, aka who's doing what?  Try setting up an Advanced Find like this:

The Results will list all the Activities (Phone calls, emails, tasks, appointments) related to the Open Opportunities:

Trouble with email messages?  Use Advanced Find to look for email messages:

The Results will show all email messages and their Status reason- sent, received, draft (often an issue with the "From" email address) and pending send (possibly stuck):

Verify the integrity of your data- Use Advanced Find to search for blank fields.  In this example, I need to make sure that all crm users who are listed as Account owners have their Job Title and Phone number filled in on their user record because I'm using this information in a mail merge template.

I set up my Advanced Find like this:

The results show that there are 2 users with no data in the Title field on their user record.

As you can see, Advanced Find is very powerful and allows great insight into your data.


MSCRM 2013: Cool (Free!) Inline Grid Editor

There are a number of cool add ons for MSCRM 2013, one of my favorite time savers is the Editable Grid  from Sonoma Partners.  This free solution turns your existing Account, Contact, Opportunity and Case Views into editable grids.   

Here's a link to the download:

Once you have imported and published the Solution (if you don't have sufficient permissions to do this, ask your System Administrator to do it for you), navigate to any entity to see it in action.  

For this example, we are using Accounts:

The menu options look the same, until I place a check next to one or more account names.  Notice the "Edit Records" button:

*Note- depending which entity you are in, "Edit Records" may be listed in the 'More Commands' section:

Clicking "Edit Records" opens a new window with all the records and columns in the current view that are part of the Account entity:

Email (Primary Contact) is not displayed in the Edit Records window because it is actually part of the Contact record, Not the Account record.

To update data, click in the cell and start typing:

Notice that modified records are identified with a red exclamation point.  You may save and close this window, or choose not to save your changes:

You can't edit composite fields like the Contact field "Full Name" in the inline editor, instead edit first name and/or last name.  Just make sure to add whatever fields you would like to edit to the View.


Saturday, June 21, 2014


You can easily add a signature to your Emails.  Go to Setup -> Personal Settings -> Account Information.  You may need to scroll down a little bit to reveal the Signature.  Click Edit to the Right of Signature.  You can create a signature using the editor, or you can add an image of your signature by clicking on the Add image icon.

To add your signature to your outgoing emails, create an email template (see earlier Tip on how to create a template) and click the Add User Signature Checkbox in the Edit screen.

ZOHO CRM - Adding Fields to your Quick Create/Add Forms

"Quick" Forms are the Module Add Record forms that pop up from another module - for example when you're in a Quote and you want to add a new Product.  But the first time you try it you might see that not all of the fields you'd like to fill in are present on the Quick form... so how do you add these other fields?  Easy!  You make them mandatory.  Go To Set-up -> Customizations -> Layout.  Select the module who's Quick Form you want to modify.  Hover over the fields you'd like to add to the Quick Form and check the "mandatory" box that appears on the right.


You might be familiar with the Zoho CRM Outlook Plug-in or setting up your External email box as an associated POP for Email integration with your Zoho CRM, but ZOHO CRM has now made it possible for you to easily and selectively associate your emails to Zoho CRM from any of your email clients with the BCC Dropbox!
All you have to do is to Blind Carbon copy (BCC) the BCC Dropbox email address that you get with your Zoho CRM account, while sending emails to your customers. Based on the editable search pattern, Zoho CRM will automatically place a copy of the email that you want to associate, in the respective customers record. 
To find your BCC DropBox email address, click Set-up-> Personal Settings-> BCC DropBox.

Friday, June 20, 2014

MSCRM 2013: Help! Leading Zero on Zip Code Missing!

What do Maine, Vermont, New Hampshire, Massachusetts, Rhode Island, Connecticut and New Jersey all have in common?  They're all states with zip codes that start with a "0".  This leading zero can disappear during the process of creating your import files if you aren't careful.  I've had clients with this issue who have not noticed it for months, then are panicked because they need to generate letters or labels.

Here's one easy way to get your leading zero back.

Step 1:  Create an on-demand workflow (or ask your system administrator to do it for you).  The Zip code field is probably listed down towards the bottom of the page.  Type in a "0" (that's what the red arrow is pointing to) in the field then, using the controls on the right, set the field to the zip code field:

Step 2:  Create an Advanced Find query to get a list of all your Accounts with this issue; that is Accounts from the states whose zip codes start with zero that have zip codes that don't begin with "0".  Don't forget to save this View!

Step 3.  After clicking the Results button,  Select the Accounts and click Run Workflow.

Step 4. Select the Workflow created in step 1.

After the workflow has successfully run, this view will be empty.  Once you have your workflow and your Advanced Find View created, finding and fixing zip codes with a missing lead "0"  is easy and takes less than a minute.