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: