Friday, July 29, 2011

HEAT: Business Rule Protection

A client asked this: What can we do to protect our existing business rules while developing new rules?

One approach is to use a Development system to create and test new rules and then use the BPAM import/export function to move the new rules to your Production system. The import/export function is also a good method to backup rules.

After creating new rules on the Development system:

* From the Business Rule Editor, click File > Export
* Select one or more rules to export
* Choose a place to store the .hef file > click Export

Note: if you check Process Linked Items at the bottom of the window, it will retain the Call Group and Auto Tasks used for the rule.

After you export the rules from the Development system, you can import them into the Production system as follows:

* On your Production system, open the Business Rule Editor
* Select File > Import > Browse to the exported .hef file
* Click Import

The new rules will be added to your Production system.

HEAT: Quick Customize Error

A client had this problem: When I try to apply a Quick Customize I get an error: 'Could not create recovery file'. Where is this recovery file and how do I fix this error?

This error could be created by the recovery file being directed to a folder where you do not have permissions. To correct this:

* Locate a folder where you can create a file
* Open the registry with regedit

Note: backup the registry first and get help with this if you are uncertain how to proceed with a registry change.

* Open the following registry key:
"HKEY_CURRENT_USER\Software\FrontRange Solutions\HEAT\Admin\Commit"

* If a string value named 'Directory' does not exist, then create it
* Right click Commit > select New > String Value > name Directory
* Enter the folder where you verified you can create a file

This should allow you to create the recovery file in the folder where you have permissions.

HEAT: Trade Ticket Progress

A client asked: Is there an easier way to track ongoing activity with a ticket than paging through Journal entries to review the activities of a ticket?

We currently append information to the Solution Description on the Call Log screen but feel there should be a better method.

An alternative approach would be to create a Memo field on the Call Log screen (or Journal) and call it Work Log. This Log would be updated using an autotask appends work performed and a date/time stamp.

To add a Work Log field, you would do the following:

* Select Administrator > New Edit Set
* Select CallLog > System > Open Table > Add Field
* Enter Field Name and Description (e.g. WorkLog)
* Enter Field Type of Memo > Click OK
* Click Add Field to Form > Place the field where desired
* Add a Command Button > Add Text Label to button

The command button needs to be connected to an autotask that will append information to the Work Log field. To save time, create an autotask named 'Add To Work Log' in Call Logging. The Update Call action and click OK. The details can be added later.

* Click on the command button > Click Control > Connect > Autotask
* Select the 'Add To Work Log' autotask > Click Save
* Close form/table > Close HEAT modules and services > Click Commit

To complete the Work Log, you need to add the details to the Add To Work Log autotask. This function will allow you to append information to the Work Log field as the ticket progresses and use the Solution Description to hold the final solution.

* Open Call Logging > Click Autotasks > Manage Autotasks
* Click 'Add To Work Log' > Click Edit > Click the WorkLog field
* Click Insert > Insert Function > Click @UserID()
* Click Insert > Insert Function > Click @CurrentDate()
* Click Insert > Insert Function > Click @Prompt()
* Enter a Prompt (e.g. Enter Work Details) > Click OK
* Enter a Prompt (e.g. Enter Work Details) > Click OK

You will need to add spaces and a colon to the different fields appended to the Log field to make it readable. This can be done while creating the Insert Functions. For example, when inserting @UserID(), add a colon and two spaces before inserting @CurrentDate(). This will separate the two fields and make it easy to read. Do this for the other fields and leave the option as Append on the WorkLog field.

Test this function using the command button and revise as needed.

HEAT: Yes/No Prompt

A client asked this: We created a new autotask, however, when we run it a Yes/No prompt appears. Can you explain how to stop this from happening?

Yes, most likely you enabled a checkbox that is labeled 'Confirm Auto Task Run'. To stop this prompt, do the following:

* Open Autotask > Manage Autotasks > Highlight the autotask
* Look in the lower corner > uncheck the Confirm Auto Task Run
* Click Close > test the autotask

Now that you know how this works, you can also consider ways that it can be used. For example, if you have an autotask that poses a potentially risk if executed accidentally, you could check this box and spell out the risk in the Description of the autotask.

Tuesday, July 26, 2011

QuickBooks: Customize Reminders

A client asked: I don't want to see all the reminders, only the To Do notes. How do I change this?

To have only your To Do notes display each morning when you open QuickBooks to the following:

* From the Edit menu > Preferences > Select Company Preference
* Click on Reminders > Click on "don't remind me" for all
* Do not click on "To Do notes" (click on "show list")
* My preference tab, click show reminders when opening company file

To enter a new to do:

* From the company menu > To do list > Ctrl + N
* Each morning you may want to print the to do list.
* From the Company menu > To Do list
* From the "To Do menu button", click on print report.
* To print a single to do, highlight the to do
* Click print note.

Be sure to make inactive all completed "To Dos" or your list will become unwieldy.
From the company menu > To do list > Highlight the to do > Ctrl + E to edit.

QuickBooks: Instantly create, and print multiple standard monthly reports

New users often ask about getting their important business reports on a regular basis, and how to automate the task.

There are many reports that can be created based on the desire, and they are already set up in the report center by group. I suggest to people that they create the initial basic reports, such as Profit and Loss and the Balance Sheet and then format them and memorize them for easy use. I suggest they create a memorized report group called “MY Monthly Reports” and then save them there. Then, each subsequent month, they can all be processed in one quick motion by using the following path:

Report Menu -> Process multiple reports -> My Monthly reports.

QuickBooks: Clean Up Company Data

It is not uncommon for users to not have a regular plan for cleaning up the data file. This is an important feature of QuickBooks and one that should be used with care and caution.

Some reasons for using the Clean Up Company Data Utility include reducing the size of the data file, cleaning up unused accounts, items, customers and vendors. When you clean up your data file, QuickBooks deletes transactions that you no longer need, replacing them with new general journal transactions that summarize, by month, the deleted transactions. You can also choose to have QuickBooks clean up data by removing list items that you no longer need.

Which transactions are affected?
The ending date, specified for the period of time before which you want to remove transactions, has no effect on transactions dated after the ending date. For example, if your ending date is 12/31/09, all transactions dated 1/1/10 and later remain unchanged in your company file.
Of the transactions dated on or before the ending date, QuickBooks deletes and summarizes only those that have no effect on transactions dated after the ending date.

QuickBooks summarizes deleted transactions
QuickBooks creates summary general journal transactions for the transactions it deletes from your file. Except for transactions that affect the value of your inventory, you can spot the summary transactions by looking for GENJRNL in the Type field of your registers.

There is usually one GENJRNL transaction for each month in which QuickBooks deleted transactions. The transaction amount is the total of the transactions that QuickBooks deleted for the month. For a given month, the register may also show other transactions that QuickBooks did not delete. These are transactions that could be affected by transactions you have yet to enter.

The Clean Up Company Data wizard
The Clean Up Company Data wizard will lead you through the process required to clean up a data file. Before you begin, you should have available media for backing up your data and know that a large file will take a long time to clean up. Be sure to schedule this process when you can afford the time. Note: You will not be able to update any transactions that fall within the period you have selected to clean up.

1. Go to the File menu, choose Utilities, and then click Clean Up Company Data.
2. Choose a cleanup option. You can choose to remove all transactions as of a specific date or you can remove all transactions.
3. Continue through the wizard screens choosing items to be removed.
4. Click Begin Cleanup when you are sure you want to proceed.
QuickBooks displays a message stating that it will make a backup file before it removes the transactions. The backup file ensures that you will still have a record of the details of any transactions that QuickBooks deletes from your company file.
You can change the name and location of the backup file (QuickBooks suggests the name of your company file with a .qbb extension).
5. Click OK to dismiss the message and open the Back Up Company File window.
6. Click Create Back Up.
Before starting the cleanup process, QuickBooks:
• Creates a backup copy of your company file.
If you need to restore your company file to the state it was in before you cleaned up your data, use the backup copy to do so.
• Creates an archive copy of your company file.
If you need to view data that was removed from your company file during the cleanup process, use the archive copy.
• Verifies the integrity of your company file.
Once your data has been verified, QuickBooks begins the cleanup process.

QuickBooks: Find the Difference Between the Trial Balance and Reconciliation Detail Reports

I have a client with an unreconciled difference between their Trial Balance and the Register balance per the Reconciliation Detail. The Trial Balance shows a credit of $15,561.39 and the Register shows a credit of $15,140.87 for a difference of $420.52.

They wanted to know how to change the Trial Balance balance without it changing the reconciliation balance because any adjustment made so far has made them both more incorrect.
They were concerned and asked me how to make them agree.

The Answer:
A difference between the Trial Balance and Reconciliation Detail reports is usually due to transactions created/edited/deleted in the reconciliation period after you reconciled the account.

The Trial Balance is a real-time report but the Reconciliation Detail report is a snapshot in time, specifically the cleared and uncleared transactions at the time you did the reconciliation. If anything was added, changed or deleted, the Reconciliation Detail report will no longer be accurate.

Compare the reconciliation report to the detail on the Trial Balance report (double-click the amount of the account you want to check) to see what changed.

When the client did this, they confirmed that there were two items that changed – one was a date change (moved a transaction to a prior fiscal year in error) and the other was an accidental voiding of a check.

QuickBooks: Eliminate The Cents From QuickBooks Reports

Question: My boss asked you to reprint the company profit and loss report for the year, but asked that there not be any cents included on the report. He just wants whole numbers. Can you help?

Answer: Absolutely! In fact, tell the boss you'll have the report to them in just a moment!

To accomplish this for the profit and loss report (or any report in QuickBooks for that matter), here is what you need to do:
• Run the desired report
• Click the Modify Report button in the upper left hand corner of the report screen
• Click the Fonts and Numbers tab across the top of the window, and a new screen will appear.
• Just click on the Without Cents option on the right side and then click OK
Finish printing the report and you're all set!

Monday, July 25, 2011

Crystal Reports : Using MID() to cut out a substring

Let us suppose that we are reporting on history, and our field {HISTORY.REF} has some annoying characters padded into the beginning of the string, like this:
"oc: Justin Hill - Appointment"
"oc: Gene Marks - Phone Call"
"oc: Corey Babka - Next Action"

For cosmetic purposes, we will create a formula to strip out the "oc:" with the following code:


This will return the entire string, starting at character position five.

Crystal Reports : Using MID() and INSTR() together

Sooooo, if we were able to write a dynamic MID function, one that could determine where the Lastname started no matter what it was, that would be pretty sweet, right? Because it is in the nature of a human name to have a space between Firstname and Lastname, we can do just that by unleashing our INSTR() "in string" function.

The INSTR() function searches a string for another string. It takes two parameters, like this:

INSTR(string to search, what to search for)

We are going to use it to search for the space in our {USERS.NAME} field.

So, going back to our "Justin Hill" example,

INSTR({USERS.NAME}, " ") = 7 - finds the space between first and lastname

So going back to our original problem, cutting out the Lastname, we can now substitute the starting position in our MID() function with the INSTR() from above like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ")) returns " Hill" (note the leading space).

What you really need to do is increment our starting position by 1, because the Lastname starts one character position after the space. Like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ") + 1) returns "Hill"

Because the MID function is always being passed the location of the space by INSTR it should reliably "kick out" the Lastname.

Then it is a simple matter to make this code into a Crystal Formula, then use that as your sort field in your Sort Expert.

Crystal Reports : Consolidating Buckets into One Formula

Let us suppose further that we are creating a crosstab object to show us where the INVOICES land in date buckets. You would want to specify each ROW of the crosstab as your summary (i.e. COUNT or SUM). You would need, then, to specify your COLUMNS within the crosstab as a single, unified function that will "kick out" the correct "bucket name" based on INVOICES.DATE.

We will call our unified crystal function DATEBUCKET and the code would look like this:


This code consolidates our date buckets into one unified crystal function that can be added as a Group or a Crosstab column. Obvoiusly, the bucket names are "kicked out" as described above. These will ultimately be the names of your groups.

Again, watch your math to guard against double bucketing!

Crystal Reports : The Perils of Double Bucketing

When grouping (or "bucketing") data by a date field, it is important to make sure that your groups are mutually exclusive. Let us suppose that we are breaking down rows from a table called INVOICES based upon INVOICES.DATE. We just want to see which invoices are "out" thirty days, and which are "out" sixty days.

So, if our "30 Days" group is calculated thus:
(give me invoices that are between zero and thirty days old)

Our "60 Days" group must necessarily exclude day 30 and begin on day 31:

The potential danger here is making our second bucket start on day 30, a common mistake. This would mean that any invoice with a date landing on day 30 would belong to both buckets, therefore skewing our results.

This cannot be overstated. Crystal known nothing about your date buckets, just the code that you provide it.

A better solution could be to use the "automagical" Crystal functions AGED0TO30DAYS or AGED31TO60DAYS, both of which are available under the "Date Ranges" function tree in the formula editor.

I actually prefer the "manual" technique as described above, as it allows you to specify exactly what ranges are being used. (i.e. some clients like to use 40/80/120 instead of 30/60/90)

Crystal Reports : The Perils of Double Bucketing Continued

Double bucketing; it is a scourge upon the hapless Crystal Reporter!

Another technique to use when bucketing is to force Crystal to use mutually exclusive "automagical" functions. A good example of this is to use the YEAR() function to group any date by it's corresponding year.

So, if we break out INVOICES.DATE by grouping by YEAR(INVOICES.DATE), we can be assured that any one invoice can only belong to one specific year. Therefore we avoid double bucketing.

But the needs of the client are paramount; some businesses like "strange logic" to be applied to their buckets, depending on usage requirements.

An example: a client needs all invoices cut in December 2009 to belong to year 2010, yet anything previous should belong to year 2009.


2010 Bucket:

This code above includes all invoices with a year of 2010 AND also the ones from December 2009.

So we can see that sometimes the "automagical" crystal date function do not always meet the expectations of the client.

Know your manual date math! And beware the perils of double bucketing!

GoldMine : Display two Contacts Simultaneously

Did you know you can view multiple GoldMine records simultaneously?

By default, GoldMine displays one contact record at a time. If you also want

to open other contact windows simultaneously, follow these steps:

Select Window | New Contact Window. This will bring up the Contact List.

From the contact list, select the second contact you wish to see and
double click on that contact.

This opens the second contact window. You will see a second record tab at
the top of your screen. Then, select Window | Tile Horizontally for a side
by side view or Cascade Windows for a Vista-style window display

This option is particularly useful when you wish to compare two contact
records in GoldMine. It saves time by eliminating the need to alternate back
and forth between the two records.

GoldMine : Using Multi-Level Searching in the Search Center

Did you know that GoldMine can search on more than one field in the Search

Simply bring up your Search Center (my favorite way is to double click right
on the word "Contact" on the Contact Record itself).

To the right of the "Search Box" is a plus sign; click this.

You'll see that a new Search Box has opened up below, connected by an "AND".

You can click on the "AND" drop down list and select "OR" if you prefer.

So now you can search on the values of two fields. Keep clicking the plus
sign to add more Search Boxes.

Just like any other GoldMine search, as you type in values, your list
changes. I know a lot of clients that use this functionality to generate
lists of customers (by Outputting to Excel).

GoldMine : Using To-Do's

A "To-Do" in GoldMine is a special kind of Calendar item. Typically used for
personal or "busy work" reminders, To-Do's are lightweight pending items
that track a Priority.

To create a To-Do, select Schedule | To-Do.

You'll notice that it looks a lot like other Pending Items, but has less
fields. You'll want to give your To-Do a Name, Priority, and some Notes. You
may choose to link the To-Do to the current contact record by checking the
"Link" checkbox at the bottom right-hand corner.

When you open your calendar, you'll see that your To-Do's appear in the Task
Pane (lower left hand side), alongside items that do not have a time

You can alternatively see your To-Do's by going to Go To | Activities and
selecting "To-Do's" from the Open Activities drop down on the left hand

When completing your To-Do's, they become somewhat "normal" History entries,
and will appear everywhere History does.

GoldMine : Moving E-mail Attachments

Did you know that you can redirect an email attachment in GoldMine to a
different location?

The major underlying problem is that, by default, GoldMine stores all e-mail
attachments in a single folder on the server (\GoldMine\Mailbox\Attach).

After a few years of trading emails, this folder can become very large and
difficult to navigate (anyone who has tried to keep a few thousand MP3's in
one folder can attest to this).

When viewing a GoldMine e-mail, you can right-click on any attachment listed
in the email header, then select "Move". Then simply browse out to the
special location you'd like to store the attachment.

Note that the attachment "link" is now updated. Anyone opening this email in
GoldMine will see the attachment in the new location.

ProTip : Deleting an email in GoldMine does not delete the attachment files.

GoldMine : Using a Decimal in the History Duration Field

I've had a few calls over the last couple weeks with folks who'd like to use
their histories in GoldMine to generate billing for clients. I actually do
this myself every week. Where the challenge lies is in the fact that the
"Duration" field on a History item reads like this: "00:30:00" (30 minutes).
This makes any totaling of these durations (say, on a report or within a
query) difficult to say the least.

I find it much easier to enter in "Decimal" times for my histories. So,
instead of using "00:30:00" for a half hour, I use ".5" Note that the
Duration field is a simple text field, and will accept almost any input you
give it. So my 01:45:00 becomes 1.75 and so on.

This way, any totaling you want to do becomes easy. It is possible to
"parse" out the HH:MM:SS duration value, but you'll spend more time doing
that than working on the report itself...
This also brings up a good concept; whenever possible, structure your data
entry model to make reporting easier.

Sunday, July 24, 2011

MSCRM 2011: Adding a Sub-Grid to a Form

Microsoft Dynamics CRM 2011 contains a special feature that allows you to add sub-grids to Forms. For example, when working with a specific account record, you can add the related Opportunities grid to the main form rather than accessing the related opportunity information from the left navigation bar. In this example, we will add the Opportunities grid as a sub-grid to the Accounts Entity form as follows:

* Go to Settings -> Customizations -> Customize the System
* Under Entities, Select the “Account” Entity Form.
* Go to the Main Information Form.
* Go to the Insert tab on the Form and then Click on Sub-Grid
* The Sub Grid Properties window will be opened.
* In the Sub-Grid Property window provide the name , Entity, Records and Default View type under Data source Section. If you want you can provide the additional options like Display search, Display Index and Display View Selection.
* Save the changes and then “Publish all the Customizations”

* You can now see the Opportunities sub-grid in the Account Entity. To see this sub-grid-go to the Account Entity in the Workplace and then open one record. The Opportunities Sub-Grid will be displayed.

MSCRM 4.0 and 2011: Finding Unresolved Emails

Are you looking for a quick way to locate all unresolved emails in MSCRM and update them? See the Advanced Find below.

There a number of reasons why you might encounter unresolved emails in MSCRM. One reason is when you are using the Microsoft CRM Outlook Client "Track in CRM" feature. This is due to Outlook containing email addresses that are not related to any existing CRM Users, Accounts, Contacts or Leads at the time of being "tracked" in MSCRM.

Use the Advanced Find listed below to find all the Microsoft CRM e-mail records with unresolved email ,addresses and update them.

* Click New > select Look for: E-mail Messages
* Click Select > Status Reason > Select Equals > Select Received;Sent,Pending Send, Draft
* Click Select > Activity Parties (Activity)

* Click Select > Participation Type > Equals > ToRecipient, Sender, BCC Recipient and CCRecipient

* Click Select > Party > Does Not Contain Data

Save the Advanced Find and then click Query. It should list any unresolved emails.

MSCRM 4.0: Showing Only Related Contacts in Primary Contact Lookup in Account Form

A client asked: We have many accounts with multiple contacts. When we want to change the primary contact, the lookup shows all contacts, not just those related to the account. How can we change this?

To display only contacts with the current account as their parent account, you need to make some customizations.

There are two parts to making this work:
Customize your Contact entity
* Select > Lookup view > click on 'Add Find Columns'
* Check 'Parent Customer' > Save changes > Publish.

Following this change, when you do a lookup for primary contact, you can search by parent company name as well as the person's name. This is useful on its own.

To complete the customization and do this lookup automatically:
* Customize the Account entity main form > edit the form properties
* Add an onLoad event with the following line of code:

crmForm.all.primarycontactid.additionalparams = 'search=' +;

This adds a parameter to the primarycontactid field to pass to the search box when the lookup is invoked (the parameter is the field "name" which is the Account Name of the current Account).

* Save and close > Publish the Account entity.

Now when you use the Primary Contact lookup from within the Account, you will only see contacts that are identified as working for that company (by their Parent Account).

Note: In Microsoft Dynamics CRM 2011, this functionality is automatically built-in to the lookup dialog.

Wednesday, July 6, 2011

Is The Economy Worse Than We Think?

News and commentary from around the small business community in my weekly New York Times blog.

What My Kids' Summer Jobs Taught Me About the Job Market

Why my kids have summer jobs in this tough market and what it taught me.  My new Huffington Post blog.