Wednesday, July 31, 2013

Crystal Reports : Age Calculations

One of the many things you will want to do with a bona-fide date field is calculate age.
Crystal makes it easy for us here; as long as we're comparing two date type fields, we can perform simple math.
Let us suppose that the date field in question is {CONTACTS.SOURCEDATE}, which represents when a particular prospect was imported into our database.

1. We can see how many records were imported in the last seven days

{CONTACTS.SOURCEDATE} <= CurrentDate() - 7

2. Or how "old" any particular record is, expressed in days


3. To calculate the age in years, we must use the Crystal DateDiff function:

DateDiff("yyyy", {CONTACTS.SOURCEDATE}, CurrentDate())

The "yyyy" specifies we want the answer in years; "m" and "d" are also options (months and days, correspondingly)

Crystal Reports : Record Counts

When creating a "Summary", using the Count function may not be desirable. It is often the case that a report must only return the number of unique contact records, as other tables (i.e. History) may contain multiple rows for each customer.
Select Insert | Summary.
Select the fieldname you wish to summarize.
Make sure to select Distinct Count as the Summary Operation.

Crystal Reports : Inserting Images

You can display your company logo on a Crystal Report (or indeed any picture file).
Go to Insert Picture from the top-level menu. Then browse for the picture you want to insert. Once it's inserted, you can resize it to fit the report.

Crystal Reports : Week Number and Date Functions

Most reports use some kind of date math or date grouping (i.e dollars per month, week, etc).
Let us suppose we need to return the week "number" within any given year based upon a simple date. (Our fieldname will be {SALES.SALEDATE})

The DATEPART() function is what we need here:


1. The "ww" specifies that we want the "week number" returned.
2. {SALES.SALEDATE} is the date field to evaluate.
3. The final parameter, crSunday, specifies on what day the week starts.

Crystal Reports : Formulas acting upon Formulas

You will undoubtedly have noticed that there is no real right way to do anything in Crystal.
A Running Total could be substituted by a Summary on a Formula, and so forth.
Where technique really matters is in duplication of labor. Effective use of Formulas is one way to cut down on this:

Let us consider a calculation on an Invoice report;


But just to make it tricky, we're making this part of a formula, because we only want our calculation to happen for a certain year;


Now to make it worse, you've got dozens of these calculation fields, all specifying different criteria (i.e. Year, Month, etc). But that's fine, you create the fields normally and everything works.

Suddenly management decides they need to increase their prices by 5%, and this must be reflected in the Invoice report.

It is easy enough to change our formula code (you'll notice our Quantity and Netprice is now encapsulated in parenthesis)


And this works too, but now you have a full days work ahead of you adding that .05 to each and every calculation field.

A better way:

Create a formula called {@frmExtPrice}:


Then change the calculation formula thusly:


This way, any changes that need to be made to the way the Invoice calculates line items can be modified in frmExtPrice once and all the calculation fields automatically "have it".

It is easy to code all your logic directly into one big formula, but keep the bigger picture in mind.

While you're at it, why not make your .05 into a parameter?

Friday, July 26, 2013

Zoho CRM - Leads, Leads & Leads


In Zoho CRM, Leads are a very powerful module with several critical functions that can:

Three Quick Tips to help you use leads more effectively

1) Search first, then add the new lead if it doesn't exist.

Searching first will reduce the amount of possible duplicates.  If the Lead already exists, then simply add your additional data to the existing form.

2) Add as much data/intelligence in the form as possible.

 Adding or completing as many fields in the form, prior to converting, will save you from manually editing three records (Contacts, Accounts & Potentials) after the fact.

3) Convert the Lead only when you feel there is a defined interest, time frame, intent or monetary value. Or when there are multiple contacts per company.

Converting a lead implies that you now have a relationship, intent/interest and/or monetary value that this person will add to your revenue / commissions.

Converting a Lead actually MOVES the data into three forms (Contacts, Accounts & Potentials).  Once converted, the Lead form is gone forever.

Using Leads also allows you to better delineate between prospecting and actual active opportunities that require more time, effort and expertise from your sales team.

Happy Selling!

Thursday, July 25, 2013

GoldMine : Finding Contacts without E-mail Addresses

Eventually the question comes up; who has an e-mail address and who doesn't?

The veterans out there immediately understand this is a somewhat difficult proposition. E-mail addresses in GoldMine are not on a "one-to-one" relationship with the contact record. What this really means is that the email address field is not available within a Filter.

So we need to build a Group based on a Query. First we will build our query.

Go to Tools | SQL Query and use the following query:


Click "Query", click through a few records to make sure the resutls are correct.

Now click on the "Groups" tab and click on the New Group button.

Give your Group a friendly name, click Ok.

Select "SQL Query Records", Next.

At this point you can click through the wizard (there are some sorting options you can experiment with) and Finish.

Once your Group is built, it is basically interchangeable with a Filter as far as GoldMine functionality goes. Keep in mind that Groups are static, however, and must be manually refreshed whereas Filters do not.

GoldMine : Scheduling to a Group

First you will need to create the filter or group which specifies the contacts to which you want to assign the activity. Go to Tools > Filters & Groups and create your filter or group.

To then schedule an activity to that group, begin by selecting Schedule. Choose the type of activity.

In the next window you will find the Filter/Group tab. Check the box labeled ‘Schedule to a group of contacts’ and use the drop-down list to choose the appropriate filter or group. (You may also choose ‘ALL Contact Records’.) A Pending activity will be posted to each contact record, and the activities will appear as usual in your Activities and Calendar views.

GoldMine : Using 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 : Clients with Too Much 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 : Using Filters in the Search Center

This is a really nice feature; the ability to Preview a Filter or Group directly within your Search Center. A lot of folks like this because it lets them see more information about the Contacts within a Filter than the Preview Tab in the Filters window.

To do this;

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

2. Click on the Filters/Groups button on the top "strip" of the Search Center

3. This will open up a Filters and Groups pane along the left hand side, in a tree format

4. To Preview any Filter or Group, simply doubleclick on it

5. GoldMine will prompt you whether or not to apply a "limit" on the Previewed Filter or Group. This is not required. If you'd like to see the entire contents of the Filter / Group, simply click "No"

Your Search Center should now be showing you all the Contacts within the Filter / Group. Keep in mind that your Previewed Filter is "activated" within the Search Center at this point, and to Release it, you must eventually right-click within the Filters and Groups pane on the left hand side and select "Release Preview".

Tuesday, July 2, 2013

MSCRM: Lookup Filtering Based on Another Lookup

A Client recently asked ‘Can I filter the choices in a Lookup based on the value selected in another Lookup field?” The answer is “Yes”. CRM now includes filtered Lookup fields.

In the example below, I will show how to filter the “Responsible Contact” Lookup on the Case form to only include the contacts associated with the selected Case account.
In “Settings”, “Customization”, “Customize entities”, choose the Case Form.
On the Form, select the “Responsible Contact” field.
By clicking the “Change Properties” button, you will see the field properties for the “Responsible Contact” Lookup field.

Under the Related Records Filtering section of this form, check the check box “Only show records where”. This will filter the available values of this Lookup field to records related to the value selected in another Lookup field. Choose the “Account (Accounts)(Cases)” in the top drop down box. In the “Contains” choose “Company (Accounts) (Contacts)”. This will only show the Contacts that are related to the Account chosen in the Customer field above.
Please note that you can also select whether or not to allow users to turn off the filter—this gives the  user  the ability to choose whether a filter is “hard,” limiting users to just the filtered records, or “soft,” allowing users to select something outside of the filter. This is important because some filters are there to prevent users from selecting invalid record combinations, while others are there for user convenience, but may need to be overridden for exceptions.
This works well for straight-forward filtering of Lookups but for more complex instances, java script might still be required to remove values from the second Lookup.

MSCRM: Bulk Editing

Are you changing the same information in multiple records? Save some time making those changes to many records at once by using bulk edit. Whether editing 3 records or 250, bulk edit is a great feature to use to save time.

To edit more than one record at a time, select multiple records from any entity list at once by using the check box located on the left side of each record or hold down the “Shift” key to select multiple records at once. If you’d like to select all of the records on the page, place a check in the checkbox on the top leftmost corner of the records display. Each record selected will be highlighted.

Then click the Edit button - located on the ribbon at the top of the page.

This will open the Edit Multiple Records window. At this point there are multiple fields from the entity that you can make edits to. Bulk editing has endless limits and can save you much time in the long run.

MSCRM: Opening Automatically to a Specific Area/Entity in Microsoft Dynamics CRM for Outlook

Suppose you’d like to have your Outlook session automatically open to the Dashboards from your Microsoft Dynamics CRM system.

This is an Outlook setting. Perform the following steps:
·        In Outlook, drop down on File

·        Choose Options

·        Choose Advanced

·        In the “Start Outlook in this Folder” - Choose the Dashboards folder.

You can choose any folder in the CRM system that you would like to open up with in Outlook.

Note that this a per user Outlook setting and cannot be centrally administrated from CRM.

Monday, July 1, 2013

Adding Custom Fields in Zoho CRM - The right way!

When adding custom fields in Zoho CRM to meet your business needs, one should take time to consider these three tips.

1) Focus on what reports you will want or need to see first.

2) Create the fields in the correct module/form.

3) Scrutinize when or why custom fields should be duplicated across several modules.

 Focus on what reports you will want or need to see first.

Defining the reports, or mapping out what data you want to see and when, will drive what custom fields you actually need to create.  

Example: "I want a daily report that shows how many outbound "calls" were made by each Sales Person pertaining to each Product or Service."

Create the fields in the correct module.

In the above report requirement you find that in the Activity "New Call" module, there is no field that allows a Sales Person to enter in data relating to a specific product/Service.  Meaning there is no way to report on this.

Thus, you would create a new custom field called "Related to Product/Service" in the "New Call" form. 

If you also wanted a daily report on "tasks" relating to a specific Product or Service, you would also create a new custom field called "Related to Product/Service" in the "New Task" form.

Scrutinize when or why custom fields should be duplicated across several modules.

Having the same custom field in several modules or forms can cause data integrity issues.

Example: If you create a new custom field called "Product of Interest" in the Contact, Account & Potential Forms, it can skew your reports if the customer's interest changes and the Sales Person doesn't change or update each field in each form.  If the Sales Person only changes/updates the "product of Interest" in the Potential form....and your reports are pulling data from the Account Form, then your reports are inaccurate.

In this case, you can create a custom field "Product of Interest" in the Potentials module/form.  As the specific Potential is related to a product....not the Account or Contact.  Or you can create the field under the Account Form, depending on your business policies or needs.....but not both.

In the first example of "New Call" & "New Task", you WOULD create a duplicate field as a Sales Person can make a CALL regarding Product/Service 1 and create a TASK regarding Product/Service 2.  Thus, there would be now data redundancy or data integrity issues.

So, keeping these three tips in mind when creating custom fields in Zoho CRM can save you many headaches down the road.

Happy Selling!