Monday, January 27, 2014

Crystal Reports : Calculating Age

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 : 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?

Crystal Reports : Using a Formula as a Group

One of the most effective techniques I've learned is how to group on a formula.
Think of it this way: the logic of the formula can be whatever you want it to be. So, any "high-level" corporate logic can be expressed here.
Take for example a table called HISTORY in which are listed history items per user.
Let us create a formula that will "kick-out" a value based on HISTORY.DATE.

IF {HISTORY.DATE} > CURRENTDATE - 30 THEN "History within 30 Days" ELSE
IF {HISTORY.DATE} < CURRENTDATE - 30 THEN "History beyond 30 Days" ELSE

Now this formula can be used as a Group.

You will notice that the last line is an ELSE {HISTORY.DATE}. This is because if a {HISTORY.DATE} does NOT fall into our formula criteria, then it will be "skipped". The final ELSE works as a "catch-all".

Crystal Reports : Groups 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!

Crystal Reports : Parameters with Multiple Values

This is a blast from the past, but came up recently during our last Crystal training presentation. How to print the values inside a parameter that allows the user to select multiple items?

Let us suppose we have a parameter field; {?UserID}, which allows the end user build a list of Users to report on.

Create a formula called {@UserList} and paste in the following code:

JOIN({?UserID}, ", ")

The JOIN function in crystal will "join" all the multiple values together into a string for us, using the second parameter as a delimiter; in this case a comma.

Try it out!

GoldMine : Increasing the Number of Recently Viewed Contacts

By default, the "Recently Viewed Contact" list along the left-hand side of GoldMine only shows a handful of contacts. You can expand it to 99 items by performing the following;

1. Select Tools | Options | System tab

2. Under Navigation, increase the number of Recent Items Shown to 99

3. Okay your way back out, GoldMine may need to restart.

I find having this list as long as I can make it invaluable! It becomes, in a sense, a "hot" list of all the folks I've been talking to lately. I also find myself using the list more than actually Searching.

GoldMine : Turning the Trash Can On/Off within the E-mail Center

Did you know that you can disable the Trash Can within the GoldMine E-mail Center? Actually, can either turn it *OFF*, or set it up to automatically purge itself.

To access your Trash Can settings;

1. In GoldMine, select Tools | Options | Email Tab.

2. Click on "More Options".

3. Click on the "E-mail Center" Tab.

4. To DISABLE your Trash Can, simply uncheck the box labeled "Move Deleted E-mail To Trash Can".

5. To force your Trash Can to empty itself, simply check the box labeled "Empty Trash Can when...".

6. Ok you way out.

Some folks prefer to disable their trash can as a VERY full trash can can sometimes impact performance when working within the E-Mail Center.

GoldMine : Changing Font Size

As I grow older (and my glasses thicker), I find myself always looking for ways to make my screen easier to read. One of the ways you can do this in GoldMine is to use "large fonts".

To enable this;

1. In GoldMine, select Tools | Options.

2. Select the "Record" tab.

3. Check the box labeled "Use Large Font".

4. Hit Ok.

5. GoldMine will now prompt you to relogin.

You'll notice that this makes EVERY font in GoldMine a bit bigger. I prefer it as it's much easier to read, but you'll need to experiment on your own to see what works best for you.

GoldMine : Switching Between Tabbed and Windowed Interfaces

One of the nicest things about GoldMine Premium is the way it displays itself through the "top tabstrip". What this means is that as you open GoldMine areas, they persist as tabs alongside this strip. Those of you who have used GoldMine in the past may wish to turn this off and re-enable the "child window" interface (the way it used to be).

To do this;

1. In GoldMine, simply single-click on the little tab all the way to the left of the top tabstrip. (Looks like four little boxes).

2. This will "release" your tabs into child windows. Some folks find this preferable as it allows more information to be shown on one screen.

3. To revert back to the tabbed interface, simply maximize any Child Window.

Have fun!

GoldMine : Viewing Completed Activites on the Calendar

Did you know that you can view your completed activities on the Calendar? It may sound strange but can actually be quite useful.

To turn this on;

1. Right-click within the Calendar and select Activities.

2. From the "Completed Activities" list, simply choose which Activity Types you wish to appear on the Calendar.

3. Ok your way out.

You'll notice that your Calendar is now showing Completed Activities (with a strikethrough font). I find this useful to simply see at-a-glance my completed service from the previous week.

Sunday, January 26, 2014

Zoho CRM: IPAD App: Mapping Leads/Companies/Accounts Near Me

Another incredibly useful feature of the IPAD App is the ability to display a map of  Leads/Companies/Contacts 'Near Me.'  

How is 'Near' defined?  It's the distance from your current location to the address of the Lead, Company or Contact.   The Default value is 10 miles, but you can change this by navigating to Settings ==≥ Search Settings.  The maximum distance is 100 (miles or km).

Now, to see Leads Near Me,  navigate to the Leads area, Click on the View Name (My Open Leads, for example).  The list of Views is displayed.  Select the Map View "Leads Near Me".  The Leads near you are listed on the left side of your screen, a map with corresponding push pins is displayed on the right.   Tap on a pin to see the name of the lead.

Tap on the pin a second time to bring up a menu.  Select 'Get Directions From here' to display directions from your current location to the selected Lead.

Very Handy, indeed!

Happy CRM-ing :)

Zoho CRM: IPAD App - Locate a Lead/Company/Contact on Map

Hi All,

Obviously loving my Zoho IPAD App, especially the Map Integration.

Here's how to locate a Contact (the process is similar for Leads and Companies) on a map:

1. Select the a Contact from your Contacts list.

2. Tap on the Details Page (Called a 'Page', but looks like a business card in a wallet).

3. Select the 'locate address on map' option

4.  To return to the Contact after you are finished looking at the map, double tap the home button on your IPAD, select the Zoho CRM screen.

Happy CRM-ing!

Zoho CRM: IPAD Mobile App - Set Map Settings to Use Google Maps

Happy New Year!

Got the new IPAD air for Christmas :)

One of my first tasks was to set up the Zoho CRM mobile App.  I really like the Map feature of this App, which uses Apple Maps.  Since I prefer to use Google Maps, I was pleased to see that this could be enabled in the Settings area.  Here's how:

1. In the navigation bar at the bottom of the screen, click on 'More'.

2. Select Settings

3. Scroll down until you see Map Settings:

4. Set Use Google Maps to ON

That's it.

Happy CRM-ing!

Monday, January 20, 2014

MSCRM: Drill Down Chart Example

Charts are a useful tool when you need to communicate a lot of data quickly and effectively.  Sometimes a chart isn't very useful if the data is not broken out in a meaningful way.  Dynamics CRM allows you to drill down into the data so you can extract something meaningful.  Let's look at an example.

 First, Navigate to Sales-Accounts, and expand the Charts area on the right edge by clicking on Charts:

If you haven't made customizatons to charts yet, you'll see the 4 standard System Charts when you click on the down arrow:

I've selected the New Accounts by Month:

As you can see, it's not that useful.  All 10 of my accounts were created in July.  But, I can 'drill down' by clicking on the blue bar to get some more details about these accounts:

Click on the chart and box appears prompting the user to select a field.  I would like to see a breakdown by state so I select 'Address 1: State/Province' from the list:

Next, click the blue arrow which means "OK"

...Voila!  My Drilldown chart is displayed, and it is much more interesting than the original chart.

MSCRM: Social Insights

One of the best new features in the release of Dynamics CRM 2013, is its integration with Social Insights. With Social Insights, you can see connections, company profiles, breaking news, LinkedIn profiles, Twitter Feeds, etc., all within your crm.   Best of all, it's free!  (Professional License required).

This new feature will also allow you to save data to your CRM.  To illustrate, let's say I have Microsoft as an Account in my crm.  I don't know much about Microsoft, so most of the crm fields in our Microsoft Account record are blank.  In contrast, the Social Insights area on the account record contains a lot of data and displays this:

If I click on the Sync button (circled in the screenshot above), a form opens up and asks what fields I'd like to sync.  It's a one-way sync that gives you the option to pull data into your crm.  I can sync all, some or none at all.

Since most of the Microsoft Account fields in our crm did not contain data, this was very handy!
So, how do you get Social Insights?  Comes with my Professional License, right?  But it must be installed into your crm by your System Administrator.

Here's how:

1. Import the Solution.  The link below contains the download and detailed instructions for importing the solution.  Complete steps 1-9 as described in the instructions.

2.  Want User or Admin Guide? Click on the link below (and scroll to the bottom).

Once the solution is imported, the Social Insights area will appear automatically in the Default Contact, Account, Lead, and Opportunity Forms.  No need to customize the Default forms.


Notes:  If you are NOT using the default forms,  insert Social Insights into your customized form as a web resource like this:

Organizations still using Windows Live Ids have reported issues with not being able to set up the Sync.  I will keep you posted on this issue.

MSCRM: Quick Create

Happy New Year!

Hopefully most of you have transitioned to Microsoft Dynamics CRM 2013 and are exploring the new features.  Today we will take a closer look at the new Quick Create feature.

The Quick Create feature is available from anywhere in your CRM.  The purpose of this feature is to allow you to quickly create a record by entering a small amount of data.  Best Practice Tip - go back into the record later and fill in more detailed data when you are not pressed for time.

Let's get started!

Step 1:

Click the Create Button found at the top of your screen.  This button is always visible.

Step 2:

Select the type of Activity or Record you would like to create.  Activities that may be created include: Task, Phone Call, Email, or Appointment. Records that may be created include: Account, Case, Competitor, Contact, Lead, or Opportunity.

In this example, Contact has been selected.

Step 3:

A Quick Create Contact form drops down over the top section of your screen.  Note that the only required field is the Contact's last name.

Enter your Contact data into the fields, click on Save when you are finished.  The form will close, a message will appear at the top of your screen stating that the Contact was added.

That's it!  Don't forget to go back into the record later and enter additional data as it becomes available.