Monday, July 28, 2014

Crystal Reports : Copying Formatting

In order to shorten the time it takes to make a report look nice, we can use the format painter to first, "copy" the formatting from one field, then "paste" it onto another. Formatting includes everything under the formatting options menu, including things like decimal places, currency symbol, etc.

1. Click on the object you want to "copy" the formatting of.
2. Go to Format | Format Painter on the top level menu
3. Click on the object you want to apply the formatting to

Note: Some versions of Crystal will not have this menu option, but will instead provide a format painter toolbar icon, in the form of a little paintbrush.

Crystal Reports : Exporting to Excel

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 : Conditional Fonts

You can specify the color of any font in your report to change based upon a "condition".
A typical example would include making a sales total field "red" if it drops below a certain value.
Let's assume our Sales Total field is called "SalesTotal".
Step #1 : Right-click on the field in question and select Format Field.
Step #2 : Click on the "Font" tab.
Step #3 : Click on the "x-2" (formula) button next to Color.
Step #4 : Use the following fomula: if SalesTotal < 100 then crMaroon else crBlack

This formula forces the sales total to be drawn in red if it drops below 100, oherwise it stays black.

Pro Tip: When testing a condition to change the font, any fields on the report are available to use.

Crystal Reports : Notes Fields

I don't see a lot of these in use lately (thankfully). But if you come across a Note data type, Crystal has special ways of dealing with them;

- After inserting the field, you may specify whether or not it "can grow" (meaning vertically) by going to the fields' Formatting page and selecting "Can Grow". You may also specify how many lines it "can grow" by.

- Certain databases keep their notes in HTML format, in which case the output will look garbled. You may, again, go to the fields' Formatting page and go to the Paragraph Formatting tab, then select a "Text Interpretation". Choices include HTML and RTF (Rich Text Format).

Crystal Reports : Setting Bckground Color

To make a report easier to read, you can alternate the background color to help different lines stand out. (Think of the old green and white striped paper you used to put in your tractor printer years ago)

To make this happen, we will need to format the Details Section of our report.

Step #1 : From the top-level menu, select Format Section.
Step #2 : Select the "Detail" section on the left hand side.
Step #3 : Click on the "Color" tab on the right hand side.
Step #4 : Click the [formula] button with the "x-2"on it to bring up the formula editor.
Step #5 : Paste in the following : if RecordNumber mod 2 = 0 then crSilver else crNoColor

This will alternate a white and silver background for each row printed.

Pro Tip : To alternate color for Group rows, paste the following into the Group section color formula: if GroupNumber mod 2 = 0 then crSilver else crNoColor

GoldMine : Cloning Users

Turnover is crappy; it's never a nice situation and it sometimes can leave a bunch of "orphaned" records in your GoldMine database. It's easy enough to create a new GoldMine username, but what do you do about the Pending and unanswered e-mails from the previous user?

Fortunately, GoldMine makes this rather easy. In our example, let us imagine that Bob is leaving our company and Jim is taking his place.

The first thing we can do is Clone an existing user. This allows us to create a new username for Jim which will be a "carbon copy" of Bob's old username. This would include user preferences, e-mail configuration and user access permissions.

To do this;

1. Select Tools | Users Settings from the top level menu.

2. Find the user you wish to duplicate in your list. Right click on them and select "Clone".

3. GoldMine will now prompt you to enter in a new Username, Full Name and Password (optional). Do so and hit Ok. You now have a new username that has been cloned from the old.

Now, to deal with the Pending Items and unanswered e-mails attached to Bob's GoldMine username. This actually happens when we Delete Bob's old username.

1. Select Tools | Users Settings from the top level menu.

2. Find and select the user you wish to purge from the system. Right click, select "Delete".

3. GoldMine will now prompt you about what to do with Bob's old pending records. You can;
  - Reassign the Users' Activities to a new user (most popular)
  - Automatically Complete all outstanding Pending and E-mail activities (less popular)
  - Leave the activities as is
  - Delete all Activities for the deleted user

Keep in mind that when you delete a GoldMine username, that username no longer appears in any drop downs.

GoldMine : Which Activity Type?

Clicking on your "Complete" or "Schedule" menu in GoldMine can be an aggravating experience. You'll notice there are several different Activity Types to choose from. And for the most part, they're all kind of equivalent. All need a Date, a User and usually, a Reference. But that's where the similarities end.

Here is a list of the different types and their respective uses;

Phone Call - The simplest and most frequently used Type. Appears on the graphical calendar as a "phone" icon.

Appointment - The is the default Type selected if you drag out a time on the Calendar. These appear as a pair of shaking hands.

Next Action - Here is where the Types start to "lose meaning". A Next Action can be anything; typically used to identify "internal" or busy work which isn't necessarily communicated with the client. Appears on the Calendar as a push pin.

Sale - The only Activity Type that has fields for a Dollar amount. You'll notice that there are Quantity and Price fields, but these are actually optional. You don't necessarily need to be selling Widgets in order to capitalize on this feature.

Literature Request - Probably the most complicated Activity Type. This allows you to Schedule or Complete an item for the Contact Record out of your Literature Fulfillment Center (Go To | Literature)

GoldMine E-mail - Used under the Schedule menu, this item appears for the scheduled user as an item in their GoldMin inbox, but is never "sent" using an e-mail server.

Event - The only Activity Type that has a non-standard Duration. You'll have noticed that most Types have the option of specifying the Duration in Hours or Minutes. Events are Days long, and do not appear on the graphical Calendar.

To-Do - The lightest weight Type. To-do's don't require a Date, just a User, some notes and a Priority. To-Do's appear on the Calendar in the Task Pane (lower left hand corner).

Happy Scheduling!

GoldMine : Creating Cases from E-mails

The Cases module is something that was added to GoldMine Premium. It lets you track Service Tickets against Contact Records. A lot of our clients like to be able to create a new Case from an E-mail (typically this is how new "issues" are submitted).

To do this;

1. Open the e-mail you want to create a Case from.

2. Click on the "Link to Opportunity..." button from the top toolbar (within the e-mail message).

3. Click on the "New" button next to "Case" (the bottom item).

4. GoldMine will now drop you into a New Case window for the associated Contact.

Unfortunately, this does not present the "full blown" New Case window; rather it only allows you to create the "default" Case Type and enter in some Notes.

However, it's a great way to easily create a Case, something which I don't see a lot of folks doing. Try it!

GoldMine : Click Drag to Schedule

The best way to schedule something in GoldMine is from the graphical calendar (Go To  | Calendar). When you do it this way (as opposed to using the Schedule menu), the Date, Time and Duration are automatically filled using whatever swath of time you dragged out on the calendar.

1. Open the Calendar.

2. Using the Week or Day view, click-drag out a section of time.

3. GoldMine now prompts with the "Schedule a..." window. Please be aware that GoldMine automatically thinks this is an Appointment. You can feel free to change this to "Call" or "Next Action", or whatever may be applicable.

Notice that the scheduled item is linked to the Current Contact, that is, whomever we're looking at right now. If you need to relink to a different contact, simply click on the little "person" button to the right of the Contact name within the Schedule window.

GoldMine : Hotkeys

I'm a big fan of hotkeys. The less I have to touch the mouse, the better. With this in mind, here are my favorite GoldMine hotkeys;

CTRL+SHIFT+E : Create a new e-mail to the current contact.

F3 : Search the current tab for text.

F4 : Bring up the Search Center.

F5 : Bring up the E-mail Center.

F6 : Activity List.

F7 : Calendar.

F9 : Opportunities.

Have fun!

Thursday, July 24, 2014

MSCRM 2013: Help with the Quick Find View

One of the most common, and frustrating, issues people have when first using Microsoft Dynamics CRM is the inability to find records using the search box.  Let's look at an example.

Scenario- I'm in Opportunities, I can't remember the Topic name, but I  know the Account starts with "Fabrik".  As you can see from the list below, There is an Opportunity for the Account Fabrikam:

Yet when I type Fabrik in the search box, no results are found:

Why is that?

Only fields that are defined in a system View called Quick Find View are searched.  To see what fields are included in the search, first navigate to the Default Solution, search the list of Opportunity Views for the Quick Find View.

Open the Quick Find View and click "Add Find Columns" to determine which columns are currently included in the search:

We see Account is not currently included in the search, which is why Fabrik yielded no results:

Only the Topic field is included in the search in the out of the box Quick Find .  Lucky for us, adding additional fields is as easy as checking the box next to the field name (if you have System Administrator or System Customizer privileges).  Save and Close the View.  Publish.

TIP: Don't forget to use the wildcard search (proceed search text with an "*")  to search for the text anywhere within the field.


MSCRM 2013: Why Can't I enter Estimated Revenue in the Opportunity Form

Here's a quick tip for the folks who are just getting started with Microsoft Dynamics CRM.  The estimated revenue generated by an Opportunity can either be "System Calculated" or "User Provided".  The field where this is set is called Revenue.  If Revenue is set to "System Calculated", it will be locked for editing:

In general, if your Organization uses the Product Catalog, this field will most likely be System Calculated.


MSCRM 2013: Printing CRM Records

Although we all strive to be "green" and limit the amount of paper we use, some of us still need to print records from time to time.  Whether you access your CRM Online through a web browser or through Outlook, printing a record is easy.

Scenario 1-  Within Outlook:   In this example I'm looking at contact "Patrick Sands" and I'd like to print this record.  I simply type ctl-p (or click File==>Print).  Your printout will look like this:

Not very pretty, but the information is there.

Scenario 2- From the web browser:  Click on the Gear in the right-hand corner, then click "Print Preview".  The preview window opens and looks like this:

The printout generated from the web browser is much like the form.

And there you have it, two different ways to print a record with two very different results.


Monday, July 21, 2014

ZOHO CRM - Using Multiple Currency Feature

When your company is Global,  it is often necessary for your CRM to communicate in multiple currencies.  Zoho CRM makes it pretty easy to set this up.

  • Setup-> Organization Settings -> Currencies
  • Choose your Home currency - CHOOSE WISELY - once set, this cannot be changed!
  • Customize the format (if needed) then click Confirm
<you will get a message saying it takes a while... this is normal> - the software is adding the currency field to your modules

From this screen - You can add additional currencies by 
  • clicking the Add Currency button
  • Choose the currency from the drop down list
  • Option to customize format
  • Enter an exchange rate
  • click save

Using Currencies: When you configure your account for multiple currencies, Currency fields are automatically added to the appropriate modules.  You just need to select the desired currency from the Drop down list.  NOTE - ALL currency fields are converted to the selected currency.  Values are shown in selected currency as well as the home currency, so be sure to update your exchange rates!

ZOHO CRM - Workflows not working after Account Re-activated?

Say you are using Zoho CRM on a trial basis, and your trial lapses before you upgrade to a paid account.  When you re-activate your account you may notice that workflows you built during your trial period are no longer working... simple fix - check to make sure your Rules are Active.  They sometimes de-activate when an account lapses.
  • Setup -> Automation -> Workflow
  • Open Rule by clicking on rule name
  • Check "Active" Box

ZOHO CRM - Executing Workflows on Imported Records

We're all familiar with using workflows to automate functionality in Zoho CRM, right? Workflows can be triggered based on record events (create, edit, field update, etc), or if you're using the Enterprise Edition, time-based events.  Oddly enough, when records are created by import (e.g., from an excel spreadsheet), workflows set to run on the record event of Create are NOT triggered.  So how do you get this workflow to run?  Easy... (just click the Advanced Options link to expose).

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.


Thursday, May 29, 2014

Crystal Reports : JOINs Refresher

It isn't often that you'll need to worry about this, but it is something all Crystal Reporters should know. You can change the default table JOINs in Crystal to mimic the other JOINs available within the SQL syntax;

Within the Visual Linking Expert (Database | Visual Linking Expert), we can create JOINS between the tables that make up our report.

For those of us that have more complex needs or a more modular (relational) database (i.e. many tables) may need to use different kinds of JOINS. The "default" JOIN type is INNER.

1. Within the Visual Linking Expert, right click on a "link" (right on the line).
2. Select "Options".
3. At the bottom right of the window, you may select a JOIN type. All the expected types are listed (i.e. LEFT OUTER, RIGHT OUTER, etc).

Note: Changing this will impact your expected recordcounts. It is advisable to be familiar with all the JOIN types before modifying. It's always a good idea to write down how many records are being returned before changing the JOIN. That way, you can really see how the JOIN is impacting the report.

Crystal Reports : MID() Refresher

Ah, MID(). Is there anything you can't do?

Let us suppose that we are reporting on history, and our field {HISTORY.REF} has some annoying characters padded into the beginning of the string, like this:

"oc: Justin Hill - Appointment"
"oc: Gene Marks - Phone Call"
"oc: Corey Babka - Next Action"

For cosmetic purposes, we will create a formula to strip out the "oc:" with the following code:


This will return the entire string, starting at character position five.Using the function as outlined above will return;

"Justin Hill - Appointment"
"Gene Marks - Phone Call"
"Corey Babka - Next Action"

This is also great for cutting out parts of model numbers, serial numbers, etc.

Crystal Reports : Conversion and Formatting

As mentioned, when using the Format options in Crystal, it can only be as smart as the underlying field. That is, a number must be a "Number" and text must be "Text". If you aren't seeing the expected formatting options, you may need to convert the data type of the underlying database column.

There are many functions available in Crystal to make this happen, but for now we'll keep it simple.

1. To convert from Text to Numeric, simply use the ToNumber() function within a formula, like this;


The resulting formula will "behave" like a number.

2. To convert from Numeric to Text, use the ToText() function;


Try it. Every Crystal Reporter should be comfortable with type conversion; it's our bread and butter!

Crystal Reports : Format Multiple Objects at Once

While we are on the subject of formatting, did you know that you can apply formatting to multiple objects at once?

1. Simply CTRL-Click each desired object to add them to the selection. (or drag out a box-select)

2. Right-click on any selected item and select "Format Objects".

From here you will be able to apply formatting to all objects. Keep in mind that Crystal will only give you formatting options that all objects have "in common". For instance, if you have both Date and Number objects selected, the Format Objects window WON'T give you specific type-formatting options.

This is a nice way to easily change the Font or Style of many objects at once. Try it!

Crystal Reports : Date Formatting Options

Oh, Crystal, you always give us so many options! Especially when it comes to formatting data types.

1. Right-click on any report object, select "Format Field".

If you're modifying a Number type, you'll see a "Number" tab. If you're modifying a Date type, you'll see a "Date / Time" tab. This is one of those instances when Crystal does a bit of "automagical" thinking for you. If you don't see the formatting options you expect, it's because the underlying data type isn't correct.

2. From the "Date / Time" tab, select your favorite Date Format. You'll see there are many options. I'm personally a big fan of XX/XX/XXXX, but must admit that XX/XX/XX also works well.

The biggest reason you'll probably want to go here is to select a formatting that omits the time precision portion of the date field. You'll probably need to re-size the field in question after the fact as well.

3. For those who are brave of heart, you may also click the "Customize" button. This gives you ultimate control over how a date is presented. Poke around a little; it's pretty interesting. You can use this, in some cases, to provide offbeat sorts and work in client-specific nomenclature.

GoldMine : Viewing YOUR History

Such a simple question, isn't it? How can you look at just YOUR historical items without running a report or going from record to record to record?

Easy, it's all within the Activity List.

1. Select Go To | Activities from the top level menu.

2. From here, you can see both Pending and Completed items on a per-user basis, filtered by Date Range and Activity Types.

3. Use the left-hand pane to drill down to the historical items you want to inspect. (Clicking on the "Closed Activities" folder gives you ALL activity types)

4. Use the top-level drop downs to select Date Range and Username.

5. Don't forget you can export the list by right-clicking and selecting Output To | Excel.

Try it!

GoldMine : Turning Document Preview On or Off

One of the nicest things about the newer versions of GoldMine is the ability to preview the contents of any linked document without opening it. This is displayed within the "Preview" pane within the Links tab. However, if you're having problems previewing certain kinds of documents, or if this simply slows down your computer, you can turn Off document previews on a per-filetype basis.

Note: This is a system-wide setting and can only be accessed from a "Master" account.

1. Select Tools | Configure | System Settings from the top level menu.

2. Select the "Preview" tab.

3. From here, you can pick and choose which document types are Previewed within the Links tab.

4. When finished, Ok your way back out to GoldMine.

5. All users will need to restart GoldMine to affect the changes.

Happy previewing!

GoldMine : Enabling E-mail Spell Check

When you type and send out a bazillion e-mails a day, simpel speling erorrs can (and do) occur. To enable automatic spell-check when sending GoldMine e-mails;

1. Select Tools | Options from the top level menu.

2. Select the "E-mail" tab, then "More Options", then "Advanced".

3. Check the box labeled "Auto spell-check before sending".

4. Ok your way out.

Now, if you've never used this feature before, it needs to be "trained" a little bit. When you go to send an e-mail, the spell check will run and prompt you for any words it doesn't know. Your choices are;

0. You can simply double-click any of the suggested words from the list, or....

1. Ignore : Ignores just this single instance of the word. (for this spell check only)

2. Ignore All : Ignores all instances of the word. (for this spell check only)

3. Add : Adds the word to your users' dictionary. You will never be prompted to correct this word again. Good for things like acronyms, company names, etc.

4. Change : Replaces the word with the first item in the "Suggestions" list.

5. Change All : Replaces all instances of the word with the first item in the "Suggestions" list.

6. Suggest : Refreshes the "Suggestions" list.

It can be annoying at first, but as soon as you start Adding words to your dictionary, this becomes less of a hassle and will typically only prompt for "real" spelling errors. Try it!

GoldMine : Setting the Default E-mail Signature Template

Did you know that you can use an actual e-mail template for a signature, as opposed to using a simple text file? This allows you to use fancy fonts and insert company logos.

First, you must create an e-mail signature template.

1. Select Go To | Document Templates from the top level menu.

2. Select YOUR username from the top left-hand drop down list.

3. Within the left-hand pane, right click on "E-mail Templates", then select "New".

4. This will drop you into the template editor. Simply use it like a word processor and type out (inserting pictures as appropriate) your desired signature text.

5. When finished, click the little yellow disk button in the top left-hand corner to "Save". Make sure your template is listed properly in the Document Templates list.

To set the template to be your default e-mail signature;

1. Select Tools | Options from the top level menu.

2. Select the "E-mail" tab, click on "More Options".

3. In the lower right-hand corner, use the "Default Templates" drop downs to find and select your signature template. You can set it for "New Outgoing", "Replies", and "Forwarded".

4. Ok your way back out to GoldMine.

5. To test the template, simply start writing an e-mail. The Defaults you set up should take effect immediately.

GoldMine : Adding Menuitems to the Toolbar

Did you know that you can add simple GoldMine menu items to the top-level toolbar? This is done on a per-user basis, so each user can have their own "favorite" toolbar items.

1. Click on the "little down arrow" at the right edge of the toolbar;


2. Select "Add or Remove Buttons". 

3. Click "Customize". 

4. Click the "Commands" tab.

5. From here you will see all the available GoldMine top level menu items. To add any item to your toolbar, simply click-drag the item from the window up onto the toolbar. If you add the wrong thing, or want to remove an item, simply click-drag it OFF of the toolbar.

Wednesday, May 28, 2014 Some Salesforce Shorthand to Save You Time

Do you type in a lot of nice round numbers in the thousands?  Maybe your customer wants to buy 5000 cool widgets or a donor wants to give you $15,000 for a new grant.  Typing out those three zeroes every time gets tedious.  Try replacing them with “k” (1k, 5k, 250k).
How about those pesky phone numbers, you don’t have to properly format them every time!  Just type in the numbers and Salesforce will figure out the rest! Extension?  No problem, just add an “x” between it and the rest of the number.
And you don’t have to take your hands off the keys to click “Save” after editing a record; you can save by just hitting Enter!  It won’t work if you’re in a text area (regular, long, or advanced), but it will work even if you’re doing inline edits.  Enjoy these quick little tips that will save you seconds, minutes, and even hours every day! Embed a Chart in a Record

The Reports and Dashboard tabs are great one-stop shopping spots for data. But users don’t have to go there every time they need to know something. You can set it up so people can see a report chart right on the page where they’re working. For example, when updating an opportunity, they can see a chart that’s filtered to show the latest data about the account that opportunity is associated with, without leaving the Opportunity detail page.

  1. Pick the report you want to display in your record page. Make sure it already has a chart, and that it’s in a folder that’s shared with users who need access.
  2. Go to the page layout editor for the object you’re adding a chart to. Let’s use Opportunities for this example. From Setup, click Customize | Opportunities | Page Layouts. You can do this with a custom object too. From Setup, click Create | Objects, then choose the object.

  3. Click Edit next to the page layout.
  4. Click Report Charts. 

  5. In the Quick Find box, type the name of the report and click the lookup/magnifier to find and select the report chart. You can browse up to 200 recently viewed reports by chart type in the Report Charts palette.
  6. Drag the chart onto the layout.
  7. Click the "wrench" button (settings) on the chart to customize it.
  8. Click Save and go look at a record on the Opportunities tab. It will look something like this:

Congratulations! You've just saved some time and effort for everyone who works with Opportunities in your organization. You've made it so they can see data about an opportunity right on the page they’re working on, without having to switch over to another tab. Prevent Salesforce Emails from ending up in Spam Folder

One common problem organizations face when sending emails to clients is that the email ends up in the Spam folder and the client misses out on their emails.  Even though it is not completely possible to avoid Salesforce emails from ending up in the spam folder, but enabling email security compliance will help.
To do this: Navigate to Setup, under Administration Setup click Email Administration | Deliverability.  In the Email Security Compliance section, make sure that both the “Enable compliance with standard email security mechanisms” and “Enable Sender ID compliance” check boxes are checked.  The Sender ID compliance declares emails as coming from an authorized IP address. 

This setting will help to authenticate the emails, which in turn will prevent the emails from landing in the recipient’s spam folder.

Wednesday, May 21, 2014

ZOHO CRM - Identifying Modules in Custom Functions

Within Zoho CRM, each module has a 'system' name associated with it. Zoho allows you to change your Module Label, but the system name remains the same.  When you create a custom module in Zoho CRM, the system automatically assigns the Custom Module a system name, with the naming Convention "CustomModuleX", where X is the next number in an internal counter.  So, for example, the 4th custom module you create will be known internally as CustomModule4.

Why am I telling you this?  Because when you are writing custom functions, you must use the system Module name to retrieve and update records.  You can find this system name in the module list as shown below: (Setup->Modules)

ZOHO CRM - Where can I see the Emails in Zoho that were sync'd with the Outlook Plug-in?

So you've downloaded your Outlook Plug-in and configured your preferred synchronization methods for your Emails, but it doesn't appear as if your Emails are being sync'd with Zoho because they are not listed below your Contacts in Zoho...  Of course there could be many reasons for this happening, but as a first step try changing the Email source to Sent Emails from CRM using the drop down on the right of the Emails related records list under your Contact as depicted in the image below.
A little misleading ;o) - this is also the way to see mass emails sent to contacts and leads.

ZOHO CRM - View Report as Chart with Dashboards

With Zoho CRM Dashboard module you can present your custom reports in chart format with a few easy clicks...

  • Go to your Dashboard module, select the Dashboard to which you want to add the graph and click the Add Component
  • Choose the Chart radio button, provide a name and use the drop down to select the source report
  • Use the interface tools provided to set up the graph the way you want it: select the chart type and configure the details - the drop downs provided in the chart details window are based on the sorting established in the report definition.
  • Click finish
Your new chart will become a part of the selected dashboard!

Tuesday, May 20, 2014

MSCRM: Attaching a Document to an Opportunity

A lot of my tips are geared towards System Administrators.  This one, however, is for everyone.  The scenario - you would like to attach a document of some sort to an Account, Contact, Opportunity, whatever.  Here's how:

1. Select Notes:

2. Type in some text, then Click "Attach":

3. Select the file from your computer to attach.  Click Done:

4.  Your Document is now attached to the record:

Very handy!

MSCRM: a Quick look at the Quick View Form

The Quick View Form, new for MSCRM 2013 is a type of Form that you can add onto another form- resulting in a "form within a form".   It has a different look than a subgrid, which is really just a View within a form, showing all the related records.

Use the Quick View form when you want to see the details of a look up field.  You could just click on the look up field name and go to the record (example- Click on the Company name from the Contact record to see the Company details), but sometimes it's handy to have the information right in front of you.  Below is an example of a Quick View form for a custom entity called Service Contract:

Below is the Opportunity involving the renewal of this service contract:

Very handy indeed!  If you'd like to explore the Quick View Form in greater detail, check out the following Blog:

MSCRM: Disabling Quick Create Form

The Quick Create form, introduced in MSCRM 2013, allows users to quickly create records (hence the name!) by entering key information in.  The form is an abbreviated version of the main form.

There may be instances where you do not want your users creating records using this form.  The default behavior fort Accounts, Contacts, Opportunities, and Leads is to allow Quick Create.  To turn this off, System Administrators can navigate to:

Settings ==>Customizations ==> Customize the System

Click on the entity name, in this example it's the Opportunity entity.  Then simply uncheck "Allow quick create":

Save, Publish, and you're all set!

Tuesday, April 29, 2014

Crystal Reports : Zooming for Fun and Profit

Within Crystal, you may change your Zoom perspective to easier see all objects on a page. This makes it easier to arrange the report to be aesthetically pleasing.

Within Crystal select View Zoom from the top-level menu.

Then enter in your desired zoom level. Typically, a level of 50% will allow you to see the entire page. I find this invaluable when formatting and polishing an extra dense report.

Crystal Reports : Trapping for Zero Divide

When division occurs within calculated fields, there is always the risk of getting the dreaded "zero divide error". This happens, obviously, when a calculation tries to divide the number zero.

And since we all know that underlying data cannot be trusted 100%, it is necessary to test for these zero divide errors within our Formula Field.

Example: The formula field called {frmSalesPerDay} is a simple division of {NumSales} by {NumDays}.

So our {frmSalesPerDay} code should look like this:

if {NumSales} = 0 then 0 else {NumSales} / {NumDays}

So, if {NumSales} equals zero, then we simply return a zero and exit the formula. Otherwise, we let it perform the division.

This is important any zero divide error will halt the report completely!

Crystal Reports : Report Sections and Suppression

While designing a report, you may find it helpful to Suppress (hide) sections, easily Revealing them later.

Within Crystal;

1. Right-click on the Section name on the left hand section of the designer.

2. 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.

3. OK your way out.

And as always, you can drive these values with a Crystal Formula! For instance, you could even provide a parameter for the end user to be able to control Section visibility. 

Crystal Reports : Special Fields

A very typical field to see on a report is the "Page N of M" Special field.
Crystal has a collection of "special" fields that you can insert. You can see these fields by expanding the Special Fields folder underneath your Field Explorer.
By inserting the Page N of M field in the Page Footer, we can now see "Page 1 of 23" (for example) on the bottom of each page.

Crystal Reports : MID() and INSTR()

Okay, so the problem is thus; cut out the lastname of any given Contact Name (i.e. Justin Hill should return "Hill", etc). This is actually very easy to do using both INSTR() and MID()

The INSTR() function searches a string for another string. It takes two parameters, like this:

INSTR(string to search, what to search for)

We are going to use it to search for the space in our {USERS.NAME} field.

So, going back to our "Justin Hill" example,

INSTR({USERS.NAME}, " ") = 7 - finds the space between first and lastname

So going back to our original problem, cutting out the Lastname, we can now substitute the starting position in our MID() function with the INSTR() from above like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ")) returns " Hill" (note the leading space).

What you really need to do is increment our starting position by 1, because the Lastname starts one character position after the space. Like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ") + 1) returns "Hill"

Because the MID function is always being passed the location of the space by INSTR it should reliably "kick out" the Lastname.

Then it is a simple matter to make this code into a Crystal Formula, then use that as your sort field in your Sort Expert.

GoldMine : Relinking History

Did you know you can actually relink an existing history to a different contact? (Doing this will "move" the History to the new contact)

1. Find the History item you wish to re-link (everything excepting e-mails is allowed).
2. Right click on the History Item and select Properties.
3. Click on the little black arrow button to the right of the Contact Name, near the top of the History dialog window.
4. The Search Center will now prompt you to search for the appropriate Contact, do so normally, then double-click on it.

The History is now linked to the Contact you just searched for, and this change should be reflected in the associated History tab.

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 : Default E-mail Templates

Did you know you can specify which e-mail templates should be used for e-mail Replies, New Messages and Forwarded Messages?

Once you have your e-mail templates set up, open your Document Center by selecting Go To | Documents from the top level menu.

On the left hand side, you can set each Default E-mail Template, by right-clicking on the appropriate template, then selecting Set as Default, then selecting either "New Message", "Reply", or "Forwarded Message".

Now, whenever you Reply, Create a New Message or Forward a Message, the appropriate template will be used.

Pro Tip : E-mail templates are a great way to use HTML in your signature.

GoldMine : Using Field Colors

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. List Views on the Home Page

You can easily add a list view to your Home Page to view accounts, contacts, or leads.  We’ll use the Leads object in this example:

  1. Go to Setup | Develop | Pages | New.
  2. You can name it: LeadsListView.
  3. Clear contents in Visualforce Markup section and Copy and Paste:

<apex:page showHeader="false" tabStyle="Lead">
    <apex:includeScript value="//"/>
    <apex:listViews type="Lead"/>
    <script type="text/javascript">
                if(this.className.indexOf('listItem') == -1)
           = '_blank';
        navigateToUrl =
       = a;

*Notice the text written in Blue. If you choose to use another object other than Leads you can replace Lead with the API Name of the Object's List View that you’re interested in.

If that is done then create a Home Page Component:

1.      Go to Setup | Customize | Home | Home Page Components
2.      Click on New
3.      You can name it: Leads
4.      Type: HTML Area
5.      Click Next
6.      Component PositionWide (Right) Column
7.      Next step: From the Formatting Controls bar, check the option - Show HTML.
8.      Now paste this code: <iframe frameborder="0" src="/apex/LeadsListView" style="width:100%; height:300px"></iframe>.
9.      Notice the src attribute in step 8. All the Visualforce Pages can be accessed with the URL: and hence the URL of our VF page will be: /apex/LeadsListView.
10.  Save it.

Now, you have to add the same to the Home Page Layout.
  1. Go to Setup | Customize | Home | Home Page Layouts
  2. Click Edit
  3. From the Select Wide Components to Show check the option Leads
  4. Click Next
  5. Decide the sort order for the Wide Area Components
  6. Save

The list view should now be on your Home Page!

(Right-click on the Image, or frame and select Open image, or frame in new tab to view it larger).