Thursday, January 31, 2013

Crystal Reports : Currency Fields

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 : Numbers to Text

Conversely, you may use a Crystal formula to convert any Numeric field to Text by using the ToText function.
This will allow you to use String functions (i.e. Mid, Left, etc) on any Numeric field.

Crystal Reports : Text to Numbers

When using Text fields, you can convert them to Numeric by creating a formula field and using the ToNumber Crystal function.
This will allow you to perform math upon the text field in question.

Crystal Reports : Missing my Group Tree

Very recently a client asked: "where did my Group Tree go?"
They were referring to the left-hand windowpane where the Groups are displayed in a hierarchical view.
You may enable or disable the Group Tree by selecting View | Group Tree from the top level menu.

Crystal Reports : Using Background Color

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

Sunday, January 27, 2013

GoldMine : The Search Center - Exporting Records

  When I train new GoldMine users, the question always comes; "How do I get my records out?". The answer, thankfully, couldn't be simpler.

1. Right-click, select "Output to... | Excel".

  That, just that. If I sound like an awestruck geek, it's because I am and I am. I know of no other CRM product existing that makes it easier for an end user to create a spreadsheet out of any list they see in GoldMine.

  For instance, the right-click Output To functionality can be used within;

  - The Search Center
  - The History and Pending Tabs
  - The SQL Query records list
  - The Master Users List fact, it can be used anywhere you see "rows" in a window. Try it and happy list building!

GoldMine : The Search Center - Using Filters and Groups

  Did you know that your Filters and Groups are available within the Search Center? This is much more convenient than using the "Preview" functionality within the Filters and Groups window.

1. From within your Search Center, click the "Filters/Groups" button. You'll see that the left hand side opens up, showing a tree view of your Filters and Groups.

2. Simply open the tree view and select the Filter or Group you wish to view. Double-click to activate it.

3. You may be asked to add a limit to the Search. This it optional and you may click "No".

4. To select the Filters or Groups of a different user, simply click on the "Filters of..." or "Groups of..." items within the tree and select the appropriate username.

Note: After "activating" a Filter or Group, it will stay activated until you release it by right-clicking on the Filter tree and selecting "Release Preview".

GoldMine : The Search Center - Compounding Search Criteria

  Another very useful thing to use when building lists in the Search Center is the ability to search on multiple fields. This is easily done.

1. In your Search Center, click on the little "plus sign" button to the far right of the Search Field.

2. You'll see that an additional "line" to search with is added. You may choose a different fieldname, operator and value.

3. You may continue to add lines in this fashion by hitting the plus sign again. To remove a line, simply click the minus sign.

  This is a very effective way to generate a somewhat complex list easily, Try it!

GoldMine : The Search Center - Adding Columns

  One of the best things ever is the ability, nay, the ease with which an end user can add and remove columns to the GoldMine Search Center. This is a per-user setting and can be changed at will. More useful still is the fact that you will be able to sort on any added column.

1. From within your Search Center, click on the Columns button.

2.  Go to the "Column Selection" tab.

3. Simply select columns to add from the left hand side, then use the "arrow" buttons to add them to the right hand side, where they will be displayed within the Search Center.

4. You may also re-order your columns by clicking the up and down arrows.

5. Ok your way out, there is no need to restart GoldMine.

Try it!

GoldMine : The Search Center - Increasing max records

 Building lists in GoldMine is easy. We're starting to see a lot of our clients re-engaging their Suspects and Prospects lately. And typically, the first step in this process is to build a list. This list invariably needs to find it's way to Excel.
  Ever since GoldMine Premium was released, this is actually very easy to do by simply using the Search Center. But the first thing we must do is understand and modify how Goldmine displays records within the Search Center.

*Note: This is a system-wide configuration setting. When you make this change, you make it for everyone. Large values could lead to a decrease in performance.

1. Select Tools | Configure | System Settings.

2. Click the Display tab.

3. Increase the "Maximum Records" amount to X.

4. Ok your way out.

5. You will need to restart GoldMine to effect the change.

  So, what's X? You want X to be above the maximum number of records you'll ever want to see in your Search Center. So, if your list is going to be "around" 10,000 records, make sure this value is above 10,000. To give you an idea, I have mine set to 75,000. If there is a need to build a list reaching the hundreds of thousands, this would be better served by some back-end SQL Exporting.


Thursday, January 24, 2013

MSCRM 2011: Displaying a Hidden Field Depending on a Check Box Value

A Client recently asked “I’d like to only show an Option set type field if the value of a Check box is Yes”.
This is possible by adding some java script on the On_change of the check box field.

Following is a list of tasks that need to be performed:

Task 1Create a new JavaScript Library that will include the function to hide/show the Option Set field.

  • Go to Settings, Customizations, Customize the System.
  • Scroll down to Web Resources, Click New from the toolbar.
  •  Enter the following Information in the New Web Resource Dialog:
    • Name= “HideOptionSetonChoicefromCheckBox”
    • Display Name= “Hide Option Set on Choice From Check Box”
    •  Language = “English”
    • Type = “Script (Jscript)”
  • Click the Text Editor button
  • Paste the following script in to the source dialog and make the changes described in the Note comments.
function HideShowOptionSet ()
// Note: an_Checkboxname should be replaced with the Name of the field you are examining

// Note: an_OptionSetname should be replaced with the Name of the OptionSet field you are hiding
if (Xrm.Page.getAttribute("an_Checkboxname").getValue() == false)

   Xrm.Page.getControl( "an_OptionSetname").setVisible(false);



} // End of Function 
  • Click OK
  • Click Save
  • Click Publish
  • Click Save and Close

Task 2 -  Attach JScript function to onChange event handler

Next we need to setup the event handlers on the form being modified.
  • Click on the Entities to expand.
  • Select the Entity where the function will be called from and then click OK.
  • Expand the Entity, choose Form and Information form type Main.
  • Now that you are in the form editor we need to attach the event handler to the Form onChange event.
  • Go to the field that will be changed and double-click. This will bring up the field properties dialog.
  • Go to the Events tab and choose to Add a Forms Library
  • The Web Resource Lookup will be displayed, choose the Web Resource added above and Click OK.
  • In the Event Handler section Click Add.
  • In the Handler Properties dialog do the following: Function =  HideShowOptionSet
  • Check Enabled, Check Pass execution context as first parameter
  • Click OK, to close the Handler Properties dialog
  • Click OK, to close the Field Properties dialog
  • Click Save and then Publish form interface.

Task 3 – Test your Changes.

MSCRM 2011: Creating Follow-up Activities

In CRM 4.0, there was a button on the phone call form called “Follow up.” When you clicked this button, a new activity would be created that included the details from the original call. This was a useful way to quickly create and schedule a follow up call.

In Microsoft Dynamics CRM 2011, this functionality has been removed. How can follow-up activities now be created ?

The new activity relationship functionality is a great replacement for the follow-up button.

In 2011, you can create a self referential activity relationship. For example, you can customize the phone call entity and create a 1:N relationship for phone call to phone call. Use the custom label and call it Follow Up Calls.

In the mappings area under the Common area in the Relationship,  you can then map the fields that you  want to be copied from the originating call to the follow up call.
Once this relationship is published, you will now see a navigation bar link for “Follow up calls.” When you create a phone call,  you can now create a related follow up activity that contains the data from the originating call.

This also give us some new capabilities that the old follow up call button did not:
1. Follow up activities are related to their originating call. The 4.0 follow-up activity button would create a new call, but it would have no relationship to the originating call. With the new approach, related activities are joined together, so you can group conversations together.
2. You can now customize what maps when the follow up activity is created. Given that you set the mappings of the 1:N relationship, you can choose what fields to map, and which not to map, even with custom fields.

A few limitations with this approach:
1. You cannot map the party-list type fields, such as the “to” or “attendee” fields. These fields are a unique type of field that actually display the contents of multiple related records (activityparty), so the contents of these fields will not map to the follow-up activity.
2. You have to create a follow-up activity prior to closing the activity, since related records cannot be created from inactive records.

MSCRM 2011: Using Links to Share Data

On each of the entities in the Microsoft Dynamics CRM 2011 system now you will see the “Copy a Link” and” Email a Link” buttons on the ribbon of the entity tab. These buttons are available via the Web and the Microsoft Dynamics CRM Outlook Client.
These buttons are available in the Web Version and the Microsoft Dynamics CRM Outlook client.
Some of the benefits of using these Links:
  • If you need to send a record’s profile (I.E. everything included on the form of that record) or a list of records (I.E. All the records on a View); instead of printing the details of that record and then sending the attachment to a group of people or maybe extracting the results of a view to Microsoft Excel and then send the attachment to a group of people, why not send them a link instead?
·        A link will not take as much space on your Microsoft Exchange (or other third party email) server, imagine sending a Microsoft Excel spreadsheet with hundreds of results and a size of 2 MB. You send that to 10 people on the office and now the attachment occupies 20 MB on the users’ mailboxes. 20 MB doesn’t sound like a lot but imagine doing this dozens of times every year for a few years; we are talking about GBs of attachments stored on mailbox databases.
  • Sending a link is more secure than sending an attachment:
    • The results of a link can only be seen by a user with access (I.E. an Active User account) to your Microsoft Dynamics CRM Organization. If an unauthorized user ends up receiving the link (I.E. You just sent that ‘classified strategic roadmap report’ link of Coca-Cola, Co. to ‘John Doe, VP of Marketing at Pepsi, Co.’ instead of ‘John Doe your company’s COO’) that user will click on the link and he/she will be welcomed by a “Log in to your Microsoft Dynamics CRM Organization” page and that would be the end of it. If you had sent the full report as an attachment, you would probably be in trouble. I know you could have password-protected the attachment but for the sake of this example let’s say you simply forgot.
    • Even if the user has access to your Microsoft Dynamics CRM Organization, security roles would apply to the results that are displayed once that user clicks on the link and logs into Microsoft Dynamics CRM; for example, if you sent a link of a record and a user clicks on it, they would either see the record or see an error message that reads “You do not have permissions to access this record”; the same applies to the results of a particular view, if you send a link to the “Open Opportunities” view to an outside sales rep by mistake, when that user clicks and opens the link he/she will only see their own open opportunities as you have limited visibility to only ‘user owned ‘records on the “Outside Sales Rep” security role.
·         A link can be reused: When I send a link, the users can keep coming back for the “latest info” on that link. For example, if I send the link to an open opportunity to my manager, he/she could keep clicking on the link daily/weekly to follow up with anything that has changed on that record. The same applies to a view, I could send a link to “Appointments on the next 7 days” and that user can click on the link any time he/she would like to see what appointments are coming up on the next 7 days. In other words, the results are dynamic and most scenarios require these kinds of results.

The drawbacks of using the Links:
  • Information is dynamic; as described above, in other words, if you want to capture the way information looked like at any given time, sending a link is not going to work in most cases. A regular export + email with attachment works in those cases.
  • Only users with Microsoft Dynamics CRM access can see the files, in other words, if you need to send something located in your Microsoft Dynamics CRM organization to a user outside the organization (I.E. Your customer just requested a list of closed activities on the last 6 months), sending a link is not going to work.

Some scenarios where you could use these links:
1. Email a link of a record to a co-worker: In Microsoft Dynamics CRM, navigate to a view showing the record you need to email and select the record, after that, click on the “Email a Link” button on the ribbon and then click on “of selected items”.
This will open your Microsoft Outlook and populate the subject and some of the contents of the email for you; just add some extra comments and send the email out when you are ready. You can perform this step with multiple records as well.
2. Email a link of a view to a co-worker: In Microsoft Dynamics CRM, navigate to the view you would like to send out and click on the “Email a Link” button on the ribbon and then click on “of current view”. This will open your Microsoft Outlook and populate the subject and some of the contents of the email for you; just add some extra comments and send the email out when you are ready.
3. Copy a Link: You can copy a link of a record, group or records or a view, the functionality of this button is the same as the one described above with the exception that no Microsoft Outlook window will open up automatically. In other words, you can place the same amount of information you were going to email with the “Email a Link” button into your ‘clipboard’ and then paste it on a Skype or Lync chat box or use it yourself, for example, you can create a Shortcut on your desktop with the link to a view or report you use often. Instead of navigating your way around Microsoft Dynamics CRM into that view or report, you can create a shortcut and access it directly.

The scenarios above show you how to take advantage manually of these two buttons but if you are a system administrator within Microsoft Dynamics CRM, there is one more thing you can do to use this functionality.
If you have any workflows that send email notifications regarding records in CRM, you know that these emails will show the “regarding” record in Microsoft Outlook when they are received, as long as the user has the Microsoft Dynamics CRM client for Microsoft Outlook installed and configured. This means that if the user is receiving this email on a home computer or even their phone, they would have to wait until they are connected on a machine with the client configured; unless you paste the link of the record directly on the body of the email.
Suppose you have created a workflow that will notify a user when a new Lead has been assigned to them. On the configuration for the email being sent, once you have built the body of the email, click on the “Insert Hyperlink” button.
On the “Insert Hyperlink” menu, configure the fields to reflect your preferences. Select the Full Name as the text to be shown, select the “Record URL(Dynamic)” on the URL field from the Lead fields and click ‘OK’. The Hyperlink will be inserted in the body of the email. Clicking on the link will now open the record automatically.
You can see how having the link to the Record(s)/View can really come in handy!

Wednesday, January 23, 2013

QuickBooks: Implementing a Payroll Backup Plan

One of the first, and perhaps most important, steps of implementing a backup plan for issuing payroll in the event of an emergency or disaster is – don’t wait until the last minute to run your payroll!

The type of disasters you could be facing will determine the type of payroll backup plan you need to have in place.  Here are just a few items that are helpful (and why) and things you can do in the event of an emergency:

  • ·         A laptop (with a fully charged battery),
  • ·        QuickBooks and a backup of your QuickBooks data file – even if your power is out your laptop is fully charged and with a backup of your QuickBooks data you can create paychecks, even if you have to hand write them.
  • ·         Hard (paper) copies of pay stubs and or payroll summary reports.  If you have no power, no laptop, or your computer crashed and you know how many hours your employees worked this week you’ll be able to look up old paychecks with the same number of hours and hand write them a check if you have to.
  • ·         Issue a payroll advance explaining to your employees that this advance will be paid back as an employee payroll deduction from future paychecks. Payroll advances are a life-saver in just about any type of emergency, even if you use Inuit Online Payroll and either you have no internet or their site is down.
  • ·         Contact your CPA or tax professional – if you are in dire straits perhaps they can run payroll for you, if they have a copy of your QuickBooks data file – even if it’s one that is several months old.
  • ·         Make sure there are at least two people who know how to run payroll – yes, payroll is sensitive financial data and you don’t want absolutely everyone to have their fingers in your company payroll. However, having a second person, who has been trained to run payroll is absolutely invaluable. You simply cannot just not issue payroll if the normal payroll clerk is out sick, has to take a leave of absence due to a family emergency, or is hospitalized due to some sort of accident.
  • ·         In the event of an impending storm, calculate what you would need to give each employee as a cash advance, cut a total check, code it to petty cash, and distribute a cash advance to each employee. Make them sign some sort of receipt that they received $X amount of dollars as an advance and that payment would be made via payroll deductions in future paychecks. Go to the bank and actually cash the check and put the money in the company safe in case the storm is severe.
  • ·         If you use an online payroll service and the site is unavailable – use a web based calculator and hand write the checks. Here are some web based paycheck calculators – and

If your emergency involved a power outage, loss of internet, or you use an on-line payroll service and are unable to transmit your payroll data for any number of reasons contact the provider when you are ready to transmit the data to have them refund any backdating fees associated with the transmission. Intuit offers a refund of backdating fees in most instances. I do not know about this specific policy for other on-line payroll providers, so be sure to check with them for specifics.

The IRS will have specific details for late tax deposits/filings as a result of a natural disaster. Go to for more information. The IRS also has natural disaster information to assist customers. Specific state websites will also have information for state tax deposits/filings.

These are just a few suggestions for you to think about. What sort of payroll backup plan do you or your clients have in place in the event of an emergency?

QuickBooks: Memorized Transactions Shortcuts

You can memorize common transactions so that you can easily reproduce them later. Think of this as creating a template that you can make copies from.

Memorize the current transaction. This opens the memorize transaction window
Create a new transaction from a memorized transaction. This opens the memorized transaction list

QuickBooks: Inserting Invoice Numbers in QuickBooks emails

"I would like to include the invoice number in the subject line when I email invoices to my clients but I haven't been able to figure out how to do it.  Can you help me?"

When you use the Send button in QuickBooks 2012 to email a copy of an invoice (or, the Email button in QuickBooks 2013) the content of the email message is set in your Send Forms preferences.  To insert the invoice number in the subject line of an invoice you should follow these steps:  

-> Edit
-> Preferences
-> Send Forms
-> Company Preferences screen

You can then edit the Subject line and insert the tag “<NUM>”. This will make QuickBooks insert the invoice number at this point.

Very handy! This works in QuickBooks 2012 and 2013, it might not be available in older versions (I haven’t looked). You cannot use this in the body of the email message. Note also that special characters in the invoice number are stripped out – in QuickBooks I might have “2013-001″ but in the email it will show as “2013001″.