Wednesday, October 29, 2014

Crystal Reports : Programming Comments

I never do this, but I probably should. It's often that I'll be asked to modify a report I haven't looked at in years. So there is always some "ramp up" time spent just figuring out what the heck I was doing. This can help cut down on that;

Did you know that you can save programming Comments within your Formula Code? You simply prefix each commented line with "//", like this;

//Formula to choose correct descriptions - 04/23/2012 Justin Hill
//If we can't find the code, simply return UNKNOWN

This is a great way to comment very complex formulas, and really shines when you need to come back to them after time has passed and you might have forgotten what you did!

Crystal Reports : Percentages as Cosmetics

I don't know why I've always loved this trick. It's a silly hack on using a custom currency symbol to force a percentage sign as part of the field object. I abhor "combining" textboxes and field objects within the Crystal Designer, so I like this approach.

1. Right-click on the field in question, select Format Field.
2. Click the "Display Currency Symbol" checkbox.
3. Click the "Customize" button.
4. Click the "Currency Symbol" tab.
5. Change your currency symbol from "$" to "%".
6. Then change the "Position" to "-123%".

This forces the percentage symbol to appear at the end of the numerical value.

This will give you percentages thus: "87.22%".

Crystal Reports : Hiding Report Sections

Okay, so a report is made up of Sections, right? The Report Header/Footer, the Page Header/Footer, Details, etc. And we are able to Hide or Reveal any section we want within the designer by right-clicking and choosing the appropriate item.

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.

A great use for this is when you're dealing with a Report made entirely of aggregates, that is, mostly Footers and Summaries. You can add columns to the Detail section to inspect and troubleshoot problems and simply Hide it again when finished.

Pro Tip: 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 : Converting to the Numeric Data Type

Almost all conversions in Crystal are easy. Until you get to playing with dates. But I digress.

To convert a String to a Number, simply use the TONUMBER() function.

This will allow you to perform math upon the text field in question.

Keep in mind that Crystal will do the "best that it can" when converting. If something isn't obviously a pure number, the conversion may not work and simply return a blank or NULL. 

Crystal Reports : Converting Numeric data to Text

This couldn't be easier in Crystal. Honestly. It's nuts. Anyone who has struggled with any kind of explicit conversion in TSQL or programming languages will appreciate this.

Within a formula, convert to text by using the TOTEXT() function. 

This will allow you to use String functions (i.e. Mid, Left, etc) on any Numeric field.

This changes not only the formatting of the output but the underlying Crystal object data type.Try it and have fun!

GoldMine : Filtering History

A GoldMine customer asks: There are so many items in the History tab that I can't find what I am looking for.

To correct this:
* Within the History tab, Right-click > Options > Filter.
* This brings up the Activity Filter window.
* Check the Activate Filter box and choose your criteria.
* Select User, Activity Type, Date Range, Activity Codes, Result Codes or Reference Text.
* Click OK.

The History tab will now show only the items that fit your criteria. To release the filter when you are finished, Right-click > Options > Release Filter.

GoldMine : Tagged Records

Did you know that you can "tag" Contact Records within your Search Center, making them a "pseudo-group"?

When viewing results in your Search Center, you can check the box on the left of each row to Tag the record in question. Once one or more records are Tagged, this becomes an "Active Filter", which means it can work with all the Filter-aware function of GoldMine. These include Building a Group, Merging an Template, Global Replace, etc.

Keep in mind that once you Tag records, they will remain Tagged (and therefore the Active Filter) until you Un-Tag them (right click within the Search Center, select Release All) or restart GoldMine.

Couple this functionality with the ability to conduct multi-level Searches (click the plus sign button in the top right of the Search Center, and you've got a very powerful tool.

GoldMine : Who Changed What? And When?

So we've all had this conversation (or variants of it);

"SOMEONE changed the customer type on this record! Yesterday they were a Customer, and today they are labeled as a Vendor!"

Basically, the problem is data control. How do we prevent these mistakes from happening? How do we track down the source of the error when it does happen? There two few fronts upon which to open our battle;

1. Prevent the mistake from happening by locking down (setting to Read-Only) critical fields. This is done on a per-field basis within each fields' Properties. Of course, this introduces considerations; the tighter something is locked down, the less useful it is (in general). Now, if someone needs to update the Customer Type field, they need to track down a GoldMine administrator (or someone allowed to make the change for them).

2.  Log changes in History. This also is done on a per-field basis, also in Properties. The idea is that once you set "Log Changes in History", every time that field changes, you'll... get a History. Be careful though, this can "junk up" and bloat the History tab and make it less than informative.

Also, don't forget about the Summary Tab! It always shows the last update date and user.

GoldMine : Understanding Codes in Picklists

Ah, codes! Where would the marketing world be without you? I'm talking about those three-character codes that every GoldMine system seems to accrue over the years. Consider the "Result Code" field on the Complete a Phone Call window. It's used to specify a call result, but only allows three characters. So, a typical Result Code picklist might look like this;


And COM might stand for "Complete", FUP for "Followup Needed", LVM for "Left Voicemail", etc. But what about users new to the system? It may not be immediately apparent what NI (Not Interested) means, or FUP.

The answer is to comment your picklist items. If you use a double-slash ("//") within a picklist entry, everything after the slashes is ignored. So, the same list with comments would look like;

COM //Call Completed
FUP //Followup Needed
LVM //Left Voicemail
NI //Not Interested

Choosing any of those items automatically ignores everything after "//".

Confession: The more astute readers amongst you will undoubtedly realize that BECAUSE the Resultcode field is only three characters long, there is no harm in using picklist items of any length. Only the first three characters will ever be captured in this example. But comments are good to get into the habit of using. 

GoldMine : Changing Your Interface

...and by Interface, I am talking about the top Tabstrip above your Contact Record. You know, where all the separate windows open up when you invoke them (E-Mail Center, Calendar, etc).

But did you know you can "go back" to the older style windowed interface? It's true. All you need do is click on the small tab at the left of the strip, the one with the four boxes on it. This will release all your tabs into windows.

To switch back, simply maximize and of the new windows; your Tabs should be back where they started.

But why do I care about this? Sure, it's a neat trick, but does it DO anything? Actually, it allows you to;

- Select Window | New Contact Window from the top level menu to get two Contact records on the screen at once.

- Certain functions in GoldMine, like "Merge Visible Records" require you to use the windowed interface.

- If you use multiple monitors, it allows you to arrange different GoldMine windows wherever you want them, and they're always visible.

Give it a shot!

Monday, October 27, 2014

ZOHO CRM - View and Edit records in a spreadsheet format

Did you know that right inside of Zoho CRM, without having to export and reimport, you can view and edit module records in a spreadsheet format?

To open the "Sheet" view - click the module in the top navigation bar and then click the sheet icon on the right:

Note this will open only those records that are present in the view selected.

You can then Edit the data present and even add records.  No need to add an ID in the ID column when adding a record, one will automatically be assigned.  To save click the save icon in the far left corner of the sheet.


ZOHO CRM - Replacing Picklist values in records

Sometimes we find we want to edit a pick list value, but are deterred thinking how painful it would be to go back and change the value in all of our existing records, or have records out of sync.  Well here's the easy way to do it...

I have the pick list field "Account Type" in my Account Module and I want to change Hosp.  to Hospital...
  • Set-up->Customization->Fields
  • Choose Accounts in the modules drop down
  • Click the Replace link to the left of the Account Type field
  • Choose the old value you want to replace from the drop down
  • Type in the new value in the text box provided
  • Click Replace
  • Now you've quickly and easily updated the pick list value and the value in all the records that had Account Type = Hosp.


Did you know there's a screen in Zoho CRM where you can quickly and easily configure how fields map from a Lead into the Account, Contact and Potential records when the Lead is Converted?

Set-up -> Customization -> Fields

Choose Leads as the Module, then click Map Fields:

Standard fields come already mapped, but this is a great way to create or modify the mapping of custom fields. The only limit is that you must map fields to fields of the same type.  Picklist - Picklist, text-text, etc...

Sunday, October 26, 2014

MSCRM 2013: Getting Creative with the Account Image Field

In a previous blog, I explained how to add an image to an Account record.

If you are not currently using this field on the Account form, you'll see the generic image shown below:

Why not utilize this field to provide information with a quick visual?  Example- one of my clients marks their customers who have unpaid invoices with a red square:

When an employee takes a call from a company, they pull up the crm Account record and immediately know the customer has been red-lighted for non payment.

You can get creative with this image field- I'd love to hear about the creative way your organization is using it!


MSCRM 2013: Subgrid Issue- Disappearing Page Navigation

When a subgrid on a form has multiple pages associated with it, page navigation appears in the lower right corner of the subgrid, allowing you view all the related records:

When you get to the last page, the page navigation disappears- leaving you stuck:

Microsoft is aware of this issue.  I have spoken directly with tech support and was told it will be addressed in a future update.  What to do in the meantime?  

Refreshing the screen (hit your browsers refresh button or press ctl-r) resets the subgrid to Page 1.


MSCRM 2013: Quick Tip- Opening a Record From a View

We have all experienced looking at a list of records displayed in a view and clicked on a field that took us somewhere we didn't want to go, or did nothing at all!  Let's look at some examples.

Example 1: Below is a screenshot of the records displayed in the View "Open Opportunities".

Since I'm used to Topic being the first column, I inevitably click on Potential Customer by accident and end up looking at the Account record.

Example 2:  In some cases, the link to the record may not even be on the view.  The "Opportunity Products" (aka Line Items) subgrid on the Opportunity Form is a perfect example:

Quick Tip: Double-clicking in the blank spaces (highlighted below) will open the record!

Example 1:
Example 2:


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!

Thursday, August 28, 2014

Crystal Reports : Ignoring Time Precision

Here is something very handy when you start to deal with databases that track both a date and time precision for records.

Let us imagine we are trying to group call records based on {CALLLOG.CREATEDATE}, but when we create a simple Crystal Group on this field, we end up with a bazillion little groups, like this; (may look different depending on your database back end)

01/01/2011 13:45:015
01/01/2011 13:47:001 on and so forth. What we are really after is "calls per day", so instead of Grouping on the raw database field, let us Group based upon a formula;


The DateValue function returns only the date portion of the field, so now you'll only have one group for each unique "day" in your database.

I've always been a huge fan of grouping on formulas, it allows you total control over what values are being "grouped on", essential when dealing with less than pristine data sources; here you could even choose to exclude certain values, or build in any logic you'd like. Try it.

Crystal Reports : Count Formulas, not Fields

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 : Ordering Groups

When Inserting a Group in a Crystal Report, the report then becomes automatically sorted by the highest level Group. This is usually no big deal, but sometimes, you just want a little control over "what comes first", by which I mean "what is printed on the first page".

Let us consider my father's management report for Hill's Heating. In his accounting software, Hill's Heating appears as a normal company alongside customers, so it would always appear on the sixth or seventh page of the Report, alongside the rest of the "H's".

So what I did was instead of Sorting the report on {CONTACT.NAME}, I created a Group on it instead. Then went into my Group Expert (Report | Change Group Expert from the top level menu) to enable the Specified Sort Order under Options. You'll be able to select it from the "in ascending order" drop down list.

Once you select Specified Order, the "Specified Order" tab becomes available. Here you can explicitly add the values you'd like to appear first in your "sorted" list (by selecting the items from the "Named Group" drop down list).

You will also NEED to go into the "Others" tab and select "Leave in their own Groups", otherwise any Group not defined the in the Specified Order is resigned to this "Others" category.

This way, I could force Hill's Heating to the top of my report, then resume normal sorting after that first entry was out of the way. Try it.

Crystal Reports : Ode to the Tractor Printer

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

Crystal Reports : Using the Groups Expert

You may need to change the existing report "Groups" after defining them.
To do this, select from the top-level menu, Report | Change Group Expert.
Here, you will see all your defined Groups. You may select any of them and use the "up" and "down" arrows to change the Group order.
This is a nice way to regroup the report without having to do a lot of work.

GoldMine : Create your Cockpit; Adding Toolbar Items

Why we never think to do more of this, I'll never know. The problem is thus;

To Complete a Call, I go to Complete | Unscheduled Call | Outgoing Call. I have to do that EVERY TIME. It's a pain, and I don't even know it. I'm so used to being forced into navigating menus, I never stop to think "this is dumb".

Same deal with Scheduling something. I go to Schedule | . If I'm always scheduling Calls, why am I always picking "Calls" out of a list of possible menu-items? It's inherently inefficient. 

You can add any menu-item in GoldMine to your toolbar;

1. Find and click on the "little down arrow" which is all the way to the right of your toolbar.

2. Select "Add or Remove Buttons".

3. Select "Customize", the go to the Commands tab.

4. From here, click-drag the desired menu-item up and onto your toolbar. Buttons will spread themselves to make room.

5. To remove buttons from the toolbar, right click on it and select Delete.

6. When you're done arranging your masterpiece, OK your way out.

You'd be amazed at what shaving a few mouseclicks off can do for productivity. Try it!

GoldMine : A Few Simple SQL Queries

I wish more folks would use the SQL Query tool in GoldMine. But I think that's mostly because I'm a data geek. I love seeing what's underneath the system, all those extra columns that we never observe from the graphical interface.

At any rate, here are a few simple queries that you can run right now (Tools | SQL Query). These queries will return ALL the columns. But once you get a sense of what's there, it's easy enough to pick out just the columns you want;

A list of all calls completed in the last seven days;




A list of all appointments completed in the last thirty days;




Just the user, the date and the notes of all appointments completed in the last thirty days;




...and don't forget the all-powerful Right-click | Output To | Excel. Have fun!

GoldMine : Using the Notes Tab Effectively

I see this on a weekly basis; the Notes tab comes up as a problem. Whether I'm upgrading an older GoldMine to the latest build, or trying to report on history, this can always present itself as a challenge. The reasons are thus;

- The notes tab is free-form HTML text. As such, is usually riddled with carriage returns and all manner of nonsense. Exportation becomes tricky to anything other than, say, Excel or directly into a SQL server. Kiss any kind of text-file export goodbye.

- Depending on the version of GoldMine you're running, the Notes tab is limited. Which means it's running out at the bottom. No, really. If you've been tracking a decade of call history in the Notes tab, you might want to scroll down and make sure years 1-3 are still there.

Technical Note: GMPE 8.5 and up is not affected by the notes limitation.

- Reporting is painful. There's no way to differentiate between Calls and Appointments. What was just a simple note, and what was actual action for the client? So, any kind of meaningful measurement like "Calls per day" becomes impossible. 

In my experience, the Notes tab excels in one thing; storing general info about the client. I've seen driving directions, parking validation procedures, shipping instructions, kids names, you name it. Which is all good stuff to have, but it's general. It doesn't run on for pages and pages. 

Remember, the History tab is there to record all your Calls, Appointments, Actions, etc. It's reportable and unlimited.

Pro Tip : The Notes tab can be disabled on a per-user (or global) basis in Tools | Options | Record | Tabs.

GoldMine : Alarming Everything... And Dealing with the Consequences

Certainly, in GoldMine, you may alarm every kind of Activity Type. This is done by checking the Alarm box directly on the "Schedule a..." window in GoldMine. Advanced Alarmers will sometimes set the alarm date/time specifically, depending on what kind of window they want before their activity. For instance, if I schedule an appointment that's an hour away, the default ten minute lead time isn't enough.

And, of course, Alarms summon the Alarms Window. This is the "screen pop" that happens when GoldMine is open, and from here you can do a few different things;

1. Click "OFF" to turn the selected Alarm off permanently. The scheduled item is left intact.

2. Click "Snooze" to delay the next alarm screen-pop by X minutes. You can choose your favorite interval.

3. Click "Ignore" to snooze for one minute only.

4. Click "Suspend Alarms" to prevent any further alarms until you restart GoldMine.

5. Click "Show/Hide Notes" to inspect the scheduled items' Notes.

6. Click "Delete" (in the lower left-hand corner) to DELETE THE SCHEDULED ITEM. This is the same as if you deleted it from the Calendar. And just as irreversible.

7. "Complete" works as you would expect, it completes the selected Alarmed Item.

8. "Schedule", "Attach a Process" and "View Contact" all work as you might expect; they bring you to those areas of GoldMine when clicked.

What no one ever talks about is "When" to Alarm. The Boss will typically say "Alarm Everything, because Everything is important!" And, in his defense, that's true. But what happens when there are Too Many Alarms? For starters, the scheduled user will get into the habit of continually clicking "Suspend Alarms" because they are annoyed by the constant screen-pop. From what I observe in the field, a better result is achieved through alarming priority items; allowing the presence of an Alarm to denote importance versus being a simple nagging reminder.

GoldMine : Using the Search Center to Create a Filter

Did you know that you can save any Search in GoldMine as a Filter?

1. Bring up your Search Center by clicking on the "Search" button on the top level toolbar.

2. Perform your Search normally.

3. Now, click the "Save" button in the top left-hand corner of the search center. Select "Save as Filter".

4. Give your new filter a friendly name, hit OK.

5. You're done! The Filter now appears in your collection of filters.

Pro Tip : Don't forget that you can add/remove levels to/from your searches by clicking on the "plus" or "minus" buttons in the top right-hand corner of the Search Center.

Monday, August 25, 2014

ZOHO CRM - Using Workflow Alerts to Update Customers

A great way to keep your customer's informed on the progress of their Cases in your system is to use a Workflow Rule in conjunction with an Alert to send your Customer an Email when the status of their Case changes.

STEP 1: Create an Email template that pulls in and nicely formats the applicable fields from the Case/Solution modules that will provide your customer with sufficient insight into the progress made on the case.

STEP 2: Set up the workflow rule to trigger when the Case Status field is updated.

STEP 3: Create and associate an Alert to the Workflow Rule, using the email template created above, that gets sent to the Customer's Email.

ZOHO CRM - Removing Modules you're not using.

Zoho CRM is built for action! There are a total of 20 modules that help you manage every aspect of the Sales Process: from Marketing all the way to Customer Support.  But suppose you only want to use Zoho CRM for Marketing and Sales?  How do you remove all those other modules that are cluttering up your navigation menu?  EASY!

Set up -> Modules -> Manage Modules

Move the modules you are not using to the Unselected Module list by clicking on the module name and then clicking on the left pointing arrow.  Then click Save. This will remove them from the Top Navigation Bar.

If you're using many of the modules and want to better organize them in the navigation bar, see our tip on using Tab Groups.


When you are using most of the modules Zoho CRM has to offer,  often times you find yourself hunting across the top navigation bar and down the over-spill list for the name of the module you need to use.  Use the Tab Group feature to better organize your modules and make them easier to find.  Tab Groups are kind of like folders.  You create a Tab Group and then place modules into that group.  When you select that Tab Group ONLY the modules associated with that Tab Group are displayed in the navigation bar.

To create a Tab Group:  Set up -> Tab Groups -> Create Tab Group
Give your Tab Group a meaningful name and select the modules for the Tab Group by clicking on the module name in the Available Modules list then clicking the right pointing arrow to add the module to the selected module list.  For example, you might want to have a Tab Group called Marketing, that includes Campaigns and Leads.  You can also assign which Profiles will have access to the Tab Groups you're creating

To Use a Tab Group: Mouse over the area next to your company Logo to reveal the select arrow
This is the tricky part!  You've created your Tab Groups easily enough, now how do you use them?  Simply mouse over the area of your screen next to your logo to reveal the Tab Group Select Arrow. Click on the Arrow and Select the name of the Tab Group you want to use.

MSCRM 2013: Viewing Activities in Related Entities

Here's the scenario- 

You've got a custom entity, let's call it "Job".  A Job is created when an Opportunity is Won.  You'd like to have all the Activities from the Opportunity visible in the Job form.  

Quick View form to the rescue!  If you are a System Administrator or Customizer, you can create a Quick View Form in the Opportunity:  

Place the Activitites Subgrid on the form:

Save and Publish.

Next, Add the Quick View Form to the Job Form:

Save and Publish.  

That's it, now you've got your Opportunity Activities displayed on the related Job form.


MSCRM 2013: Printing Views

Printing a view is a quick way to get a “report” and you don’t need to be a System Administrator or Customizer to do it!    

From the web browser (not CRM for Outlook), navigate to a View, click the gear in the right corner and select “Print preview”:

A print preview window will pop up:


MSCRM 2013: SPRING ’14 Update- Social Listening

The Spring ’14 update of Microsoft Dynamics CRM brought some notable changes, among them is Social Listening.  Social Listening is a service that monitors social media.  This service is included at no cost if your organization has a minimum of 10 Professional Licenses.  It can also be purchased separately (Dynamics CRM and Social Listening must be on the same Office 365 tenant to integrate).  

Let's walk through the set up.  First, you will receive a notification from Microsoft if Social Listening has been included with your Dynamics CRM subscription.  Upon receipt of this notification, click the link to get started:

Once you've signed in to Social Listening, you'll notice it has the same look and feel as Dynamics CRM 2013.  Set up your searches by clicking on the logo, then select SETTINGS:


Finally, Click the “Add search topic” to define your searches:

For more detailed information on setting up searches, click here:

Once you have your Social Listening searches defined, integrating with Dynamics CRM is easy.  You must have System Customizer or System Administrator security role.    
Sign into CRM, Navigate to SettingsèAdministration,  select Microsoft Social Listening Configuration:

After accepting the Disclaimer, select your Solution.  After clicking the Select button, it will be greyed out, confirming your selection:

Finally, we can create a Dashboard for Social Listening results.  Click SettingsèNavigationèCustomize the System.  Select Dashboards and click on New:

In this example, we select the 3-Column Overview dashboard, click Create:

Give your Dashboard a Name, then click the “Insert Social Insights” icon:

Select Search Topic:

Select a Topic for the Chart:

Then Select the Chart to Display:

Repeat for each Chart on your Dashboard.  Save and Close when you are finished:

Don't forget to Publish!  Locate your new Dashboard:

Every Part of the Dashboard is clickable and will take you to Microsoft Social Listening for detailed analysis.


Friday, August 22, 2014 Drag and Drop Scheduling

Would you like to access your calendar and create an Event from an Account, Contact, or Lead list view, you can with Salesforce’s Drag-and-Drop Scheduling.  

To enable Drag-and-Drop Scheduling Go to Setup-> Customize -> User Interface -> Go down to Calendar and Check:
Enable Click-and-Create Events on Calendar Views, and Enable Drag-and-Drop Scheduling on List Views.

After your administrator sets up drag-and-drop scheduling, you can click the Open Calendar link at the bottom of a list view to display a weekly view of a calendar underneath the list. Then, you can drag a record from the list to a time slot on the calendar to quickly create an event associated with the record. When the calendar is open, you can:

Select the Change link to switch to any calendar to which you have access

Click the Previous Week () and Next Week () icons to view the previous or next weeks in the calendar

Click the Date Picker icon () to choose a specific date on which to view the calendar

Select the This Week link to view the current week in the calendar

Select the Show Weekends checkbox to display weekends on the calendar

Click the Close icon () to close the calendar at any time. Alternatively, you can click the Close Calendar link at the bottom of the list view to close the calendar.