Wednesday, September 30, 2009

Crystal Reports : Easily copying formatting between fields

In order to shorten the time it takes to make a report look nice, we can use the format painter to first, "copy" the formatting from one field, then "paste" it onto another. Formatting includes everything under the formatting options menu, including things like decimal places, currency symbol, etc.

1. Click on the object you want to "copy" the formatting of.
2. Go to Format | Format Painter on the top level menu
3. Click on the object you want to apply the formatting to

Note: Some versions of Crystal will not have this menu option, but will instead provide a format painter toolbar icon, in the form of a little paintbrush.

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 : Displaying Parameters with Multiple Values

When using a parameter that accepts multiple values, you will notice that you can't just drag it onto the report. We must pass it to the "Join" Crystal Function to "split" the values out.

Our parameter field is called {?UserID}, and accepts multiple values.

Create a formula with the following code:

Join({?UserID}, ", ")

This will return all UserID values within the {?UserID} parameter, separated by commas.

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)

HEAT: Import Excel to HEAT tables

A client wants to know: We have alot of data for our new HEAT system in Excel worksheets used to export from our previous help desk software. How do we get it into HEAT?

As an example, let's use Call Type as an example. There are a number of ways to move data from Excel to SQL tables; 1) HEAT's import/export function; 2) SQL DTS function and 3) an Access database. We will cover the Access database method:

First, as an backup of the Call Type table, open Administrator
Select Database > Export > Select Call Type > Browse to a folder
Save as Call_Type_ Export > the Call Type table is now saved *********************************************************
Open Access > Create a blank database
Select Tables > File > Get External Data > Import
Browse to your Excel worksheet > Select the Call Type worksheet
Click OK > Your Excel worksheet will become a table in Access
Select Tables > File > Get External Data > Link Tables
Change the File Type to ODBC Databases() > Select HEAT DSN
Select the Call Type table > this becomes a linked table in HEAT

Click Query in Access > Select New Query > Design View
Add the CallType worksheet > close table window
Select Query > Change to Append query > Apppend to dbo.CallType
Match the Excel Fields to the Call Type fields below
Close and save the query >Select the query
Click Open > it will tell you that is going to append x records
Close the Access database after the append is complete


Open Call Logging and select Category. The contents from the Excel worksheet should now be in the Call Type drop down.

HEAT: Working from the Assignment tab

A client recently asked: Can we setup HEAT so that technicians can work from the Assignment tab to perform all the tasks they need to do in HEAT?

Yes, this involves adding some command buttons and autotasks but it not very complicated. To add a command button:

Open Administrator > Select Quick Customize
Select the Asgnmnt table > Click Open Form
If the Toolbox does not appear, click View > Controls Toolbox
Select the Command Button > Drag to under the Resolve button
Right-click the button > Change text to Save and Close
Open Call Logging > Create an Autotask called Assignment Button
This autotask should include the following actions:
-Update Call > Call Log:Close Desc > Insert Field > Assignment:Notes
-Update Call > Call Log:Cause > Insert Function > Validate Prompt()
-Check the Save Call and Close Call checkboxes
Save the Autotask
In Administrator, select the Command Button > click Control
Click Connect > Click Autotask > Browse to Assignment Button
Apply the Quick changes
Open Call Logging > it should create a new Catalog
Open the Assignment tab > Create an Assignment
Acknowledge and Resolve the Assignment
Enter the Assignment details in Notes
Click the Save and Close button
Enter Cause the from Prompt > Notes will be copied to Close Desc
The autotask will save and close the call

That's it - you can then add IF statements to check if the Assignment has been Acknowledged and Resolved before the Save and Close function. You can also test that the Notes field is not empty before it is copied to Close Desc.

Sunday, September 27, 2009

QuickBooks: Using Transaction Classes

A client asked: What are Classes and how are they used in QuickBooks?

You can use classes in QuickBooks to segment your financial statements and to filter many of your QuickBooks summary reports and detail reports for specific segments of your business. However, certain entries may not apply to any of the classes on the list or you may need to allocate the entry across multiple classes.

For example, if you use QuickBooks to track locations, entries for company-wide expenses like insurance policies and advertising do not apply to a specific location. You have two options when working with this type of entry:

1. You can create a class called “Overhead” and you can post the entry to that class.

2. You can split the detail of the transaction so that the single transaction (e.g. check, bill or invoice) applies to multiple classes.

Whatever method you use, the bottom line is to always use a class on each and every transaction. If you do not include a class, you will:

* Create an Unclassified column on the Profit & Loss by Class report. Doing this does not allow you to track transactions that should have posted to a specific class but where the user failed to enter the class.

If you use a Class called “Overhead,” you can use the “Unclassified” column to track transactions that are missing a class.

* Receive a warning message that the transaction does not include a class. (This message will show only if the “Prompt to Assign Class” box is check in the Accounting Company Preferences.)

If you exclude the class on selected posts only (e.g. posts that are overhead or posts to Balance Sheet accounts), the benefit of this warning is diminished. Over time, users will ignore the message.

For this reason it is best to use the Overhead Class on posts to Balance Sheet accounts, even though QuickBooks does not allow you to filter the Balance Sheet by Class.

QuickBooks: The Ask Advisor Equity Account

A client asked: Since every transaction requires assignment to an account, what is the best practice for handling tricky transactions that I am not sure about?

The best approach is to create an “Ask Advisor” Equity Account. Depending on your level of accounting and QuickBooks experience, you may often need to record transactions that you are unsure of which account you should use. In fact, to record the transaction you might even need to create a new account.

Since you cannot record the transaction without assigning an account, you need to choose something in order to continue with the data entry. However, if you guess and post to an incorrect account, the transaction becomes buried in the General Ledger and will appear on account-based transaction detail reports.

Instead, create a new account with an Equity type called “Ask Advisor” You can choose whatever type of account you like, but it is best to select an account with a Balance Sheet type (like Equity) so you can use the account register to easily edit the transactions in the future to assign the correct account.

Arrange regular meetings with your QuickBooks Advisor to help you to clear the balance in this account. The transactions you post to “Ask Advisor” could have a significant impact on your financial reports, affecting the way QuickBooks measures your financial performance and how you manage your company, so don't wait under the end of the year to meet with your QuickBooks Advisor.

QuickBooks: QuickBooks on Terminal Services

A client asked: Does QuickBooks run on Terminal Services?

Yes, it does and it runs very well. It provides a faster and more stable connection to QuickBooks for each user.

First, there is a difference in setup and licensing. Terminal Services allows each workstation to share the server’s desktop. When you use Terminal Services, you still need a QuickBooks license for each unique QuickBooks user, but you do not install QuickBooks on each workstation. Instead, you install one license of QuickBooks on the server and you store the data file on the server.

Then, all QuickBooks users on the network run the QuickBooks application stored on the server, up to 30 users running a single installation of QuickBooks for QuickBooks Enterprise. QuickBooks will perform significantly better and each user's connection to the QuickBooks data file is much more stable.

In a Terminal Services environment the processing power is focused on the Terminal Server instead of the workstations, so the faster the server the better. You also may want to consider a separate server just for the QuickBooks program.

QuickBooks: Networks and Databases

A client asked: Can my network configuration cause problems with the QuickBooks database?

The condition of your Local Area Network (LAN) can have a significant impact on the possibility of QuickBooks data file corruption. Consider the following:

1) Wireless Networks
Wireless networks are the worst enemy of the QuickBooks data file. The QuickBooks file is sensitive to unexpected disconnections and wireless networks have a much greater propensity to have connection interruptions than connections via Ethernet cable.

Strongly consider not using QuickBooks with wireless network connections. If the network has a wireless router at all, consider that some computers may have both Ethernet and wireless network adapters. To ensure the computer uses the Ethernet connection and not the wireless connection, you should either turn off the wireless signal or take steps to prevent the computers with wireless network adapters from connecting to the wireless router.

2) General Network Maintenance
Substandard, obsolete or poorly configured routers can impact both the performance of QuickBooks in a multiuser environment and can increase the risk of file corruption.

The concept here is the similar to wireless connections. If the configuration of the server or the condition of the hardware causes periodic interruptions in a user's connection, this interruption can cause a QuickBooks file corruption.

Examples include connection issues with the network router, hardware or driver problems on workstation network adapters, operating system/resource issues on the server that cause QuickBooks or Windows to enter a non-responding mode.

QuickBooks: Reduce Data Corruption

A client asked: What should I be doing to prevent data corruption in my QuickBooks Enterprise database?

There are a number of things that you can do yourself or in conjunction your QuickBooks Advisor:

To reduce the potential for data file corruption, take precautions to make sure the database is as stable as possible. You can do the following to reduce the likelihood of data file corruptions or other types of data integrity issues.

1) Interpreting the Product Information Window
The QuickBooks Product Information window shows valuable information about the QuickBooks program When analyzing your QuickBooks files this information is extremely important. You need to ensure your clients installed the most recent QuickBooks update, that the size of the QuickBooks file is reasonable and that the client’s lists are not nearing the 28,000 limitation for Items and the 14,000 limitation for customers, vendors, employees and other names).

2) Track Historical Database Size
A way to track your historical data use is to open a backup copy of your data file from about one year ago and note the number of transactions in the file and the size of the file. You can then note the same information using the current file. Using this information, compute the number of transactions entered over the past 12 months to project the file growth over the coming year or even over multiple years. When factoring file growth, consider not only the historical growth of the file, but the projected growth of the company and any changes the company intends to make in the use of QuickBooks. To access the Product Information window, open the QuickBooks data file and then press F2.

3) Monitor File Size
The size of the file is a major factor in corruption prevention. Though larger files do not necessarily become corrupted, the larger the file the greater the risk. The increase in risk escalates quickly as the file size reaches about 500MB for QuickBooks Pro and Premier and about 1GB for QuickBooks Enterprise Solutions. You should monitor the size of QuickBooks files and keep the sizes at or below these levels if at all possible.

There are numerous ways you can reduce the size client's data files. As one quick (and strongly recommended) option you can use the Portable Company File process to reduce file size.

4) Monitor Database Fragments
The Product Information window also shows you the number of database fragments for any given file. If possible you should have no more than 9 database fragments in the file. If there are 10 or more database fragments you can perform the following steps to reduce the fragments:

* Resort the lists in the file. Use the "Resort List" selection in the list menu to resort the lists. Resorting the lists using the list headers does not have the same reorganization impact on the file. Note: Some lists do not have a "Resort List" selection.

* Rebuild the File. Select the File menu, select Utilities and then select "Rebuild Data." You can also use the Rebuild Data utility to repair file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.

* Create a Portable Company File and then restore the Portable Company File. This process re-indexes the database and is one of the most effective ways to reduce database fragments. You can also use the Portable Company File utility to repair some data file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.

* Run the Windows Defragmentation utility on the drive on which the QuickBooks data file is stored (e.g. the server). The fragmentation level of the computer's hard drive can impact the fragmentation level of the QuickBooks database.

Note: According to Intuit's data repair department, running the Rebuild Data utility too frequently can actually increase your risk of file corruption. You should limit the prevention intended rebuilds to a handful per year.

Saturday, September 26, 2009

GoldMine: Record Activity Time

A client asked: Is there a way to automatically record the time spent on an activity and save it in the history of the contact?

Yes, there is a timer that can be configured in GoldMine preferences to time your activities. Here is how you set it up:

* Select Edit > Preferences from the main menu
* In the 'Preferences' dialog box > click the 'Schedule' tab.
* Check the “Start timer when completing activities” option.
* Click OK.

Now, when you view the completed activity in the history tab, the duration will be recorded.

GoldMine: Quick Scheduling

A client recently asked: It seems like a lot of steps to create an activity in Goldmine. Is there a quicker way?

There are several shortcuts to create an activity in Goldmine. My favorite method is below:

* Look up the contact that you would like to schedule
* Highlight the contact > Press F7 to open the calendar
* Pick a date > Click the Schedule drop-down > pick Activity type
* The Schedule Activity window opens with the contact filled in

Enter the remaining information about the activity and click Save. You're done.

GoldMine: SMS Text Message

A client asked: Can I use text messaging in Goldmine 8.5? If so, do replies go to my Blackberry?

Yes, SMS text messaging is possible in Goldmine. To send a text message, you would do the following:

* Select Contact > Send SMS/Text Message
* Enter the number to text > enter the provider
* You can type your message in the large text box
* Click Send

Any reply to the text message would be to the email address setup in your Goldmine email preferences.

Friday, September 25, 2009

GoldMine: Controlling the Process Monitor

A client asked: Is there a way to control the Process Monitor window? It seems to just pop up anywhere when you send an email or run an automated process.

Yes, there are number of ways to control the Process Monitor window. Try the methods below:

* An easy way is to drag the Process Monitor window to the bottom of the screen. It should dock to the bottom of the form. Then find and click the push-pin icon. This will cause the process monitor window to auto-hide and only appear when you mouse over the 'GoldMine Process Monitor' tab in the lower left corner

* Another way to manage the Process Monitor is change the settings in your user.ini file. Find the section below and change MdiState to 1.


The window then behaves as you tell it and does not interfer with user activity. It will stay in the shape, size and position that you set it to.

GoldMine: Beyond the Documentation

A client asked: I have never seen a list of undocumented tips for GoldMine. Can you provide a list of some tips and tricks not included in the documentation?

Sure, there are a lot of tips that are discovered by users and then passed on by word of mouth. A few are listed below:

* When scheduling an activity for a contact, you can automatically schedule three weeks out by typing 3W in the date field (or 4W, 5W)

* Schedule by priority (instead of Time) Establish a series of codes for your priorities (A, B, C), then place them in the Time field instead of the time.

This will change the field to read "Priority" and all calls will be placed on the View Activity lists sorted by priorities. These calls will show up on the daily calendar under To-Do's.

* In the Contact Search Center, click the Columns button to add fields to your view. Choose the Column Selection tab and double click the field you want added

* Right click in any list view and select the 'Summary' option to get a count of the currently displayed items (the count is displayed in a textbox on the bottom-left).

These are just some of the tips and tricks that exist for GoldMine. If you know of others, free feel to send them on for inclusion in a newsletter.

GoldMine: Accept, Decline or Propose a New Time

A client wants to know: When you use GoldMine to send a meeting request email to a contacts can you request that the contact Accept, Decline or Propose a New Time for the meeting?

Yes, you need to do the following:

* Configure SMTP settings in GoldMine to send emails.
* Select Edit > Preferences > Internet to do it.
* When scheduling an activity, check "Send a request with the Appt."
* GoldMine sends an iCal meeting request to the contact.
* The contact can choose any of Accept, Decline, Propose a New Time.
* Their email program will automatically send a response back.

This allows for meeting confirmations using emails with the option to Accept, Decline or Propose a New Time.

GoldMine: Team Meetings

A client recently asked: I use GoldMine to schedule meetings with customers. Can I also use it to schedule Team meetings?

Yes, you can easily use GoldMine to send meeting requests to your team members (i.e. GoldMine users). Here's how you do it:

* Select Schedule > Events from the GoldMine Menu.
* The Schedule An Event window appears.
* In the Detail tab, fill in details about the meeting
* Select time and duration of the meeting.
* In the same tab, Check "Send a request with the event to" option
* From the drop-down below it select Users.
* From the Users tab, select the Users to send the request.
* If the meeting is a regular activity, click on the Recurring tab
* Select the date range, frequency and occurrence of the activity.
* Hit Schedule to complete.

Now, GoldMine will automatically send the meeting request to your team.

GoldMine: Maximize Your Sales Calls

A client asked: When I visit a city on a sales call, I would like to visit any other contact that resides in the city I am visiting. Is there a report for this?

This can be easily done using a filter. Use the GoldMine Contact Search Center to find this information:

* Click on the Search button.
* In the drop down box next to ‘Search by’, choose State.
* In the next box choose ‘Equal To’
* In the last box choose the State you are going to.
* Click on the plus sign at the end of the row
* Another search line will appear below
* Choose ‘And’ in the first drop down box.
* In the Search box, choose City.
* In the next box again choose ‘Equal To’
* In the last box type in the city you are visiting.

You will now see contacts in your GoldMine database that are from that particular city and state. You can then send the results to a printer or to Excel.

GoldMine: Delete Attachments with Email

A client asked: When I delete an email in GoldMine, the attachments are not removed. Is there a way to delete both emails and attachments?

Yes, since email attachments are not be removed by default, you need to do the following to also delete the attachments with the email:

* Select Tools > Options from the menu.
* Select the E-mail tab.
* Click on 'More Options' to open the 'E-mail Preferences' dialog box.
* Select the 'Advanced' tab.
* In 'Message', check 'Delete attachments when deleting the mail'.

Now whenever you delete an email, the email attachments will also be deleted.

GoldMine: Pictures of Contacts

A client asked: I would like to include a picture of each contact in a Goldmine field. Is there an easy way to do this?

This could be done using a GM+View. The GM+View tab allows you to embed rich HTML data into your customer records, including photos, graphic images or sound files.

Below is how you can display images relevant to your contact:

* Select Web > Configure GM+View.
* The GM+View Tab Settings dialog box appears.
* Click on the New button and enter a template name.
* To insert the picture, click the Insert Image icon
* Browse to the image > add any text or borders you want
* Save the template

You can make this the default GM+View if you want it to appear whenever you select the contact. You can use this image for visual recognition of contacts or to print name badges.