Monday, November 30, 2009

GoldMine: Customize Search Columns

A client asked: When I click Search, I don't see all the columns I need in the Contact Search Center. How do I change them?

You have the ability to customize the columns you see in the Contact Search Center. By default, you will see the Company, Contact, City and Phone1 fields. In the Contact Search Center toolbar, you’ll see the Columns button.

* Click it, and a window will pop up
* To add a column, find the column on the left (in alphabetical order)
* Double-click or single click on it and use the right-arrow button.
* It will then move to the right-side of the screen.
* The order of the columns can be changed by the up or down buttons.

Click “OK” and your Contact Search Center will appear with your customized fields. Any field from the top half of the screen, the summary tab, or the fields tab can be added. Also, the column widths can be adjusted in the Contract Search Center by dragging the dividers between the column headers.

Note: In GoldMine Premium Edition 8.5 and above the Email and Web Site fields can also be added.

When you change the columns you will see their widths are a ‘sticky’ setting. So once it’s set it does not change when you exit GoldMine Also, the change only affects you, no one else.

GoldMine: Count By Field

A client asked: I maintain our GoldMine system. Is there a "general purpose" query I can use to keep track of whatever field I want (e.g. states, cities, account manager, status, etc.)

Sure, try the query below and just substitute the field you want for "FIELDNAME"
select FIELDNAME, count(*) as total from contact1 group by FIELDNAME order by total desc

For example, to use to count the number of states in your database:

* Open Tools > SQL Query > Enter the following:

select state, count(*) as total from contact1 group by state order by total desc

* Click Save > Save as New Query > Provide a Name
* Click Query > Review results

The results can be exported by right clicking and selecting Output To.

Crystal Reports : Displaying two summaries in a Crosstab

I often find is helpful to display both a SUM and a COUNT within the rows of a Crosstab object. A recent client needed to see both the SUM of {INVOICES.AMOUNT} and the COUNT of invoices based on {INVOICES.DATE}.

After adding your Crosstab as normal, go into the "Crosstab" tab under "Format Crosstab"

1. Add a row to the crosstab for a SUM of {INVOICES.AMOUNT}
2. Add a row to the crosstab for a COUNT of {INVOICES.INVOICENUM}
a. What we want to count here is the uniqueID field in the INVOICES table
3. Hit OK
4. In order to make the values stand apart in the Crosstab cells, I like to change the text alignment (left and right) of each summarized field.

Crystal Reports : Displaying minutes and hours and minutes

Let us suppose we would like to summarize the amount of minutes in a phone call report, but express the grand total in hours and minutes. This is more easily accomplished using the "Display String" of the field in question. This allows us to create the summary field as normal.

1. Create a summary field to SUM your {CALLS.MINUTES} field
2. Right click on the summary field itself
3. Click on the "X-2"button to the right of "Display String"
4. Paste in the following formula :

local numbervar Hr := Truncate(CurrentFieldValue/60);

local numbervar Mn := Remainder(CurrentFieldValue,60);

Totext(Hr,0) + "Hours " + ToText(Mn,0) + "Minutes"

Pro Tip : The CurrentFieldValue function returns the current field value of the field being formatted. This allows easy code reuse.

Crystal Reports : Display checkboxes on a report

Let us assume we want to display a checkbox on a report. In our example, we are looking to visually identify if a Crystal Formula is returning TRUE or FALSE.

The way this works is by selecting the "wing-dings" font for a formula and then programmatically changing what character is displayed.

1. Right-click on your formula and select "Format Field"
2. Click on the Font tab
3. Select "Wing-Dings" from the Font dropdown list
4. Click on the Common tab
5. Click on the "X-2" button to the right of "Display String"
6. Paste in the following formula, substituting your {@FUNCTION}


This will automatically produce an unchecked box or a checked box, depending on the value of {@FORMULA}

Crystal Reports : Group Tree Missing

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.

Sunday, November 29, 2009

GoldMine: Incomplete Contacts

A client asked: We add everyone as a contact whenever they contact our company. Is there a way to find contacts where the preliminary information was added and then the contact abandoned?

Yes, they are a number of ways. The SQL Query below will display contacts without a Phone1 entry or Email entry. These are most likely prospects that did not work out. To locate these contacts, do the following:

* Open Tools > SQL Query > Copy in the following:


* Click Save > Save as New Query > Provide a Name
* Click Query > Review results

The results can be exported by right clicking and selecting Output To. You can also click on the records returned and go directly to a contact for follow up or deletion.

GoldMine: Calculating Age

A client wanted to know: Can you add a field with the contact's age if you know their date of birth?

Yes, you can add a user-defined field that holds the person's age based on today's date and their date of birth. You would need to do the following:

* Right click > Select Screen Design
* Create a new field: ubirthdate (date field)
* Add values for the ubirthdate field

* Open the Properties for the Age field
* Click on the Profile tab > click More Options
* Under Field Data > Click Expression
* Copy in the following expression:


This will show the contact's age in the Age field and takes into consideration leap years.

GoldMine: First, Middle, Last

A client asked: Is there a way to take the contact name and break it into First, Middle and Last Name?

Yes, this can be done using a SQL query. Enter the expression below:

* Open Tools > SQL Query > Copy in the following:

SELECT contact AS [Full Name], SUBSTRING(Contact, 1, NULLIF(CHARINDEX(' ', Contact) - 1, -1)) AS [First Name], RIGHT(contact, CHARINDEX(' ', REVERSE(contact))) AS [Last Name], SUBSTRING(contact, CHARINDEX(' ', contact), LEN(contact) - (CHARINDEX(' ', contact)-1) - (CHARINDEX(' ', REVERSE(contact))-1) ) AS [Middle Name] FROM contact1 WHERE contact >''

* Click Save > Save as New Query > Provide a Name
* Click Query > Review results

The results should appear as the following columns: FullName, FirstName, LastName, MiddleName. Right click to export the SQL Query results to Word or Excel.

GoldMine: Change Default Match Field

A client recently asked: When I open up the Contact Search window it defaults to "Begins With." Is there a way to make it say "Contains" instead?

Yes, you can change the setup so that "Contains" appears by default:

* Select Tools > Options > Lookup tab
* Modify Default lookup by field > Select "Remember Lookup By"
* Open the Contact Search Ceenter > Select "Contains"
* Run a Search using Contains > Logout of Goldmine

When you log bck in, click the Search button. "Contains" should appear as the default match type field. Note: you can also use "Remember Lookup By" to change the Search By field from Contact or Company to Email, Phone or any other field that you normally use.

HEAT: Autoclose Tickets

A client recently asked: We would like to use BPAM to autoclose tickets that have been completed but not closed after 24 hours.

You can do the following steps to find and close tickets that have been left open:

1. Create a group that meets the criteria to be closed:
* Click Groups > Open Call Group > Add
* Name the Group > Click Global > Change Table to Asgnmnt
* Copy in the following expression:

{Asgnmnt.TotalAsgnmntTime} >= 1 AND ({CallLog.CallStatus} <> 'Closed' AND {CallLog.Cause} is not empty AND {CallLog.CloseDesc} is not empty)

* Save the Call Group

2. Create an AutoTask to be called by BPAM
* Click AutoTask > Manage AutoTasks
* Click Add > Name the AutoTask > Click Add
* Click Update Call > Select Call Log > CallStatus
* Validate CallStatus > Selected Closed
* Save AutoTask

(Note: you could also check the Close Call checkbox instead of changing Call Status to Closed)

3. Create a rule that runs the AutoTask
* In the BRE, click "If a ticket isn't closed in x amount of time"
* Select 24 for Hours > Select Call Group created in Item 1
* Click Add AutoTask > Select AutoTask in Item 2
* Click "No Followup" > Click Finish

After you save the Business Rule and restart the Business Rule service, this rule should close tickets ready to be closed after they are left open for 24 hours.

Saturday, November 28, 2009

HEAT: BPAM Problems

A client asked: We have periodic problems with BPAM stopping or the BPAM rule being out of sync with what was originally set up. Can this be corrected?

One approach to try is to use an index flag to get the BPAM service back in sync with the BPAM files. The -index flag can be used when starting the BRMService to reindex the BPAM files and get BPAM working correctly.

To use this flag:

* Stop the HEAT Business Process Monitor (BRMService).
* Open a command window.
* Use CD to go to C:\Program Files\HEAT directory
* Run the BRMService.exe -index command.

When the command completes, start the HEAT Business Process Monitor as you normally would. Check the BRMFiles folder after running this command you should see that all file timestamps and sizes are updated. This index can be executec periodically to keep the BPAM service and files in sync.

Friday, November 27, 2009

HEAT: HeatBoard AutoTasks

A client asked: Is there a way to automatically run an autotask when a HeatBoard item is closed?

Yes, this can be done as follows:

* Select Administrator > select Defaults > HeatBoard Setup
* Open HeatBoard Setup > locate checkbox > check Run an AutoTask
* Select the AutoTask to execute

Now that you have the ability to automatically run an AutoTask, you need to select or create one to execute. You can notify your staff when a HeatBoard is closed or update all linked tickets.

To notify your staff, create an AutoTask that does the following:

* Select AutoTask > Manage AutoTasks > Click Add
* Name the AutoTask > Click Add > Select Action
* Select Send a Message > Select your Team or Distribution List
* Enter the details of the message

This AutoTask will now be triggered when a HeatBoard issue is closed and send a message to alert your team.

Monday, November 9, 2009

HEAT: Help Desk Only Group

A client asked: We want to create a Call Group that will shows tickets that have assignments to the "Help Desk" and not to any other Assignment Group. If there is an assignment to another group, we do not want that ticket to appear in the Call Group. This group would be used for sending surveys on the Help Desk.

Try the two expressions below in your Call Group. They should return a "Help Desk Only" Call Group:

Option 1:
{Asgnmnt.GroupName} = 'Help Desk' AND {Asgnmnt.CallID} not in (select callid from asgnmnt where groupname <> 'Help Desk')

Option 2:
({CallLog.CallStatus} = "Open" and ({Asgnmnt.GroupName} = "Helpdesk" and {CallLog.CallSource} <> "HeatWEB" and {Asgnmnt.Resolution} <> "Completed") or ({CallLog.CallSource} = "HeatWEB" and {CallLog.CallStatus} = "Open" and ({Asgnmnt.GroupName} = "Helpdesk" and IsNull ({Asgnmnt.Resolution}))

Option 2 covers a number of alternatives and may provide better results depending on your Survey requirements.

Sunday, November 8, 2009

HEAT: Removing Select Calls From Groups

A client asked: How can I temporarily remove calls from a Call Group without changing the Boolean logic?

Of all the different things that can be done inside of Call Logging, one of the more popular actions is the ability to remove a Call Record from a Call Group without actually excluding it from the Call Group Boolean expression.

Why do this? A number of reasons. Maybe you want to run an AutoTask against an entire Call Group, with the exception of a few calls in that Group. Maybe you want to do a clean up by deleting all calls in a Call Group, with that same exception.

To temporarily remove a Call from a Call Group:

* Run the Call Group and open the Call Record to exclude
* Click on File > Remove Call from Group.
* This will temporarily remove the call from the open Group

The call will re-appear within the Call Group, once you refresh the Call Group, or restart Call Logging.

QuickBooks: Using IIF Files

A client asked: I would like to import various Lists into QuickBooks from another installation. Is the IIF file the best way to do this?

The IIF file format is a Generic data format used by Intuit QuickBooks for exporting financial data; saved in a text-based tab-delimited format that can be imported by QuickBooks and most spreadsheet programs. Follow the steps below

* Open the file in a spreadsheet program.
* Insert a column to the left so that the first column is blank.
* Insert a row at the top so that the first row is blank.
* Type the List Type in the first column and the first row.
* The types are shown in the next section.
* Fill in the remaining cells of the first column
* Add the column headings to the first row.

Note: You can get information about the column headings from the Reference guide to import files available in the QuickBooks Help file.

* Click the appropriate list type in the Help window.
* Verify that the column headings match the column information.
* The spreadsheet columns might require modification.
* Save your spreadsheet as a tab- or comma-delimited file.

* In QuickBooks, open the company to import the lists into.
* From the File menu, choose Utilities, click Import
* Then choose IIF Files from the submenu.
* Select the appropriate file to import in the Import window.

Click Open to run the import.

QuickBooks: Update Memorized Reports

A client asked: I use the filtering and formatting features of QuickBooks reports a lot. However, recently some of my reports are not producing accurate results. Any ideas?

Yes, the report features in QuickBooks are easier to use than most software packages and have flexibility, filtering, and formatting features that often exceed the functionality of more expensive accounting packages.

Normally, when you find a report that works, you memorize the report for quick access. You need to be careful, however, if you have memorized a report that has been filtered for more than one account, or name (e.g. job name), or item, or customer, job, vendor types

Your current problem could be that you need to use the feature you selected and add to the filter any new accounts or names or items that have been set-up since the report was memorized.

For example: A job detail transaction report has been memorized for a construction job labeled "Smith Project". Since the job started, the business added a new account to chart of accounts labeled "Permits".

The memorized job report may need to be modified to include this new account if the bookkeeper used "Selected Accounts" to create the report.

QuickBooks: Employee Loans

A client recently asked: How do I setup an employee loan in QuickBooks and, also, a payroll deduction to pay it back?

To do this you need to setup payroll items and account items:

* Set up a payroll item, type=deduction, called employee loans.
* Set up an other current asset account called "employee loans".
* Use "write checks" to loan the money > code to "employee loans".

Edit employee information > add the payroll item "employee loans" to the "additions, deductions and company contributions" box. Enter the per pay period amount to withhold from each paycheck and the total amount of the loan.

Note: The loan deduction limit is a calendar year limit. If the loan is not repaid by December 31st, you will need to make adjustments. On January 1st, QuickBooks forgets the withdrawals made against the loan in prior years and begins all over again.

Therefore, in December, for each employee that has a loan balance -before the first paycheck of the new year- adjust the amount to repay for the new year. If the loan is repaid in full, delete the "employee loan item" from the employee information window.

QuickBooks: Inventory Valuation Difference

A client asked: Why does the inventory valuation report differ from inventory value in the Balance Sheet?

Items can be entered using any of the following transaction types:

* To increase: bill, credit card, check, inventory adjustment.
* To reduce: invoice, sales receipt, statement charges.
* To decrease: adjustment, bill credits, credit refund, credit memo.
* To record a purchase from a vendor for inventory, the quantity and inventory item is entered on the "item tab" of the transaction.

Any of these transaction types, that have used the "item tab" and also have an "inventory item" will appear on the inventory valuation report

The inventory valuation report will not include inactive items, so be sure to print all period end inventory valuation reports before inactivating them. On the other hand, the balance sheet account inventory can include a much broader range of transactions.

Transactions that used the expense tab not the item tab with a posting account of inventory will appear in the balance sheet reports on inventory. But as mentioned, will be omitted from the inventory valuation report due to no item tab used.

To discover the differences between the two reports, produce the following report:

* From the reports menu > Select Company & Financial
* Select balance sheet report > drill down on the inventory account
* Change the dates to the period you are reviewing
* From the report button bar, choose "type" from the "sort by" box
* Also from the report button bar, click on modify
* The lower left of the display tab has a window named columns
* Place an additional checkmark next to item > click OK.

Look for any of the following problems: Do you see any transactions missing an item name? You may discover that you used a transaction type that does not allow for entering "items" or "quantities".

Do you see transaction types without an item, but when you drill down to the original transaction, you have correctly used the item tab and see an inventory item was entered.

Do you see inventory adjustment transactions without an item? When using the transaction type "inventory adjustment", QuickBooks handles the posting to the account "inventory" behind the scenes. Do not use the account "inventory" on these transactions,; instead, select an expense or cost of goods sold account.

Was an inventory item made inactive while it still had a quantity and value? The value will appear in the balance sheet inventory report but not on the valuation report.

Saturday, November 7, 2009

QuickBooks: Deposit Report

A client asked: When I am doing a Bank Reconciliation, I would like a report of deposits that I can total. How can I do that?

Yes, to create a totaled list of deposits do the following:

* Select the reports menu > Select custom transaction detail report
* Click on the 2nd tab titled "filters" > select the filter "account"
* Place a check next to your business checking account
* Add a second filter for transaction type and select "deposit".

The credit column on this report will never contain data so remove it from the report. Find the diamond icon to the right of the word "credit", click and hold, drag it to the left side of credit

To utilize this report every month, give it a title and memorize for future use.

* From the report button bar, click on the modify report button
* Click on the Header/Footer tab and change the report title
* Click OK. Use the memorize button on the button bar to save

Memorized reports are available under the reports menu located at the very top of the QuickBooks window. Change the date range the next time you use this report.

HEAT Plus Mobile Released

FrontRange just announced the launch of HEAT Plus Mobile that enables field-based technicians to access and update critical service management information through a BlackBerry smartphone.

Using HEAT Plus Mobile, not only can technicians be alerted to new problem tickets, they can also update and close calls as soon as the work is done, no matter where they are. They can even open new tickets while on-site. This offers a significant time and cost saving over having to wait for engineers to update tickets from their desk.We'll be doing a whitepaper on this shortly.

Please email me if you'd like more information.

FrontRange Has Just Released HEAT+ HSS 9.0

Last month we did a webinar showing the features of the new release. If you'd like to setup a personal review please let us know.

We have four documents pertaining to this release:

-A Marks Group Whitepaper "5 Cool Things We Like About HEAT 9.0"
-What's New In HEAT 9.0
-HEAT 9.0 Administrators Guide
-Call Logging vs. Calling Web UI in 9.0

If you'd like any or all of these documents (no-charge) please email

New HEAT Whitepaper From The Marks Group

We just wrote "10 Things You BETTER Be Doing With Your HEAT System!". Many of our clients are holding off on upgrading their systems or adding new stuff because of budget limitations. A lot of non-clients are holding off their HEAT implementations for the same reasons. But if you already have HEAT, this whitepaper lists out the features that you already own and should be using.

For a free copy, please email me at


"What's new...and some free training too!"

Hosted by The Marks Group PC and FrontRange Solutions.

Open to Clients and Invited Guests (including prospective users).

Tuesday, December 1, 2009
8:30 AM - 4 PM
Register Online or call
888-224-0649, x801

The beginning of the day will be the HEAT User Group and in the afternoon is free training on implementing SLA's in HEAT.

The Courtyard Marriott at The Philadelphia Airport
8900 Bartram Avenue
Philadelphia, PA 19153


8:30 AM - Noon

Welcome and Introductions / Breakfast
FrontRange Technology Update
What's New in HEAT
9.0 and Beyond / HEAT Roadmap
Customer Implementation Overview
Noon - 1 PM

Lunch and Networking
1 - 4 PM

Free Training: Implementing SLA's in HEAT

The purpose of implementing Service Level Management (SLA) is to transform a typical reactive service department to that of a proactive department which is managed effectively through the Service Level Management processes.

In this three hour session, participants will be trained step-by-step how to:

-Create and configure fields necessary to track SLA information in HEAT;
-Design custom forms and views using the fields created;
-Create and configure critical Alerts for notifying SLA tracking status;
-Create and configure necessary Rules for ensuring an automatic and meaningful SLA process;
-Configure HEAT's Messaging Center to accept communications related to existing SLAs;
-Use HEAT's Answer Wizard reports and Manager's Console to quantify SLA results;

Documentation will be provided. This class is for both beginners and administrators.

More Technologies For Your Company

The Marks Group sells Microsoft CRM, GoldMine and ZohoCRM to help companies improve their sales, marketing and service processes.

We're also certified QuickBooks and Microsoft Dynamics-GP partners and our CPAs have helped hundreds of companies improve their financial systems.

And of course, we do a bunch of Crystal Reporting projects too!

Get CRM and Help Desk Training For FREE

November 16, 2009 - Customizing Microsoft CRM8:30 AM EST - 12:00 PMMicrosoft Training Center - Malvern, PAClick here for more details and to register

December 1, 2009 - Implementing Service Level Agreements1:00 PM EST - 4:00 PM ESTMarriott Courtyard Philadelphia AirportClick here for more details and to register

Upcoming Webinars of Interest

November 24th, 2009

Crystal Reports Tips and Tricks
2:00PM EST (30 minutes)
HEAT Software Tips and Tricks
1:00PM EST (30 minutes)

Microsoft CRM Tips and Tricks
10:15AM EST (30 minutes)

GoldMine Tips and Tricks
9:30AM EST (30 minutes)

QuickBooks Tips and Tricks
11:00AM EST (30 minutes)
January 27th, 2009

Quicker! Better! Wiser! Best Help Desk Technologies For 2009 11:00 AM EST (60 minutes)

Quicker! Better! Wiser! Best CRM Technologies For 2009
2:00PM EST (45 minutes)

Quicker! Better! Wiser! Which QuickBooks Makes Sense?3:30 PM EST (30 minutes)

To register for any of the above webinars, click HERE

The Marks Group Integrates Constant Contact With Microsoft CRM

Ready to send professional bulk e-mail messages to customers and contacts? The Marks Group has entered into a business partnership with the #1 e-mail marketing company Constant Contact If you sign up with Constant Contact through us (prices are the same) we'll support and help you integrate with Microsoft CRM at no charge! Please click here for more information.


A Dynamics - CRM Morning

Free to Clients and Invited Guests

November 16, 2009
8:30 AM - Noon
Register Online or call
888-224-0649, x801

Customizing Microsoft Dynamics - CRM
Hosted by The Marks Group PC


8:30 - 8:45 - Check In
8:45 - 12:00 - Training - Customization
-Basics of form, field and view customizations
-Creating, designing and modifying custom entities
-Building "on-load" events and other tricks to reduce mouse clicks
-Modifying form behavior to better align with your processes
-Q&A and Open Forum

No prior experience necessary. This session is for beginners and moderate users of Microsoft Dynamics - CRM who want to learn how to customize the application.

The Microsoft Training Center
Great Valley Corporate Center
45 Liberty Blvd, Ste 210
Malvern, PA 19355


This month's GoldMine NetSeminars are scheduled for November 12, 2009: GoldMine - Filters, Groups and SQL Queries (11AM EST) GoldMine - E-Mail Management (1PM EST) GoldMine - Customizing (3PM EST)

This month's QuickBooks NetSeminars are scheduled for
November 13, 2009 QuickBooks - Making Sales and Getting Paid (11AM EST) QuickBooks - Paying Bills (1PM EST) QuickBooks- Essential Tasks (3PM EST)All NetSeminars are one hour long and cost $50.More information can be found here

New Whitepaper: How GoldMine Can Cut Your Costs

We all know GoldMine is a great tool for selling, marketing and servicing your customers. But if implemented correctly, it can also help reduce your overhead and cut other costs. Want some examples? Then please email me and I'll send you our latest free whitepaper.

The Marks Group Integrates Constant Contact And GoldMine

Ready to send professional bulk e-mail messages to customers and contacts? The Marks Group has entered into a business partnership with the #1 e-mail marketing company Constant Contact. If you sign up with Constant Contact through us (prices are the same) we'll support and help you integrate with GoldMine at no charge! Please click here for more information.

Invite Gene To Personally Train Your Sales, Marketing, and Service Groups

Gene's "Selling to Michael Bolton - A Quicker Better Wiser Approach To Finding, Closing and Servicing Customers" has been a hit with our clients across the country. Gene's worked with smaller organizations and larger companies like Intuit, Symantec, Citrix and Bank of America.

If you'd like more information about this three-part training program for your sales, marketing or service team, to learn more visit here