Thursday, August 28, 2014

Crystal Reports : Ignoring Time Precision

Here is something very handy when you start to deal with databases that track both a date and time precision for records.

Let us imagine we are trying to group call records based on {CALLLOG.CREATEDATE}, but when we create a simple Crystal Group on this field, we end up with a bazillion little groups, like this; (may look different depending on your database back end)

01/01/2011 13:45:015
01/01/2011 13:47:001 on and so forth. What we are really after is "calls per day", so instead of Grouping on the raw database field, let us Group based upon a formula;


The DateValue function returns only the date portion of the field, so now you'll only have one group for each unique "day" in your database.

I've always been a huge fan of grouping on formulas, it allows you total control over what values are being "grouped on", essential when dealing with less than pristine data sources; here you could even choose to exclude certain values, or build in any logic you'd like. Try it.

Crystal Reports : Count Formulas, not Fields

Using simple Summaries (Insert | Summary) in Crystal is great, don't get me wrong, but they are inflexible. For instance, when summarizing (trying to get a count) on {INVOICE.INVNUM}, ALL invoice numbers are evaluated, including duplicates (unless a Distinct Count is being used).

Which usually is fine, but there is always some sort of exclusion to be made; we only want invoices that DON'T begin with "000150" because it a House Account, etc. And you could handle this within your Selection Criteria handily enough; but let's imagine that it screws up other parts of the report; excluding House Accounts impacts other Subtotals, etc.

So to accomodate this little pickle, we will use a formula to count, one record at a time. This is actually a very popular Crystal Technique and you see it a lot in older, more "veteran" reports.

Create a formula called
IF MID({INVOICE.INVNUM}, 1, 6) <> 000150 THEN 1 ELSE 0

So what the formula is doing is evaluating the first six digits of the Invoice, and if it ISN'T a House Account, it returns 1 (we want to count it), otherwise it returns 0 (we want to exclude it).

Now, create a simple Summary on {@InvoiceCount} and make it a SUM operation, this way you get a count of only the records you want (for the particular task at hand), without sacrificing any existing Selection Formulas or Summary operations.

Crystal Reports : Ordering Groups

When Inserting a Group in a Crystal Report, the report then becomes automatically sorted by the highest level Group. This is usually no big deal, but sometimes, you just want a little control over "what comes first", by which I mean "what is printed on the first page".

Let us consider my father's management report for Hill's Heating. In his accounting software, Hill's Heating appears as a normal company alongside customers, so it would always appear on the sixth or seventh page of the Report, alongside the rest of the "H's".

So what I did was instead of Sorting the report on {CONTACT.NAME}, I created a Group on it instead. Then went into my Group Expert (Report | Change Group Expert from the top level menu) to enable the Specified Sort Order under Options. You'll be able to select it from the "in ascending order" drop down list.

Once you select Specified Order, the "Specified Order" tab becomes available. Here you can explicitly add the values you'd like to appear first in your "sorted" list (by selecting the items from the "Named Group" drop down list).

You will also NEED to go into the "Others" tab and select "Leave in their own Groups", otherwise any Group not defined the in the Specified Order is resigned to this "Others" category.

This way, I could force Hill's Heating to the top of my report, then resume normal sorting after that first entry was out of the way. Try it.

Crystal Reports : Ode to the Tractor Printer

To make a report easier to read, you can alternate the background color to help different lines stand out. (Think of the old green and white striped paper you used to put in your tractor printer years ago)

To make this happen, we will need to format the Details Section of our report.

Step #1 : From the top-level menu, select Format Section.
Step #2 : Select the "Detail" section on the left hand side.
Step #3 : Click on the "Color" tab on the right hand side.
Step #4 : Click the [formula] button with the "x-2"on it to bring up the formula editor.
Step #5 : Paste in the following : if RecordNumber mod 2 = 0 then crSilver else crNoColor

This will alternate a white and silver background for each row printed.

Pro Tip : To alternate color for Group rows, paste the following into the Group section color formula: if GroupNumber mod 2 = 0 then crSilver else crNoColor

Crystal Reports : Using the Groups Expert

You may need to change the existing report "Groups" after defining them.
To do this, select from the top-level menu, Report | Change Group Expert.
Here, you will see all your defined Groups. You may select any of them and use the "up" and "down" arrows to change the Group order.
This is a nice way to regroup the report without having to do a lot of work.

GoldMine : Create your Cockpit; Adding Toolbar Items

Why we never think to do more of this, I'll never know. The problem is thus;

To Complete a Call, I go to Complete | Unscheduled Call | Outgoing Call. I have to do that EVERY TIME. It's a pain, and I don't even know it. I'm so used to being forced into navigating menus, I never stop to think "this is dumb".

Same deal with Scheduling something. I go to Schedule | . If I'm always scheduling Calls, why am I always picking "Calls" out of a list of possible menu-items? It's inherently inefficient. 

You can add any menu-item in GoldMine to your toolbar;

1. Find and click on the "little down arrow" which is all the way to the right of your toolbar.

2. Select "Add or Remove Buttons".

3. Select "Customize", the go to the Commands tab.

4. From here, click-drag the desired menu-item up and onto your toolbar. Buttons will spread themselves to make room.

5. To remove buttons from the toolbar, right click on it and select Delete.

6. When you're done arranging your masterpiece, OK your way out.

You'd be amazed at what shaving a few mouseclicks off can do for productivity. Try it!

GoldMine : A Few Simple SQL Queries

I wish more folks would use the SQL Query tool in GoldMine. But I think that's mostly because I'm a data geek. I love seeing what's underneath the system, all those extra columns that we never observe from the graphical interface.

At any rate, here are a few simple queries that you can run right now (Tools | SQL Query). These queries will return ALL the columns. But once you get a sense of what's there, it's easy enough to pick out just the columns you want;

A list of all calls completed in the last seven days;




A list of all appointments completed in the last thirty days;




Just the user, the date and the notes of all appointments completed in the last thirty days;




...and don't forget the all-powerful Right-click | Output To | Excel. Have fun!

GoldMine : Using the Notes Tab Effectively

I see this on a weekly basis; the Notes tab comes up as a problem. Whether I'm upgrading an older GoldMine to the latest build, or trying to report on history, this can always present itself as a challenge. The reasons are thus;

- The notes tab is free-form HTML text. As such, is usually riddled with carriage returns and all manner of nonsense. Exportation becomes tricky to anything other than, say, Excel or directly into a SQL server. Kiss any kind of text-file export goodbye.

- Depending on the version of GoldMine you're running, the Notes tab is limited. Which means it's running out at the bottom. No, really. If you've been tracking a decade of call history in the Notes tab, you might want to scroll down and make sure years 1-3 are still there.

Technical Note: GMPE 8.5 and up is not affected by the notes limitation.

- Reporting is painful. There's no way to differentiate between Calls and Appointments. What was just a simple note, and what was actual action for the client? So, any kind of meaningful measurement like "Calls per day" becomes impossible. 

In my experience, the Notes tab excels in one thing; storing general info about the client. I've seen driving directions, parking validation procedures, shipping instructions, kids names, you name it. Which is all good stuff to have, but it's general. It doesn't run on for pages and pages. 

Remember, the History tab is there to record all your Calls, Appointments, Actions, etc. It's reportable and unlimited.

Pro Tip : The Notes tab can be disabled on a per-user (or global) basis in Tools | Options | Record | Tabs.

GoldMine : Alarming Everything... And Dealing with the Consequences

Certainly, in GoldMine, you may alarm every kind of Activity Type. This is done by checking the Alarm box directly on the "Schedule a..." window in GoldMine. Advanced Alarmers will sometimes set the alarm date/time specifically, depending on what kind of window they want before their activity. For instance, if I schedule an appointment that's an hour away, the default ten minute lead time isn't enough.

And, of course, Alarms summon the Alarms Window. This is the "screen pop" that happens when GoldMine is open, and from here you can do a few different things;

1. Click "OFF" to turn the selected Alarm off permanently. The scheduled item is left intact.

2. Click "Snooze" to delay the next alarm screen-pop by X minutes. You can choose your favorite interval.

3. Click "Ignore" to snooze for one minute only.

4. Click "Suspend Alarms" to prevent any further alarms until you restart GoldMine.

5. Click "Show/Hide Notes" to inspect the scheduled items' Notes.

6. Click "Delete" (in the lower left-hand corner) to DELETE THE SCHEDULED ITEM. This is the same as if you deleted it from the Calendar. And just as irreversible.

7. "Complete" works as you would expect, it completes the selected Alarmed Item.

8. "Schedule", "Attach a Process" and "View Contact" all work as you might expect; they bring you to those areas of GoldMine when clicked.

What no one ever talks about is "When" to Alarm. The Boss will typically say "Alarm Everything, because Everything is important!" And, in his defense, that's true. But what happens when there are Too Many Alarms? For starters, the scheduled user will get into the habit of continually clicking "Suspend Alarms" because they are annoyed by the constant screen-pop. From what I observe in the field, a better result is achieved through alarming priority items; allowing the presence of an Alarm to denote importance versus being a simple nagging reminder.

GoldMine : Using the Search Center to Create a Filter

Did you know that you can save any Search in GoldMine as a Filter?

1. Bring up your Search Center by clicking on the "Search" button on the top level toolbar.

2. Perform your Search normally.

3. Now, click the "Save" button in the top left-hand corner of the search center. Select "Save as Filter".

4. Give your new filter a friendly name, hit OK.

5. You're done! The Filter now appears in your collection of filters.

Pro Tip : Don't forget that you can add/remove levels to/from your searches by clicking on the "plus" or "minus" buttons in the top right-hand corner of the Search Center.

Monday, August 25, 2014

ZOHO CRM - Using Workflow Alerts to Update Customers

A great way to keep your customer's informed on the progress of their Cases in your system is to use a Workflow Rule in conjunction with an Alert to send your Customer an Email when the status of their Case changes.

STEP 1: Create an Email template that pulls in and nicely formats the applicable fields from the Case/Solution modules that will provide your customer with sufficient insight into the progress made on the case.

STEP 2: Set up the workflow rule to trigger when the Case Status field is updated.

STEP 3: Create and associate an Alert to the Workflow Rule, using the email template created above, that gets sent to the Customer's Email.

ZOHO CRM - Removing Modules you're not using.

Zoho CRM is built for action! There are a total of 20 modules that help you manage every aspect of the Sales Process: from Marketing all the way to Customer Support.  But suppose you only want to use Zoho CRM for Marketing and Sales?  How do you remove all those other modules that are cluttering up your navigation menu?  EASY!

Set up -> Modules -> Manage Modules

Move the modules you are not using to the Unselected Module list by clicking on the module name and then clicking on the left pointing arrow.  Then click Save. This will remove them from the Top Navigation Bar.

If you're using many of the modules and want to better organize them in the navigation bar, see our tip on using Tab Groups.


When you are using most of the modules Zoho CRM has to offer,  often times you find yourself hunting across the top navigation bar and down the over-spill list for the name of the module you need to use.  Use the Tab Group feature to better organize your modules and make them easier to find.  Tab Groups are kind of like folders.  You create a Tab Group and then place modules into that group.  When you select that Tab Group ONLY the modules associated with that Tab Group are displayed in the navigation bar.

To create a Tab Group:  Set up -> Tab Groups -> Create Tab Group
Give your Tab Group a meaningful name and select the modules for the Tab Group by clicking on the module name in the Available Modules list then clicking the right pointing arrow to add the module to the selected module list.  For example, you might want to have a Tab Group called Marketing, that includes Campaigns and Leads.  You can also assign which Profiles will have access to the Tab Groups you're creating

To Use a Tab Group: Mouse over the area next to your company Logo to reveal the select arrow
This is the tricky part!  You've created your Tab Groups easily enough, now how do you use them?  Simply mouse over the area of your screen next to your logo to reveal the Tab Group Select Arrow. Click on the Arrow and Select the name of the Tab Group you want to use.

MSCRM 2013: Viewing Activities in Related Entities

Here's the scenario- 

You've got a custom entity, let's call it "Job".  A Job is created when an Opportunity is Won.  You'd like to have all the Activities from the Opportunity visible in the Job form.  

Quick View form to the rescue!  If you are a System Administrator or Customizer, you can create a Quick View Form in the Opportunity:  

Place the Activitites Subgrid on the form:

Save and Publish.

Next, Add the Quick View Form to the Job Form:

Save and Publish.  

That's it, now you've got your Opportunity Activities displayed on the related Job form.


MSCRM 2013: Printing Views

Printing a view is a quick way to get a “report” and you don’t need to be a System Administrator or Customizer to do it!    

From the web browser (not CRM for Outlook), navigate to a View, click the gear in the right corner and select “Print preview”:

A print preview window will pop up:


MSCRM 2013: SPRING ’14 Update- Social Listening

The Spring ’14 update of Microsoft Dynamics CRM brought some notable changes, among them is Social Listening.  Social Listening is a service that monitors social media.  This service is included at no cost if your organization has a minimum of 10 Professional Licenses.  It can also be purchased separately (Dynamics CRM and Social Listening must be on the same Office 365 tenant to integrate).  

Let's walk through the set up.  First, you will receive a notification from Microsoft if Social Listening has been included with your Dynamics CRM subscription.  Upon receipt of this notification, click the link to get started:

Once you've signed in to Social Listening, you'll notice it has the same look and feel as Dynamics CRM 2013.  Set up your searches by clicking on the logo, then select SETTINGS:


Finally, Click the “Add search topic” to define your searches:

For more detailed information on setting up searches, click here:

Once you have your Social Listening searches defined, integrating with Dynamics CRM is easy.  You must have System Customizer or System Administrator security role.    
Sign into CRM, Navigate to SettingsèAdministration,  select Microsoft Social Listening Configuration:

After accepting the Disclaimer, select your Solution.  After clicking the Select button, it will be greyed out, confirming your selection:

Finally, we can create a Dashboard for Social Listening results.  Click SettingsèNavigationèCustomize the System.  Select Dashboards and click on New:

In this example, we select the 3-Column Overview dashboard, click Create:

Give your Dashboard a Name, then click the “Insert Social Insights” icon:

Select Search Topic:

Select a Topic for the Chart:

Then Select the Chart to Display:

Repeat for each Chart on your Dashboard.  Save and Close when you are finished:

Don't forget to Publish!  Locate your new Dashboard:

Every Part of the Dashboard is clickable and will take you to Microsoft Social Listening for detailed analysis.


Friday, August 22, 2014 Drag and Drop Scheduling

Would you like to access your calendar and create an Event from an Account, Contact, or Lead list view, you can with Salesforce’s Drag-and-Drop Scheduling.  

To enable Drag-and-Drop Scheduling Go to Setup-> Customize -> User Interface -> Go down to Calendar and Check:
Enable Click-and-Create Events on Calendar Views, and Enable Drag-and-Drop Scheduling on List Views.

After your administrator sets up drag-and-drop scheduling, you can click the Open Calendar link at the bottom of a list view to display a weekly view of a calendar underneath the list. Then, you can drag a record from the list to a time slot on the calendar to quickly create an event associated with the record. When the calendar is open, you can:

Select the Change link to switch to any calendar to which you have access

Click the Previous Week () and Next Week () icons to view the previous or next weeks in the calendar

Click the Date Picker icon () to choose a specific date on which to view the calendar

Select the This Week link to view the current week in the calendar

Select the Show Weekends checkbox to display weekends on the calendar

Click the Close icon () to close the calendar at any time. Alternatively, you can click the Close Calendar link at the bottom of the list view to close the calendar. Advanced Searching for Custom Objects and Fields

Would you like to save time and be able to Search for Custom Fields and Objects in your Salesforce environment?  If so, Enable Advanced Setup Search (Beta).

Go to Setup -> Customize -> User Interface, and under Setup check Enable Advanced Setup Search (Beta).

You will search by going to your Sidebar search and entering at least 3 characters.  You will then be prompted to press Enter for Advanced Setup Search:       

Along with Custom Objects and Fields, it will also search Groups, Permission Sets, Profile, Roles, and Users. Single Click Login

Is logging into your Salesforce org with a single click possible?  By saving a simple URL code as a bookmark it is.  Here is the code:   

Simply input your password where it says in red, and do the same for your user name (the %40 represents the @ character).  Copy and Paste in your URL, and or in your Bookmark section.  Now with a single click you’re up and running in your Salesforce org!