Monday, July 27, 2015

Crystal Reports : Run Time Grouping

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!

Crystal Reports : Using Custom Parameter Values

Parameters need not only drive selection criteria;

We will create a parameter that will control whether or not taxes are added to each Invoice.

Create a parameter called {?Tax} and give the users two options, "Tax" or "No Tax".

So,. based upon what user chooses, the formula will calculate itself differently. Here is how it might look;

{@frmTotal} :


So, you can see that if "Tax" is selected, the Invoice total adds eight percent to itself. We need not test for "No Tax" explicitly as there are only two choices.

You could also do it this way:


Giving the end user the ability to change the way the report is calculating (rather that just modifying the Selection Criteria) is an extremely powerful way to create some longevity for your reports.

Crystal Reports : Converting Text and Numbers

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!

GoldMine : Identifying NO SERVICE Contacts

Ah, the joys of commerce! When a customer leaves a bill unpaid, attacks service personnel with a garden hose, or needs to be "cut off" for any other reason, we need to remark that fact in GoldMine. There are a few different ways to make this happen, but of course, the most important thing is to educate the users as to how the system works.

The explanation would go something like this;

"When you see NO SERVICE in (location), provide no service to the customer."

But where is an appropriate location?

1. The Notes tab. One of the very few legitimate uses of the Notes Tab. Simply add a note of NO SERVICE and be on your way.

2. Use a field. The Notes Tab may not be visible enough to satisfy some users. Why not put this value right on the contact record? So, mark their Customer Type as NO SERVICE, or something like that. This ensures the user will probably see it as they are already looking at the top half of the Contact Record.

3. Use Record Alerts. An ultra-mega answer to this age old question. You define these via Edit | Record  Properties  | Alerts Tab. Once an alert is enabled for a record, it requires the end user to actually acknowledge the Alert.

Pro Tip : Use option #2. 

GoldMine : Out with the Useless

Useless Contact Records. They are a blight upon a fine GoldMine system!

You know exactly what I'm talking about; records with no History, records that haven't been touched in years. Records with no phone, no address, no email. How do they get in there? No, seriously. Why would you do this?

Here are a few different methods for identifying Garbage Records.

1.  Search for missing data. Simply, within the Search Center, use the "Is Empty" operator. Then just start searching for Contact, Lastname, Address, etc. The results may astonish you.

2. Search by LastContOn. This is the date of the most recent call to any Contact Record. It's available in the Search Center (way down that list next to "Search By"). Of course, you'll want to use the "Greater Than" and "Less Than" operators. So, if you search for LastContOn <= 01/01/2010, you get everyone that HASN'T had a call since 2010.

3. Search for missing history. This is done with an SQL query. Go to Tools | SQL Query and paste the following into the Query textbox.

This shows all records that have NO history whatsoever.


This shows all records with no history since 2005.


Pro Tip : All these methods can be used to add records to a Group, which can then be used by the Delete Records Wizard.

GoldMine : The Search for Spock

Yes, I've been binging Netflix all weekend. And, as my fellow nerds will be quick to point out; this is arguably the worst film in the franchise. But, seriously, if Spock was in our GoldMine database, how would we find him? More importantly, how would we find him if someone screwed up his record?

1. Set your default Search Field.

Go to Tools | Options | Lookup. You can select "Contact" or "Company" to be the default lookup field. By far and away, people like to use "Contact" here. This way, every time you hit "Search" from the toolbar, it will bring up the Search Center in Contact search mode.

So you hit "Search" and type in "Spock", but nothing comes up. You begin to wonder; was Spock a first or last name?

2. Try searching on Last Name.

From the Search Center drop down list, find and select "Last". A quicker way to get there is to actually double-click on the Lastname field right on the Contact Record. (Not IN the field, just double-click the field label)  

ProTip : If you ever get lost in the "Search By" list (like I do sometimes), just close and re-open the Search Center.

Still can't find him?

3. Try using Contains.

From the Search Center, select "Contains" from the center drop down list. This controls which operator is used for the search. Using "Contains" on just the Lastname field isn't particularly useful, however. Switch your Search back to Contact and try now.

Lo and behold, the search is over. But I think at some point there was a confusion of Spocks.

4. Always ensure good data is originally entered.

In this case, someone made a little note about him in the Contact field. But the Lastname field is not too bright; it simply uses the last word in the Contact field. Also note the "Doctor" is present, which should actually be put into the Prefix (Dear) field.

The real danger is that a duplicate Spock could be added to the database. Keep in mind that most searching is happening during phone conversations, so if a user can't immediately find what they're looking for, they'll add it as a new record.

Pro Tip : In times of utter desperation, try searching for an e-mail address; by design, this is unique for everyone.

Sunday, July 26, 2015

ZOHO CRM - setting up default terms and conditions for your Quotes

There are two main ways to set up default terms and conditions in Zoho CRM.  The first is to set up a default for the Terms and Conditions field in the Quotes record, the second is to add the text to an Inventory Template.


  • login to your CRM
  • click Setup (upper right hand corner)
  • click Customization->Fields
  • Choose "Quotes" from drop down menu
  • scroll down to Terms and Conditions field in list and click the Edit link (left side)
  • Enter the default terms and conditions in the text box and click save
All Future Quotes will have this default terms and conditions automatically populated when the quote is created.

Formatted Quotes can be sent to customers using 'Inventory Templates', which is very similar to an Email template.  You can use the default or create your own. For creating your own I recommend starting with a copy of the standard Quote template (see next tip)
  • Replace the merge field ${Quotes.Terms and Conditions} with text by deleting the merge field and using the text tool to type and format static text.
  • Click Save

ZOHO CRM - Insert a Page Break in your Inventory Template!

Sometimes our Inventory Templates can span multiple pages.  Wouldn't it be nice to be able to decide where the page break goes?  Now you can, and here's how...
  • Open the Template for editing
  • click the HTML button (towards the right in the tool bar)
  • find the text in the html code where you want to insert the break and paste this code in where the break should occur:

<div style="page-break-after:always;"><br></div>
  • Click Insert
  • Click Save

ZOHO CRM - Trick to Cloning Inventory Templates

Notice in Zoho CRM how when you click on the title of an Email template there's a Clone Button to create a copy of the template at the bottom of the screen, but no such option for Inventory Templates?

Here's a simple trick for cloning an Inventory Template ... copy and paste!
  • Setup->Templates->Inventory Templates
  • Click on the Edit link next to the Standard Quote Template
  • select and copy the body of the template.
  • Cancel the Edit
  • Click New Template, select Quotes and click Next
  • Drop your cursor in the body and paste in the copied code
...sometimes the best tips are the simplest!

Wednesday, July 22, 2015

QB Tips: Get Paid On The Run!

Don't miss the opportunity of getting paid, no matter where you are!  Download Intuit's mobile credit card processing app.

 Apple. Download GoPayment from the Apple Store

The next step is to create a login using your email address.  Once approved, you will receive an email from Intuit's Business Services email account. Click on the link and follow the directions.

You will receive a card reader:

Plug the card reader into your phone & open the GoPayment app.  Sign in with your user ID & password you initially set up.  You will need to select the card reader type, tap on the pic that matches your card reader.

Congratulations!!  You can now receive payments on the go!