Monday, September 29, 2014

Crystal Reports : Where Did My Group Tree Go?

Very recently a client asked: "where did my Group Tree go?"

They were referring to the left-hand windowpane where the Groups are displayed in a hierarchical view.

You may enable or disable the Group Tree by selecting View | Group Tree from the top level menu.

Crystal Reports : Introduction to MID()

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 : Zero Divide Blues

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 : Selection Logic Unleashed

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 : Setting Grouping at Run Time

This is neat: Let us suppose we want a report to be Grouped on either Users.UserID or Users.UserTerritory, depending on a Parameter field.

So, provide a simple parameter, {?Group} and let the user set it to "UserID" or "Territory".

Now, within your report, create a formula field called {@Group1} and paste in the following code:

IF {?Group} = "Territory" THEN {USERS.USERTERRITORY}

Now, create your Group in crystal, and use {@Group1} as your Group Field.

This way, the user can select how the report should group itself, affecting all summaries, etc.

Try it!

GoldMine : Streamlining Scheduling

In the Date field when scheduling an activity, instead of typing in a date or using the lookup to bring up a calendar, type in 1D. Then, when you tab or click to the next field the date will be set for 1 day from today.

Do you want to schedule the activity 2 days from now? Just type in 2D; and for 3 days 3D.
D works for Days, W for Weeks, and M for Months. So to schedule something 2 months from now, just type in 2M!

Sometimes you have an activity that you want to complete on a particular day, but it doesn’t matter what time you do it. In the Time field, you can prioritize activities instead of scheduling specific times. Instead of entering a time, simply enter A, B, etc. and GoldMine will prioritize them in alphabetical order according to your notations.

Please note: Prioritizing activities means they will show up in the Task portion of your Calendar, but will not be assigned a block of time, so we recommend you use this shortcut for flexible activities only. All activities will also appear in your Activity list.

GoldMine : Changing Data and Label Colors

Did you know that you can change both the Data and Label color of any field on the Contact Record? This can help make "required" or important fields stand out for the end user. To do this;

(You must have Master rights)

1. Right-click on the field you want to modify.

2. Select Properties, go to the Color tab.

3. Use the "Colors" button to modify the Label and/or Data color.

(Expert users will notice that they can drive these values with xBase expressions - very cool)

4. OK you way out.

One of the more popular uses of this I've seen is to turn certain labels red to make them stand out as "required"*.

* - I put required in quotes because you can also set the field properties to be truly Required, but that is not impacted by anything listed above.

GoldMine : Who's Logged In?

Did you know that you can inspect who is logged into GoldMine at any time? There are two ways to accomplish this;

1. Select Go To | Calendar from the top level menu. Then click on the "Peg Board" tab. From here, you can see everyone who is in GoldMine and the amount of inactive time they've accrued since their last keystroke.

If you don't see all the Users in the list, right-click, select "Users", and add the desired names to the list.

2. Select Tools | Users Settings. From here, you can also see who is logged in or out. This feature is only available to "Master" users.

This is a great way to see exactly how many of your precious GoldMine licenses are being consumed at any given moment.

GoldMine : Using the Clipboard

This is such a useful feature, it's a tragedy that it's so hidden. You are able to copy values from the Current Contact Record into the windows clipboard, then paste them into something else as separate lines.

1. Select Edit | Copy Contact Details from the top level menu.

2. When prompted, select which values you want to copy from the record. A good place to start is selecting "Name, Address".

3. Hit OK. The corresponding values from the Contact Record are now in the clipboard.

4. Simply Paste the contents into a Word document (or other windows application) as you would Paste anything else.

This is a great way to send someone contact details without having to copy out each individual field on the record.

GoldMine : Multiple E-mail Addresses and Websites

Did you know the E-Mail and Website fields on the Contact Record can actually store multiple values? They appear to be "normal" fields, but when you click into them, a window is brought up that lets you choose the following options;

- Hit "New" and/or "Delete" to add/delete a new value.

- When adding an value, you can mark it as "Primary" (which is the one that shows on the record).

-  E-Mails can be marked as using Rich Text by clicking the associated checkbox.

Pro Tip : You can see all the available E-Mail addresses and Websites under the Details Tab as separate rows.

Thursday, September 25, 2014

MSCRM 2013: Bulk Edit More Than 250 Records

I recently added a new field to the Account entity in CRM.   The field, "Client Since", is the year the Account became a client.  Since I just started my business this year, this field can be set to "2014" for all of my existing 3,000 Accounts. Fortunately, I can use the Bulk Edit feature accomplish this.<yay!>

Problem: You can only Bulk Edit the records on the current page of a View, which is a maximum of 250 records.  To update 3000 records, I'll have to do 12 bulk edits.  <Sigh.>

Solution:  Use the Outlook Client!  CRM for Outlook does not have a limit on the number of records per page.  Here's how to update your records in the outlook Client:

Step 1- Select the View with the records you wish to update

Step 2- Pin the View: Right Click the View and select "Pin this Tab"

Step 3- Hit CTL-A to select all the records in the View

Step 4- Click Edit from the Command Ribbon

Step 5- Enter the value for the field you wish to update, click OK


MSCRM 2013: Keeping Your Online CRM Up to date

I recently asked a client if they had updated their CRM with the Spring '14 updates.  They responded that they had the "cloud-based version of Microsoft Dynamics CRM and Microsoft takes care of all the updates".  Mostly True.

Some updates require a System Administrator to install the update.  Microsoft sends out plenty of email announcements when updates are available, but in case you missed them, here's how to check if your CRM Organization is up to date.

Step 1: Navigate to Settings==>Administration

Step 2:  Scroll Down!

Step 3: Click on Install Product Updates:

If there are no Product updates, you will see this message:

If there are product updates, click the Install Update button.  Note: Your CRM may be unavailable for a few minutes during the update.


MSCRM 2013: Viewing User Security Roles

Setting up Security Roles is a very important step in configuring your CRM.  It's also very complicated and usually an iterative process.  As the months go by, it's easy to forget which users have been assigned which roles.  If you are not a System Administrator, you may not even know what Security Role(s) have been assigned to you!  You can, of course, go to the User record and click Manage Roles to view each user's Security Role(s), this can be tedious if you need to check multiple Users.

Fortunately, there's an out of the box report called the User Summary which can help you sort things out- and you don't have to be a System Administrator to run it.

Step 1: Navigate to Settings==>Administration==>Users

Step 2: Click "Run Reports" and select User Summary

Step 3: Select All Applicable records and click "Run Report"

The report shows each user in CRM and the Security Role(s) assigned to them

Very handy!

Wednesday, September 24, 2014

ZOHO CRM - Quickly and Easily go back to where you were...

As you are moving around in and through the data in your CRM, sometimes you find you need to go back a revisit a record you were just looking at.  You might find occasionally that it seems to take a few clicks too many get back there... No More!  The easy way to navigate back to records you were just in is to click the little clock icon below and to the left of the Top Menu Bar. (see below).  A list of the previous10 records are listed with clickable links!

ZOHO CRM - You can put help tips and hidden fields on Web forms!

Zoho CRM provides an easy drag and drop interface to create a form you can host on your website. When the form is completed and submitted it will create a record in your CRM from the data entered into the fields on the form. There are Lead, Contact and Case forms.

Easy enough to drag and field onto the form, but here's the tip... after you add the field to the form, mouse over the field to expose the settings icon (see below).  Click the settings icon to bring up the Field Properties window where you can choose to make the field a hidden field, mandatory, and add a hint next to the field that can help your website visitor determine how to complete the field.  You can also change the Label here, if you'd rather the label on your form be something different than your field name.


ZOHO CRM - Create Sections in your Zoho Web Forms and other enhancements

The Zoho CRM drag and drop interface for creating web forms is really nice and easy to use, but it has its limitations... you can basically create a single column list of fields with a title, and choose your background and text colors.  Wouldn't it be nice to maybe organize the form fields into sections (especially if you have a lot) with section titles and lines?   Here's how you do it...

Drag the fields into the window in the order you want them on your form.  Mark fields mandatory, and add hints if desired (see previous tip on how to do that).  Next, enter the URL links, ownership assignment strategy, and notification information.  The last page provides the embedding options.  I usually recommend the <iframe> method so that the embedded form is automatically updated when the form is changed in CRM, but if you want to augment the form, copy and paste the html code into an html editor.

Zoho CRM creates the form as a <table> with two columns.  To add Section headers with an underline, simply insert rows between the fields where you want to create a section break:

<tr><td colspan="2">SECTION HEADER <hr></td></tr>

if you want to give these section headers some style, simple add <span> tags with embedded CSS with the text like this:

<span style="font-size: 22px; color:red; font-weight:bold;">SECTION HEADER </span>
results in:


There is no limit to what you can do here, just be careful not to change any of the Zoho CRM specific data!

Monday, September 22, 2014 - Adding Trusted IP Addresses

Save time by adding your IP address to Trusted IP Ranges, which will allow you to not have to use your security token when loading data with Dataloader.
When accessing the Dataloader you have to append your Salesforce token to the end of your password, and if you make changes to your Salesforce environment, Dataloader does not automatically update, you have to re-login; this means re-entering all your info along with the long security token code…
How can this process be sped up? 
First you have to find out your IP address.  Go to and copy your IP address.

Then, add this as the 'start' and 'end' IP addresses in Security Controls->Network Access

Now just log into the data loader with your org’s Username and Password and you are all set! - Making related lists work best for you

You can sort records on your related lists so you are viewing the most useful data, and also edit how you see the records.  This is important and can be adjusted in the related lists.  For example, you may want to see records based on a particular date or number field, not necessarily just the most recent record.  Lets take a look:
Go to Edit Layout on your sidebar if you are on a record detail page, if not on the page itself, you can access the Page Layout from Setup, under Customize for Standard objects or Create for Custom objects.
Once on Edit Page Layout, scroll down to the related list you’d like to edit, Click on the wrench icon.

To set up your columns, choose the fields from the Available Fields you want to display in the list, add them to the Selected Fields list, and put them in the order you want (top-to-bottom will come out left-to-right on the page).
Now, lets organize what records we want to view.  Below the columns section, there is a "Sort By:" section.  

Choose a field from the dropdown, then pick ascending or descending to pick which values go on the top, you can pick from most field types.

There you go, now you are seeing the data and records that best assist you and your organization! - Adding Multiple Fields to an Object

Need to create a bunch of fields in an object?  Well instead of going one-by-one, and through the many steps it requires in the standard way, take advantage of the Schema Builder and save time.

First, go to Setup, then under Build in the left hand column, click on Schema Builder.

Once the Schema Builder has opened, you will see two tabs (Elements and Objects) in the left hand column of the Schema Builder.

Next, click on the Objects tab and it will bring up an option to select and view any of your objects, you can also use the search box to find an object, in this example we’ll use Account:

Next, click on the Elements tab, which contains the field type list.

You’ll see all the current fields in the object.  To add a new field, just drag and drop the type of field you’d like from the Elements list.  In this example we’ll choose “Text”.
Once the element is dragged and dropped, a new popup window will appear as shown below:

Fill the object information into the popup window and click Save. The field will get added to the object.

Simply repeat this quick and easy process in order to add multiple fields and save a bunch of time!