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!

Tuesday, June 30, 2015 Which is better – Formula Fields or Triggers?

Many of us are so inclined to use formula fields for data manipulation as it is simpler to build and is an a built-in functionality. But from a performance stand point, because formula fields are evaluated at read time, reading records from database can often be slower. In earlier tips we've given a brief view into triggers, but please watch our next month tips for more details on this topic.

To be continued.... How to add custom fields to Tasks or Events?

To insert a custom field on Task object or Event Object, you cannot directly add the fields on to these respective objects. Instead, you will have to define the custom field on the master Activity object and add the element to desired task or event layout as shown below:

Select which activities receive the custom field and save the object changes.

Written by Prabha Krishnamurthy How to upload multi-select picklist values using data loader?

For inserting or updating multi select pick list fields using Dataloader, append the values for the field with semicolon (;).  The tendency is to use a comma, but in this case a semi-colon is the right choice.

For example, for a multi select field "Type", containing the values “A” and “B”,  the csv spreadsheet column for inserting the data would be entered as: A;B.

Written by Prabha Krishnamurthy

Monday, June 29, 2015

Crystal Reports : Currency Formatting

Any numeric field can be formatted as "currency" by:

1. Right-click on the field itself
2. Select "Format Field"
3. Go to the "Common" tab
4. Select your Style (i.e. thousands seperator, etc)
5. Click the checkbox labeled "Display Currency Symbol"

Crystal Reports : Day of the Week

A client asked: I would like to display the day of the week on the detail line along with the date. How do I do this?

Using the example of CREATEON in Goldmine Contact1, you would do the following:

Create a formula using the DayOfWeek function called DayPosition as follows. This will create the numerical position of the date (e.g. 1=Sun, 2=Mon, etc)

DayOfWeek ({Contact1.CREATEON})

Then create a formula called DayName. This will add the name to the report (Mon, Tue, Wed). Use the function WeekDayName and add the DayPosition as the function detail.

WeekdayName ({@DatePosition})

Drag the DayName formula to the detail row next to the CREATEON date. It will list the weekday name of the CREATEON date.

Crystal Reports : Getting the Selection Criteria

When the Selection Criteria becomes very complex for any given report, I often find it helpful to display the whole Selection Criteria right on the report.
To do this, you need to insert a "Special Field" from the Field Explorer.
Select Insert | Special Field.
Drop down the Special Fields list.
The field we want to insert is Record Selection Formula.
You will also notice that the Group Selection Formula is also available here.

GoldMine : Querying Duplicate Records

Here is an easy way to determine how many potential Duplicate Records you might have in your database.

Open the SQL Query window by selecting Tools | SQL Query from the top level menu.

If you want to see duplicates based on, for example, Contact and Phone1, paste in the following Query text and run it:

select count(*), contact, phone1
from contact1
group by contact, phone1
having count(*) > 1

If you'd like to include the Company field in the query as a duplicate check, use this:

select count(*), contact, phone1, company
from contact1
group by contact, phone1, company
having count(*) > 1

Try it! You might be surprised at how many duplicates there might be in your database.

GoldMine : Increasing the Number of Recently Viewed Contacts

One of the nicest things about the new GoldMine is the list of recently viewed contacts on the upper left hand side. However, by default, it only tracks five. To increase this number (99 is the max);

1. Select Tools | Options | System tab

2. Under Navigation | increase Recent items shown

3. Logout and log back into Goldmine

4. Open the Contact Search | select/open Contacts

You will now see the Recently Viewed items list increase as you open new contacts.

GoldMine : Filtering in the 21st Century

Okay, so the idea was to talk about how cool and easy it is to use Filters within the Search Center. And as cool as that is, your average user doesn't particularly care; when using the Search Center they're after one Contact. 

So, instead of seeing the Search Center as a place when we can SEE Filters, let us think of it as a way to BUILD Filters. Anyone who has used the Filter Builder (which is still available under Tools | Filters) will know the pain involved with this.

To do this;

1. Open up your Search Center by clicking on "Search" from the top Toolbar.

2. Use the "+" (plus) and "-" (minus) buttons in the upper right hand corner to add or remove levels to your Search.

3. To save this as a Filter, click the Save button in the upper left hand corner, then select Save As Filter.

4. You'll be prompted to give the Filter a name. You'll also notice that when you Save this Filter, it's saved "for you", that is; under your Username. Hit Ok.

Now the Filter is available and can be used anywhere Filters are applicable.

*Pro Tip: When adding levels to your Search, you can change the connectors between them from AND to OR by using the drop down provided to the left of each level.

Thursday, June 25, 2015

ZOHO CRM - Following records using the Feeds Module...

You can quickly track and review the activity on records in Zoho CRM using the Feeds module.  Simply "Follow" the records of interest by opening the record and clicking the Follow button (top right).
To quickly view activity on your records of interest...

  • Open Feeds Module, by clicking on "Feeds" in the top navigation bar (if its not showing, you may need to ask your adminstrator to add the module to the list of active modules - Setup-> Customization-> Modules-> Manage Modules (then click Feeds on Left side and click Right pointing arrow).

  • Click "Followed by Me" down the left side and you'll see the list of records you are following.
  • Choose a Record and click the arrow on the right to reveal the information. (Note clicking the record name will open the record)
  • Three Tabs of information are available
    • Info (general information)
    • Timeline (record history displayed in timeline format)
    • Conversations (social and email).
You can also use Feeds for Group Chats.  Simply Create a Group and start posting!  You can even attach documents to your posts.

ZOHO CRM - Easily add contacts to your CRM from any text with the CONTACT EXTRACTOR!

Zoho's suite of products just keeps getting better!  They just added the FREE CONTACT EXTRACTOR application which allows you to quickly add leads or contacts to your CRM by just highlighting the text that contains their contact information!

Example - receive an email from a new contact at an existing company...

  • Open the email in your CRM using the Chrome Browser
  • Highlight the contact information 
  • Click the Contact Extractor Icon

  • Review the data

  • Click Add Contact

And the contact is added to your CRM!!!

You can install a copy of Contact Extractor from the Google Chrome Web Store - its FREE!

ZOHO CAMPAIGNS - how to easily reach all those recipients that didn't open your email...

Hopefully, as a Zoho CRM user, you are aware of the advantage of integrating Zoho Campaigns with your CRM account.  (If not, please see our earlier post!)

Campaigns is a nice little tool for email marketing, providing valuable feedback from each recipient - did they open the email, did they click any links in the email, or did it bounce?

Chain Campaign is a handy, built-in feature that allows you to target only those recipients that didn't open your first email.

To create a Chain Campaign...
  • Open the original Campaign
  • Click the 'chain' icon

You can then update the content of the email to try to improve your open rate.

Use the 'Send mail to' button to target a follow-up email to those recipients that did open your first email.

Use Social Share to share your email campaign on your Facebook, Twitter or LinkedIn accounts.

MSCRM 2015: Calculate Gross Profit Margin with Rollup Field

I added a few custom fields and a small amount of JScript to the quote form to calculate the gross profit margin.
  1. The fields to create:
    • Profit (optional): currency type field that will provide the total profit in dollars.
    • Profit Margin %: a decimal type field.
    • Total Cost: a currency rollup field. Here’s what mine looks like. I have a custom field called Cost on my quote product form that brings in the product cost from the product catalog.
  2.  Put your custom fields onto your form. I also put the Total Amount field on my form again but put it closer to where I was calculating the GPM so that it was obvious to the user which numbers were being used in the calculation.
  3.  Add Jscript to the OnChange event for the Total Cost and Total Amount fields:
    • function calcGPM() {
        var totalCost = Xrm.Page.getAttribute("new_totalcost").getValue();
        var totalAmount = Xrm.Page.getAttribute("totalamount").getValue();
        var profit = totalAmount - totalCost;
        var gpm = (1 - (totalCost/totalAmount)) * 100;
  4. Publish and test your changes!

MSCRM 2015: Filter Contacts Out of Company Name Field on Contact Form

A client recently asked about the Company Name field on the Contact form: when you do a look up, it shows both contacts and accounts. Why would that be? Don’t we just want to see accounts here?
This is an out of the box field and you can see the description provided by Microsoft below. 

I think in most cases users would be choosing an account and not a contact for this field. If you have a lot of accounts and contacts in your system, it might be worthwhile and necessary to filter out the contacts so that users are only searching on accounts. You can also use this method to filter by other items like the state or country of the contact’s address. Opportunities and Quotes have a similar field called Potential Customer that can
be either an account or contact so you could follow the same procedure with a few tweaks for those forms. 

Add the following JScript to the OnLoad event of the Contact form.

function addEventHandler() {
function addFilter() {
        var filter ="<filter type='and'><condition attribute='contactid' operator='null' /></filter>";
        Xrm.Page.getControl("parentcustomerid").addCustomFilter(filter, "contact");