How to setup export to QuickBooks
Contents |
Software Requirements
QuickBooks (Australian Version) 2009/2010
For testing purposes, download the QuickBooks free trial. Before installng QuickBooks, make sure that Adobe Flash is on the computer, or you will get an error during installation.
When using the export from Eclipse to QuickBooks, QuickBooks needs to stay open and running. It is okay to do anything in QuickBooks - but - you should not leave it stuck on a modal dialog because that will prevent QODBC from functioning and an exception will result. As 99% of operations in QB do not involve modal dialogs, almost all ordinary work should be able to occur in parallel.
To view patients (aka customers), use the Customer Centre button on the main QuickBooks toolbar.
QODBC Driver
Download from qodbc.com.
We are using QODBC version 9.00.00.261 as of July 2009.
Free 30-day trial unlock is available in trade for email address. There are 3 modes for the install; for testing purposes, use the standalone mode (not involving other users).
QODBC on Vista
UAC must be on. If it was not on, turn it on and reboot. Reference using QODBC on VISTA
QODBC Configuration
You may need to repeat the steps in this section several times: once to configure things prior to cleanup (without Eclipse), and then again to configure things for use during ongoing operation (with Eclipse).
Make sure QuickBooks is running and you are able to open the relevant company files.
QODBC has good documentation, including this page about installation. That page may be worth reading in advance, to understand the range of features available, especially regarding optimization of data transfer. Nonetheless, the essential steps are covered here.
After installing QODBC, go into ODBC configuration (e.g. Control Panel, Administrator, ODBC) and create a new System DSN. Use one of these names exactly:
- QBMedicalObjectsLink: for use during cleanup (without Eclipse)
- QBMedicalObjectsLinkProvider01: change Provider01 to keyword based on Doctor Name
(with Eclipse)
- QBMedicalObjectsLinkProvider02: change Provider02 to keyword based on Doctor Name
(with Eclipse)
For each DSN, click the [Configure] button to get into the dialogs particular to QODBC.
During cleanup, link to the ACTIVE QUICKBOOKS FILE as shown here:
For ongoing use, link to a PROVIDER QUICKBOOKS FILE as shown here:
Granting Permission
You should use the TEST button to TEST each connection. When you do, QuickBooks will ask for approval, several times, as shown below. Be sure to change to the 4th radio button, enabling maximum permissions, so that the export from Eclipse can occur with QuickBooks closed.
Optimizer Settings
Check the Optimizer tab and make sure the radio button is selected "the start of every new connection"). Try to put the temporary files on a high-speed hard disk which is separate from the .qbw data file:
Testing the link
As recommended by QODBC, the simplest way to test the configuration is to use the so-called "VB Demo" which is installed along with the QODBC driver. It will be accessible through Start > Programs > QODBC Driver > VB Demo.
When that runs, use the menu, Connections > Add New Connection. Go to Machine Data Sources and select QBMedicalObjectsLink.
Enter sp_columns Customer and click the Query button. You should see a list of all columns (in the currently-open QuickBooks file).
The data under COLUMNNAME is especially important because that is used when configuring the export feature, to tell the software exactly where to place certain data.
Sample answer using test scenario #4
Each custom field will be VARCHAR 30.
- CustomFieldAllergies
- CustomFieldDOB
- CustomFieldGeneralPractitioner
- CustomFieldInsurance
- CustomFieldPCode
- CustomFieldStreetAddress
Save exact list of fields
To save your answer, use the menu: Edit > Copy All Results. That places the information on the clipboard, after which it can be pasted into Notepad or an email message. This is recommended, because you will probably need to refer to the list several times during configuration and testing.
Note that fieldnames which display in QuickBooks with spaces, such as "General Practitioner", are stored without spaces, e.g. CustomFieldGeneralPractitioner. Whenever Eclipse asks about your QuickBooks field names, it is asking about the version without spaces.
Troubleshooting
It is possible for QuickBooks to deny access to Eclipse, even though permission has been granted. This can occur when the registry becomes slightly corrupt, for example, when there is a power outage or when a virtual machine is only partially restored.
You can reset the permissions inside QuickBooks as long as you are using it in Single-User Mode. On the main QuickBooks menu, select Edit > Preferences, go to Integrated Applications on the left, and go to Company Preferences on the right. The QODBC driver will probably be listed as FLEXQuarters QODBC. If you do not see a checkmark indicating approval, use the Remove button to delete the saved-permission-information. That way, then next time a connection is attempted, the user will again have the option of approving the link.
Clean QuickBooks Data
Assess Status of Existing Data
Use the utility named QBCountExisting to determine whether there are any data entry issues to be resolved prior to configuring a live link from Eclipse to QuickBooks.
Before running any cleanup utilities, be sure to make a backup and optionally prepare a report so that you can quickly review the changes.
Backing up QuickBooks Data
When you back up multiuser QuickBooks from a workstation, you are given a warning that templates and icons are not saved unless the file is backed up from the server.
However you can backup templates yourself. Discussion is in this forum posting. You use the menu: List > Templates and then click the Templates button and then use the Export feature to save a copy to a known folder location.
Custom QuickBooks Report on Custom Fields
It may be helpful to add a custom report into QuickBooks, to aid in proofreading. Here is a sample report (a Customer List), with added fields to show the custom data which needs to be cleaned. (Test scenario #4).
To make this report in Quickbooks, use the menu: Reports > List > Customer_Contact_List. Click [Modify Report] button.
On the Display tab, you should see checkmarks next to fields. You can turn off Phone, Fax, Balance Total and then near the end of the list, turn on any custom fields such as DOB, Allergies, Insurance, GP. Click [Ok] to make Quickbooks remember your selected fields.
You can make columns more narrow by sliding the edges.
Use the [Memorize] button and save the report under a name so that it can be re-used later.
On the main menu, use File > Save as PDF to save a copy of the resulting report.
After Cleanup
The following screenshot gives an idea of what a customer list could look like after cleanup. The Notes field contains the custom data, and all the custom fields have been erased so that they can be re-used for data exported from Medical Objects.
To re-run your saved report, use the menu: Reports > Memorized Reports, and select the one you saved earlier.
Cleanup Utilities
Reminder: make a backup prior to running any cleanup utilities.
Fix DOB
Use the utility named QBFixDOB to fix "close" dates so that they are in perfect dd/mm/yyyy format for Australia.
Re-run the counts and make sure that there are no more DOB errors.
Move data to Notes
Make a backup and then use another utility to move custom data to the Notes field (this utility must be provided especially for your business; upon request). NB: single quotes may not be used within Notes. This utility will stop if it finds any single quotes in the Notes data.
Restructure QuickBooks to Match Eclipse Configuration
The final step of cleanup is to rename the now-blank custom fields so that they are ready to receive data from Eclipse. For example, the Allergies field could be renamed to DVA. The correct structure depends on what one wants to export from Eclipse, which is described in more detail below.
Eclipse
(Note to Medical Objects employees: Eclipse must be compiled with QUICKBOOKS compiler directive, to enable the feature discussed here. New in July 2009.)
Manage Queues
Click Setup to modify the details.
QuickBooks Export
Before using Eclipse to configure the export, it is helpful to decide on an exact plan.
Required Fields
| MO Field Name | QB Field Name | Notes |
|---|---|---|
| Name First | FirstName | |
| Name Last | LastName | |
| PatientID | AccountNumber | In QB, if AccountNumber is already used, a custom field is required |
| Date of Birth | CustomFieldDateOfBirth | In QB, this could be a custom field named DOB or DateOfBirth |
Strongly Recommended Fields
| MO Field Name | QB Field Name | Notes |
|---|---|---|
| Sex | CustomFieldGender | If available, this helps accuracy of matching MO to QB data. |
Optional Built-In Fields
Any of the following fields (which are always available in QuickBooks) may be selected to receive the corresponding data from Medical Objects:
| MO Field Name | QB Field Name | Y to Include |
|---|---|---|
| Name Prefix | Salutation | Y |
| Name Middle | MiddleName | Y |
| Curr Address Street | BillAddressAddr1 | Y |
| Curr Address City | BillAddressCity | Y |
| Curr Address State | BillAddressState | Y |
| Curr Address Postal Code | BillAddressPostalCode | Y |
| Phone - Home | Phone | Y |
| Phone - Work | AltPhone | Y |
| Phone - Mobile | AltContact | Y |
| - | ||
| GP Name and Address; Allergies | Notes | Y |
Select Custom Fields for the Customer (Patient)
NB: PatientID, Date of Birth, Gender, Allergies and GP Details are listed above.
From the list in the table below, decide which data will be used.
QuickBooks allows a maximum of 7 custom fields, and MO may export up to 30 letters and/or numbers into each.
Assuming (a) that PatientID can be stored in the AccountNumber field, and (b) a custom field is used for Date of Birth and Gender, and (c) GP Name and Address and Allergies are combined into the QB Notes field, then QuickBooks allows 4 more custom fields.
Example - custom fields in QB BEFORE integration
This is what custom fields might look like in QB, before integration with MO. The problem with this situation is that the required and recommended fields are not present.
- Insurance - good - can be used
- Allergies - data can be longer than 30 characters
- DOB - good - can be used
- General Practitioner, Street Address, City PCode - use up 3 fields; should move to Notes
Example - custom fields in QB AFTER adjustments
This is how custom fields might be used in QB, after making adjustments to allow for the required and recommended fields. Note a maximum of 4 fields have been marked Y to Include.
| MO Field Name | QB Field Name | Y to Include | Notes |
|---|---|---|---|
| MedicareID | CustomFieldMedicare | Y | |
| PensionID | CustomFieldPensionID | . | |
| DVA | CustomFieldDVA | Y | e.g. QX99999A |
| SafetyNetID | CustomFieldSafetyNet | . | |
| Date of Death | CustomFieldDateOfDeath | . | |
| Referring Party | CustomFieldReferredBy | Y | |
| Private Health | CustomFieldInsurance | Y | Health Fund Abbreviation, e.g. eg MBF, MBP, HCF, Westfund, NTF, plus
Health Fund ID: e.g. 99999999Y |
Activate Desired Fields
Based on those decisions, activate or de-active fields in the configuration list.
After selecting custom fields and, if needed, changing the name of any of the custom fields (e.g. DOB versus DateOfBirth versus Date Of Birth), use the Validate button to find out whether the settings match the company file currently open in QuickBooks.
If there are problems, the message area will say Bad News. To find out which field(s) are problematic, scroll up for detail. Use the slider bar to make more of the message visible at once.
Should you require any more information or have encountered a problem please call the support helpdesk on (07) 5456 6000.
