Thursday, July 30, 2009

GoldMine: Check Out Your Customers

A client asked: We use a number of websites to check out customers when we first sign them up. Is there a way to organize these sites with Goldmine?

Yes, you can use the GM+View feature in Goldmine to list the sites you want to check when you first take on a customer. The steps are below:

* Select Web > Setup GM+View
* Click New > Add a Template Name
* Click the large text area > click the <H> in the right corner
* Position the cursor after the </style> html tag entry
* Copy the html below over the remaining text in the GM+View

<META content="MSHTML 6.00.6000.16825" name=GENERATOR></HEAD>
<BODY leftMargin=1 topMargin=1 rightMargin=1>

<P><FONT face="Microsoft Sans Serif" size=2></B><STRONG>Use these links to check out your prospects: </STRONG></FONT></P>

<P><FONT face="Microsoft Sans Serif" size=2> </P>
<DIV><A href="http://www.whois.org/"><FONT color=#0000ff>Whois</FONT></A></DIV></FONT></A>

<DIV><A href="http://www.yellowpages.com/"><FONT color=#0000ff>Yellowpages</FONT></A></DIV></FONT></A>

<DIV><A href="http://www.weather.com/"><FONT color=#0000ff>Weather.com</FONT></A></DIV></FONT></A>

This will give you three web sites you can click on to find out information about your client's area, domain, and weather. You can add other sites by copying one of the sections and adding your own site to search.

Then click Save (Yellow button) and exit. Select the GM+View from the drop-down menu at the right.

GoldMine: Track By Color

A client asked: Is there an easy way to change labels and data fields based on the contents?

Yes, there are at least two ways. One is to the Goldmine Record Typing function. I have found this to be complicated to use if you only want to change field color. The second way is to use a simple dbase expression in the properties of the field you want to track (e.g. KEY1).

See the example below. It changes the KEY1 label and data field based on the contents. To use it:

* Right click on the Goldmine form > select Screen Design
* Double click on the KEY1 field > select the Color tab
* Enter the following expression in both the label and data areas:

IIF(KEY1='First', 139, IIF(KEY1='Second', 10526303, IIF(KEY1='Third', 25600, 0)))

Change the sample data (First, Second, Third) to your KEY1 data and you have an easy way to track fields by color.

GoldMine: Google For Goldmine

A client asked: Is there a site that searches just for Goldmine error codes? When I use a search engine I get too many unrelated entries.

No, but you can create one. Google now provides a way to create your own focused, community driven search engine for a specific subject area. I took the liberty of creating a GoldMine one. It can be found here: Google for GoldMine Tech Stuff

Also like other search wiki's, this search engine can be customized by the users! If you see something in your results that has nothing to do with CRM software or GoldMine, tell the engine and it will respect that the next time you (or anyone else) searches for the same thing!

The search box can even be embedded on web sites or e-mails. To create one, go to this site: http://www.google.com/coop/cse/

* Click create custom search
* Fill in a search engine name (e.g. Google For Goldmine)
* Enter a description
* Leave language as English
* Leave sites to search as "Only sites I select"
* Add sites to search with each on one line
* For example: www.djhunt.us, support.frontrange.com
* Select the Standard option (the other starts at $100)
* Click Next

Test a search in the search box on the next page. An email will be sent to you with the site address (it is saved on google.com so it available to others). There will also be instructions with the email on modifying the site, adding sites, etc.

To see one created by The Marks Group, paste the following into Internet Explorer:
http://www.google.com/coop/cse?cx=006246694855219629252:lfwbaxvxp7q

Add this url to your favorites so when you have a Goldmine error, it will only search Goldmine specific sites.

Goldmine: Display Multiple Contacts Simultaneously

A client wants to know: How can you 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: Searching Notes

Did you know that you can search through the notes in GoldMine?

You can search your notes in GoldMine using the Contact Search Center.

* Open the Contact Search Center.
* Select Search By > select Notes from the drop down list.
* Contains is autofilled > enter the text to search for
* As you enter text in the search field, it will bring up contacts

The result will list all contacts whose notes contain the word(s) you have searched for.

Note: this will also search for words that are anywhere in the Note, so if you are looking for the word 'hired' in Notes, it would show "Dave was hired in 2001", "He was hired 60 days after the interview", etc.

GoldMine: Scheduling for Non-Goldmine Contacts

A client asked: When I have someone on the phone, I do not want to take the time to add a contact to Goldmine before I schedule a meeting. How do I create an activity for new contact when the contact is not in Goldmine?

You can actually create a new Contact while scheduling an activity:

This is useful when you realize that the contact that you want to schedule the activity for does not exist in your GoldMine. So, instead of canceling the scheduling activity, you can create a new contact in GoldMine on the fly.

Here is how you do it:

* Select Schedule > Appointment to open 'Schedule an Appointment'.
* Fill in all the details and set the alarm.
* Click on the button to the right of the 'Contact' field
* From the options, select 'Create a new contact'.
* This will open a New Company and Contact dialog box.
* Fill out the contact information and select OK.

A new contact is now created in GoldMine along with the activity scheduled for that contact.

GoldMine: Scheduling a Team

A client recently asked: Our company schedules activities for a team, not an individual. Can GoldMine schedule activities to a team?

If you have more than one person in your sales team working on the same account, you can use GoldMine to schedule tasks to everyone involved at the same time. Here's how you can do it:

* Select the contact that you would like to schedule.
* Select Schedule and choose the type of activity.
* In the next window, click on the Users tab
* Select the users you wish to add
* Click on the user name in the left-hand column
* Use the Select button to add the user to the right-hand column.
* Use the Free/Busy tab to check scheduling conflicts for the users.
* Once you have added all the sales people, click on Schedule.

This will add the activity (appointments, calls, etc.) to each GoldMine user’s calendar and activity list.

GoldMine: Multiple Websites

A client wants to know: Is it possible to add multiple websites to a single contact record?

If you have contacts that have more than one website or a customer that added another website and you want to retain both the websites, GoldMine has an option that allows you to enter additional websites for the same contact record.

* Open the contact record, for which you would like to add an additional website, and click to the right of the Web Site field. The 'Web sites' window will open and you can type in the web address.

* This window allows you to add multiple web sites, and to update the URL as needed without losing the original URL. Click OK to exit the window.

* Another option is the Details tab. You will find your contact's website listed under the Details tab. Right-click on it, select New and type in the additional web site URL.

This feature allows you to enter as many websites for a contact record as you want.

GoldMine: Recently Viewed Contacts

A client asked: How do I change the number of recently viewed contacts in GoldMine

In GoldMine Premium, you set the number of recently viewed contacts to display. Just select the following:

Tools | Options | System

Change recent items shown to an appropriate value.

Remember that having a high value, could effect the performance of your GoldMine system (i.e. it might make it slower).

GoldMine: Major Activity Types

A client asked: When logging my activities I use the same Activity Type (Next Action) for everything. Is there a better way to organize my schedule?

Yes, many GoldMine users find the nine different activity types a bit confusing. Here is a brief activity type description and when to use each. They are broken down into four Major types which you’ll use all the time and then the rest.

Four Major Activity Types:

Calls – Phone calls (not a sales call when you go visit someone), follow up phone calls, introductory phone calls, conference calls, and other call types.

Next Action – This is used as a “To Do” related to a prospect or customer such as writing up a quote, preparing for a demo, doing research or following up via email.

Appointment – Face to face meeting could be for sales, service, or an internal meeting.

Sale – Forecasted Sales are to help you develop and keep track of your sales pipeline up to date so you know what deals you have in your sales funnel.


The Remaining Types:

Literature Request – Seldom used portion of GoldMine that lets your delegate the task of sending our literature.

Other Action – Activities that can’t be categorized into one of the other categories.

Events – Multiple day activity such as sales conference or sales meeting. These don’t show up in the timed portion of your calendar in the Day and Week view so it can be easy to schedule over them.

To Do – A generic to do. These don’t have due dates associated with them so I generally prefer to use Next Actions for anything that’s time sensitive. Also, To Dos are not, by default, linked to a contact record.

GoldMine Email – An email sent to another GoldMine user via it’s internal email center. These won’t go do Outlook so unless you’re using GoldMine for your incoming and outgoing back you’ll be better off not using these as the message might not get read.

The GoldMine calendar will show a small icon indicating which activity type you’ve scheduled and will be organized by the four Major activity types.

Wednesday, July 29, 2009

HEAT: Error When Adding Call

A client asked: When trying to log new call, the following ODBC error appears: 'Incorrect syntax near the keyword with'. What can I do to resolve this?

This error occurs because the "with" statement was added to lock the Heatseq table while other users were accessing it, thereby creating a scenario that would eliminate duplicate Call IDs.

The full error is:

Incorrect syntax near the keyword with Last SQL String:
Update HEATSeq With (Serializable,TabLockX) Set SeqValue=SeqValue+1 where SeqKey=CallID Last ODBC Command:
Function: SQLExecDirect(Update HEATSeq With (Serializable,TabLockX)
Set SeqValue=SeqValue+1 where SeqKey=CallID)

To get around the error, create a file called Winheat.ini in the System folder, create an [OPTIONS] section and then add the following command: NOSQLSERVERLOCK=1

This should allow you to add new calls without the error.

HEAT: Differences Between Call Logging and WebUI

A client wants to know: HEAT 9.0 has two Call Logging interfaces. Are there major differences between Call Logging and Call Log WebUI?

HEAT 9.0 features the normal Call Log client (CallLog.exe) and a new Web User Interface. The Web User Interface offers most of the functionality of Call Logging, but there are differences. Listed below are: 1)Features in Call Logging, not in WebUI; 2) Features in WebUI, not in Call Logging:

1) In Call Logging, Not in WebUI
--------------------------------
Place Calls On Hold
Create and View Broadcast Messages
Customized Toolbars
SupportMail
HEAT Alarms
View Activity Log, Create Activity Log
Call Logging Dashboard
Table Maintenance
Asset Configuration Window
Call Transfer
Crystal Reports
Create AutoTask Actions
Jump to Control
Create Profile, Update Profile
Create Contact, Update Contact
Create Configuration, Update Configuration
Create Customer Login, Update Customer Login
Send A Broadcast
If Field is Empty
Display Message box
Goto Call(Ticket)
Create HEATBoard Issue
Web Browse
Run A Program to Launch Other Files
Write A File To Disk
Print Information
Create Activity Log Record
Execute External Service Request
Perform Dynamic Data Exchange
Create An Alarm
Execute SLA with ITSM
DateAdjust - Custom Calendar
DateDiff - Custom Calendar
TimeAdd - Custom Calendar
TimeAdjust - Custom Calendar
TimeDiff - Custom Calendar
StrConcat, Strlower, StrUpper, StrSub
StrTrimL, StrTrimR
DDERequest
IF
ReadFile
Move back one field Shift+Tab
Undo Ctrl+Z
Alarms Shift+F11
Assignment, Acknowledge Shift+F2
Assignment, Resolve Ctrl+Shift+F2
Call, First Shift+F7, Call, Last Shift+F8
Call, Next F8, Call, Previous F7
Call Group, Close Ctrl+F4
Call Group, Open Existing Ctrl+O
Call Map, Go to Ctrl+Alt+M
Call on Hold, Place Ctrl+H
Call Record Browse, Display Ctrl+B
Call Ticket Report, Run Ctrl+Alt+R
Clear a Date or Time Field F2
Crystal Decisions Crystal Reports Ctrl+D
Customer Types, Change Ctrl+T
Display Calendar in Date Field F4
Fill in System Date/Time F3
First Level Support (FLS) Ctrl+F
HEATBoard Current Call Information Ctrl+Alt+C
HEATBoard, Go to Ctrl+Alt+B
Help F1
InfoCenter, Show Ctrl+Alt+I
Moves between Call Groups Ctrl+Ta
My Hot List, Add Call Ctrl+M
Next Assignment/ Journal Ctrl+Page Down
Previous Assignment/ Journal Ctrl+Page Up
Print Active Call Group Ctrl+Shift+P
Print Call Ticket Ctrl+P
Profile F5
Refresh Call Record Ctrl+F5
Refreshes Active Call Group Ctrl+E
Run Crystal Report Ctrl+Shift+D
Run/Edit Report Shortcut Ctrl+Alt+D
Search, Perform Ctrl+Shift+S
Spell Check Shift+F9
Subset, Autosize F12
SupportMail F11
Un/Lock Call Record Ctrl+L
Module Integration
FrontRange IT Service ManagementYesNo

2) In WebUI, Not in Call Logging
-------------------------------
Create Assignment from Call Log Short Cut
Create Journal from Call Log Short Cut
Add Attachment from Call Log Short Cut
Tabbed Browsing

HEAT: Crystal Quick Report

A client asked: Is there a simple way to create Crystal Reports without a lot of training or experience?

Yes, the report creation process can be simplified by using a single report to show whatever data you want to see. This can be done by passing a Call Group into an unrestricted report.

To create this universal report, you would:

* Create a single Crystal Report without parameters.
* Add the fields you want to see and nothing else.
* You can use an Answer Wizard report or start scratch.
* Create a report shortcut within Call Logging.
* Pick the Selection Formula tab > Limit to current call group at runtime.
* Create and run a call group and then run the report you just created.

This technique is good a particular printout to view their assignments in a simple grid.

HEAT: Securing ODBC

A client asked: Is ODBC and SQL authentication secure? Could this allow someone to breach our database security?

Locking down ODBC is very important. There are reasons which require SQL Authentication, for example using modules like HEAT Self Service. However, if your HEAT database uses MS SQL and everyone logs in via Microsoft Active Directory, and you continue to use SQL Authentication, then consider the alternative below:

* Create an Active Directory group called HEAT Users and another called HEAT Admins.

* Place Active Directory Users inside their respective groups.

* Go into SQL Studio Manager and create an SQL user that points to the HEAT Users group and another that points to the HEAT Admins group.

* Give HEAT Users public, db reader and db writer rights and HEAT Admins database owner rights.

* Update all of your HEAT Users' ODBC connections to use Windows Authentication, instead of SQL Authentication. Be sure to place whoever commits edit sets into the HEAT Admins group.

Following these steps will improve security and solve the problem of always having to enter an SQL password when logging into HEAT. Remember to include any HEAT services that use a service account into at least the HEAT Users group so that it may continue to log into the database.

Also, HEAT Web UI and HEAT Self Service will continue to need SQL Authentication. If you are using either of these modules, it may be a good idea to tighten security for that SQL user so that it only has normal public rights to the database.

HEAT: Speed Up Edit Sets

A client recently asked: Whenever we commit an edit set, it takes hours to commit. Is there a way to speed up the process?

Understanding how an edit set works can shorten the time to commit. For example if you have a 800MB, 500K record HEAT system and perform several changes on it, this could take several hours to commit. To shorten the time, make fewer changes in each edit set and the process will be a lot shorter.

No matter the size of your HEAT system, the following will speed up changes:

Use the HEAT Server: Try and commit the edit set on the same server the database is on as it keeps the data local. If your security policy does not allow you to run the Admin tool on the SQL server, then keep the edit sets small. One table at a time, on a machine that shares the same network router.

Know Your Changes: The changes you make directly affect the length of time of your commit. For example, if you add a field to the CallLog table, which has 500K records, those 500K records will have to unload first, then load back into the table after the commit adds the new field.

If you make several changes to the CallLog table, it will only unload and load those records once. If you have many changes to make in one table, do it all at once, instead of making multiple edit sets. This applies to all other tables.

How Edit Sets Commit: Edit sets commit the tables alphabetically. HEAT's main tables are Assgnmnt, CallLog, Config, Detail, Journal, Profile, and Subset. You will know that your edit set is almost done when you see the Subset loading back in.

Know What To Use: Adding, renaming, changing field types, and removing fields and tables require an edit set. Marking a field read-only or required, setting a field to validate or autofill, along with normal form changes only require a quick customize. A quick customize with 500K records only takes a few seconds.

As always, back up your HEAT system before making any changes

CRM: Restrict Direct Email Functions

A client asked: A user accidentally emailed every contact in our database. Is there a way to restrict the use of the Direct Email button?

When you open the Account or Contacts entity, you will see an email button on the toolbar. This button allows you to select a group of contacts or accounts via a view or advanced find, and send them an email. This functionality can be a very useful but it can also be dangerous, as a user can easily email every contact or account in your database.

It is best to restrict access to Direct Email to only those users who need to have access.

Restricting access to Direct Email, however, is not an obvious process. There is no permission in CRM security roles for “Direct EMail” or “Bulk Email.” There is a permission option for Email; however, you won’t want to restrict users from creating emails. If you do, they will not be able to track their email communications in CRM, which is a major reason to use CRM.

The answer is in the way that Direct Email works. Direct Email is dependent on templates — you can’t just hit bulk email and type a message. You have to first create an email template, and then select that template from the Direct Email form.

So, to restrict Direct Email functionality from a group of users, use the following process:

* Select Settings > Administration > Security Roles
* Open the Security role of the group of users restrict
* Click the Core Records tab
* Remove permissions for Email templates
* Save and close the Security Role.

Now when the users with this role log in to CRM, they will no longer see the Direct Email button on the toolbar

Tuesday, July 28, 2009

CRM: Replace Primary Entity Lookup

A client recently asked: Can I display a related entity field in a CRM Lookup instead of the primary entity field?

Yes, but it requires some javascript. A CRM lookup only displays its related entity primary field. This cannot be changed using customizations, however, there are occasions where you want to display another information in order to avoid opening a related entity form.

The javadcript function below will display another attibute inside CRM lookup.

Example:
On a contact form you want to display the accountnumber field, inside the parent customer lookup, instead of primary field of the account entity.

Select Settings > Customizations > Customize Entities
Select Account > open the Main Application Form
On the form select the lookup > select Events > select Onload

Step 1:
In the onload event, add this line of code and change LookupSchemaName to the schema name of your lookup.

crmForm.all.LookupSchemaName.FireOnChange();


Step 2
On the onchange event of the lookup, copy the following code:
---------------------------------------------------------------
Fields to add:
fieldToDisplay = the name of the attribute that you want to display in the lookup.
Take the schema name of the attribute in the linked entity.

fieldToDisplayIsText:
true if you want to display a nvarchar field.
false if you want to display a picklist or a lookup field.

organizationName = name of your organization (without spaces).
---------------------------------------------------------------

Javascript code:

var fieldToDisplay = 'accountnumber';
var fieldToDisplayIsText = true;
var organizationName = 'MyOrganizationName';
var lookupData = new Array();
var lookupItem= new Object();
var lookup = event.srcElement.DataValue;

if (typeof(lookup) != 'undefined' && lookup != null && lookup[0] != null)
{
var myValue = GetAttributeValueFromID(lookup[0].typename,lookup[0].id,fieldToDisplay,fieldToDisplayIsText);

if(myValue != '')
{

lookupItem.id = lookup[0].id;
lookupItem.typename = lookup[0].typename;
lookupItem.name = myValue;
lookupData[0] = lookupItem;
crmForm.all[event.srcElement.id].DataValue = lookupData;
}

}

function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName, isTextField)
{
var xml = "" +

"" +

"" +

GenerateAuthenticationHeader() +

" " +

" " +
" " +
" " +
" " + sEntityName + "" +
" " + sGUID + "" +
"
" +
" " +

" " +
" " + sAttributeName + "" +
"
" +
"
" +
"
" +
"
" +
"
" +
"
" +

"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);

xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Execute");

xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

xmlHttpRequest.setRequestHeader("Content-Length", xml.length);

xmlHttpRequest.send(xml);

var result = null;

if(isTextField){

result = xmlHttpRequest.responseXML.selectSingleNode("//q1:" + sAttributeName).text;

}

else

{

result = xmlHttpRequest.responseXML.selectSingleNode("//q1:" + sAttributeName).getAttribute('name');

}
if (result == null)
{
return '';
}
else
return result;
}


Step 3:
Save and publish the entity

Note: This code be used for other lookups, the account is just provided as an example

CRM: Instant Messaging in MSCRM

A client asked: Is there a way to build Instant Messaging into the Contact profile so it can be used to communicate with them?

Yes, it is often a problem to arrange a meeting, respond to a service request, or obtain some critical information to move forward on a request Phone calls and email often are not replyed and hold up communication with your customer.

One way to rectify this delay in communication is to use your Instant Messaging program. You can add IM as a option, within MSCRM, like phone call or email.

* Open a Contact view like Active Contacts in CRM.
* Find the Contact you would like to add > don’t open the record.
* Select the small circle icon in front of the Contact name.
* Right click the circle icon and you will see several options.
* One of the options is to add the Contact to your IM program.

Select this option and the Windows Live wizard will open to allow you to add the contact to Windows Live Messenger and will send the Contact an invite to install Windows Live Messenger if they do not have the application installed.

After you complete the wizard, select the circle icon next to the Contact Name. The “Reply with Instant Message” option will be available and you can now send the Contact an instant message so that you can move the business items forward without the delays of phone calls or email. Additionally, the circle icon will turn Green if the Customer is online and available.

This feature can really come in handy for customer satisfaction items like arranging a quick meeting to discuss new features of CRM or directions to the closest Starbucks.

CRM: Clear Manager

A client had this problem: When a Manager is assigned to a User, I cannot delete it. How do I clear the Manager field and leave it blank?

To clear the Manager field for a User, you need to:

* Select Settings > Administration > Users
* Open the User > select Actions > select Change Manager
* Leave the New Manager field blank > click OK
* The Manager field in the User settings will be cleared

You cannot remove the Manager by clicking in the Manager field and pressing Delete. You must use the Actions > Change Manager option.

CRM: Viewing Custom Entities

A client asked: I created some custom entities which appear in the workplace toolbar. To see them, the users need to have the System Customizer role. Is there another way?

Yes, you need to do the following for the users to see your custom entities:

* Select Settings > Administration > Security Roles
* Create a new Role (or copy their existing Role)
* Select the 'Custom Entities' tab > assign Permissions

This will allow the users, with this new Role, to see your custom entities but not make changes to them.

Monday, July 27, 2009

QuickBooks: Sort To Find Problems

A client asked: When we have a problem with a list report it often takes hours to reconcile. Is there a trick to finding problems quicker in long list reports?

Yes, sorting the list report can make finding problems much easier. Finding problems with list entries can be like looking for a needle in a haystack, especially when the lists are very long (e.g. an item list for a company that uses inventory items).

This method assumes that most of the information in the lists is correct, and if so, you are looking for irregularities. Perform the following steps for this trick:

1. Create a list report (available for customers, vendors, employees, items, terms, to do notes and the memorized transaction list). For this example create an Item Listing report to locate items that have the incorrect account assigned to the Income account field.

2. Make sure the income account field shows as a column on the report. The column will read simply Account as opposed to the COGS Account and Expense Account columns.

3. Sort the report by the Account column. First check the top and bottom of the column for anything that is not an income account. Then, scan up and down the list and check each place in the report where the value in the Account column changes (e.g. from one income account to another).

With this method you don’t need to look at each item on the list – just the items that present something usual or out of the ordinary in the Account column.

Then sort the report by other columns (adding columns as necessary) to proof the other fields in the item setup windows.

QuickBooks: Customer and Job Types

A client recently asked: How can I get custom fields to appear on all QuickBooks reports, not just on list reports?

When you create custom fields for customers and jobs, the custom fields will impact list reports only (e.g. the customer contact list) unless you include the custom field on a sales form template (e.g. an invoice template).

For example, if a garage has a custom field called Car Model they will not be able to run sales reports by car model unless they include the Car Model custom field on a Sales Receipt or Invoice template.

The field does not have to show on the printed form, but it does have to show on the screen and it has to be populated with the correct information. For example, the Invoice on the screen would have to show the Car Model field with the word Dodge Viper

The customer type and job type fields are much more powerful and global in impact. If you use the customer type field to track Car Model, you do not have to modify any sales forms to create sales reports around this information.

In fact, showing the customer or job type on a sales form template is not even a possibility. Just having this information in the customer or job record allows you to filter and sub-total reports based on the fields.

Also, these fields will impact all historical transactions on which you used the customer or job (e.g. historical invoices). The custom field will affect future invoices only – unless you edit the historical invoices to enter Dodge Viper into the Car Model field.

QuickBooks: Tracking Sent Invoices

A client asked: How can I create a report to show invoices that have been sent and when they were sent?

QuickBooks does not have any standard reports to track invoices, sales receipts and credit memos by sent status or method. In other words, you cannot get a list of invoices broken down by those that were emailed, those that were mailed or those that were sent both methods.

You have two options for tracking this information:

Use a custom field called Sent Method and a second custom field called Sent Status. You can make these fields to show on the screen but not to show on the printed invoice. Then, you can filter a list of invoices (e.g. on a custom transaction detail report filtered by Transaction type – Invoice) by the data in this custom field.

Since these reports will only be as accurate as the data entered into the custom fields, you may want to create a custom field with a drop down menu that has three selections: Emailed, Mailed, and Both.

Create a unique template for each sent method. Duplicate an existing sales form template, name the new templates: Emailed, Mailed, and Both. If you prefer, you can create have a fourth template called “Not Sent.”

When you filter an invoice report by Template you can get a list of invoices that you have emailed, mailed or both as well as those you have entered into QuickBooks but have not yet mailed or emailed. When you toggle from one template to another, no information on the screen will change, but you will change the memorized reports that you filtered by template.

Since the template list has a built in drop down menu, you solve the issue of data entry accuracy that you might have around a custom field. You also have the ability to modify the sales forms slightly depending on which method you use to send the invoice.

For example, it might be critical for the addresses on a mailed invoice to line up in a window envelope, but not on the template you sent via email.

Saturday, July 25, 2009

Crystal Reports : Using an "all" choice within a parameter

Let us consider our {?UserID} parameter, which accepts multiple values.
Let us further consider that the end-user will need to occasionally print the report for all UserID's. They could simply add each UserID to the parameter before printing the report, but in cases of many UserID's, this becomes cumbersome.
A better solution is to give an "all" option in the parameter choices, then use the following code:

if {?UserID} not like "*all*" then {SALES.USERID} = {?UserID} else true

Here is what's happening:
IF our parameter DOES NOT have he word "all" within it, we execute the Selection Criteria normally. The trick here is that our ELSE clause ends in TRUE, which means that we "skip over" this part of the Selection Criteria, therefore returning ALL UserID's.

Crystal Reports : Using Notes Fields

When inserting "notes" fields into a Crystal Report, some things must be considered:

- After inserting the field, you may specify whether or not it "can grow" (meaning vertically) by going to the fields' Formatting page and selecting "Can Grow". You may also specify how many lines it "can grow" by.

- Certain databases keep their notes in HTML format, in which case the output will look garbled. You may, again, go to the fields' Formatting page and go to the Paragraph Formatting tab, then select a "Text Interpretation". Choices include HTML and RTF (Rich Text Format).

Crystal Reports : Using Parameters with Multiple Values

Let us consider a parameter named {?UserID} within our Sales Report.
We can allow the end-user to select multiple UserID's by creating our {?UserID} parameter to "Allow Multiple Values". This is a checkbox on the main parameter property page.
This will allow the end-user to add however many UserID's to the report output they want. The "Allow Multiple Values" functionality is also transparent to the Report Selection Criteria.
Example:
{SALES.USERID} = {?UserID} will return every userid the end user has selected.

Crystal Reports : Eliminating Zero Divide Errors

When division within calculated fields, there is always the risk of getting the dreaded "zero divide error". This happens, obviously, when a calculation tries to divide te number zero.
And since we all know that underlying data cannot be trusted 100%, it is necessary to test for these zero divide errors within our Formula Field.
Example: The formula field called {frmSalesPerDay} is a simple division of {NumSales} by {NumDays}.

So our {frmSalesPerDay} code should look like this:

if {NumSales} = 0 then 0 else {NumSales} / {NumDays}

So, if {NumSales} equals zero, then we simply return a zero and exit the formula. Otherwise, we let it perform the division.

Monday, July 20, 2009

QuickBooks: Grouping Credits/Payments by Invoice

A client asked: How do we group invoices and payments to show which payments are posted to which invoices? The standard reports do not permit this.

QuickBooks links transactions together and allows you to create a limited set of reports based on those links. QuickBooks also uses the links to change the paid status of transactions when Invoices are linked to Customer Payments and Bills are linked to Bill Payments.

The problem is that when you create a Customer Balance Detail report, QuickBooks shows both Invoices and Payments, but the report does not group the invoices and payments to show you which payments post to which Invoices.

The same limitation applies to the Unpaid Bills Detail report. This makes it difficult to tell which payments are applied to which invoices when speaking with customers.

The workaround is as follows: Since the Customer Balance Detail report groups transactions by job, you can use jobs to group Payments and Credit Memos with the Invoices to which they are applied.

Perform the following steps to use this workaround:

Create a unique Job name for each new Invoice you create for the Customer. If you already use jobs and you will have more than one invoice for the job, use a separate sub-job for each Invoice. Include the invoice number in the job name.

When recording Credit Memos for the customer, refer to the applicable job (with Invoice number reference). If the Credit Memos applies to multiple jobs/invoice, record multiple Credit Memos to divide the total credit amount across multiple jobs.

When recording Payments, refer to the applicable job (with Invoice number reference). Payment transactions allow you to enter the customer’s name only and apply the payment across multiple jobs.

However, to use this workaround you must enter both the customer and job name (with Invoice reference). If the payment applies to multiple jobs/invoices you will need to enter multiple payment transactions to divide the total payment amount across multiple
jobs/invoices.

When you create a Customer Balance Detail report, QuickBooks groups all AR activity by the Invoice number/job number as shown in the report below.

Note: This workaround has a downside. You lose the ability to apply a single payment from a customer across multiple invoices. You must decide which is more important for you – to quickly show and report on the Invoices by the payments and credits applied to the invoice or the ability to apply the customer’s payment across multiple jobs. You can apply this workaround to a select group of customers if doing so makes it more practical.

QuickBooks: Tracking Worker's Compensation Expiration

A client recently asked: We would like to track Worker's Comp Expiration Dates for vendors. Do we need to purchase the QuickBooks Contractor Edition to do this?

The Contractor Edition of QuickBooks includes special fields in the Vendor setup window to track Worker’s Compensation expiration dates. However, if you use other QuickBooks versions, you don’t have access to these fields. There is a workaround, however, outlined below:

Use these steps to track expiration dates in the Vendor window:
* Open the Edit Vendor window for any vendor
* On Additional Information, add a field: Worker’s Comp Expiration.
* In the Vendor Type field, create Vendor Type “Subcontractor”.
* Add the Worker’s Compensation date to each vendor (sub-contractors)
* Set each vendor’s Vendor Type to “Subcontractor.”

Note: You must enter the date using a two digit year then a two digit month and then a two digit day. If you do not use this format, the report you create will not sort by date.

Create a Custom Report to track the expiration dates as follows:
* Select Reports > select List Reports > select Vendor Phone List.
* On the Display tab, add a column for Worker’s Comp Expiration.
* On the Display tab, sort by Worker’s Comp Expiration.
* On the Filters tab, filter by Vendor Type for “Subcontractors.”
* On the Header/Footer tab, assign an appropriate name and memorize.

This will allow you to track the Worker's Comp expiration date. This process can be used for other vendor expiration dates such as Liability Insurance.