Export Tickets to Accounting

Supported Accounting Systems

Keystone provides the ability to export tickets to third-party accounting systems. The currently supported export formats are:

• QuickBooks Pro 2002 and higher (direct connection)
• MAS-90
• BusinessWorks Gold V4
• Alkon Spectrum (822-Byte Format)
• CDS (Binary)
• CDS (XML)
• Standard (Generic)
• EXF (flexible export format using .exf files)

QuickBooks Pro

NOTE: QuickBooks Pro export was added in Keystone 2.5.

The Keystone to QuickBooks integration is a direct connection. During the export process, invoices are added directly to QuickBooks. This means that there must be a physical connection between the Keystone PC and the QuickBooks PC. Also, if QuickBooks is running on a different PC than Keystone, a small "helper" application must be run on the QuickBooks PC to handle the network connection with the Keystone PC.

Keystone Location Transfer Out Parameters

In this example, QuickBooks and Keystone are on the same PC.

• ApplicationName identifies Keystone to the QuickBooks user
• CompanyFile is the full pathname of the QuickBooks company file to be used

EXPORTTYPE=QBI
FILE=ApplicationName="Keystone" CompanyFile="C:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Company Files\Demo Company.QBW"

In this example, QuickBooks and Keystone are on different PC's connected via a network.

• URL specifies the IP address/host name and port of the QuickBooks PC
• User specifies the username set on the QBConnector start-up command
• Password specifies the password set on the QBConnector start-up command

EXPORTTYPE=QBI
FILE=URL="http://192.168.100.2:2080" User="qbuser" Password="qbpass"

QBConnector Helper Application

There is a small "helper" application included with Keystone called QBConnector.exe. This application must be running on the QuickBooks PC to accept network requests from Keystone. Several command-line options are supported and must be specified for the connector to work.

• qbuser represents the QBConnector user name
• qbpass represents the QBConnector password
• -companyfile specifies the full pathname of the QuickBooks company file to be used
• -appname identifies Keystone to the QuickBooks user

"C:\Documents and Settings\pmorey\Desktop\qbconnector.exe" qbuser qbpass -companyfile "C:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Company Files\Demo Company.QBW" -appname Keystone

Initial Configuration

QuickBooks needs user confirmation the first time a foreign application (in this case, Keystone) connects to it. For this reason, QuickBooks must be running prior to the first Keystone connection attempt. When Keystone connects to QuickBooks for the first time, a "QuickBooks Application with No Certificate" warning dialog:

Select the option "Yes, always; allow access even if QuickBooks is not running" and check the box that says "Allow this application to access personal data...". Click the Continue button. You will see a "Confirm to Proceed" dialog:

Click the Yes button. Finally you will see an "Access Confirmation" dialog:

Click the Done button.

Integration

QuickBooks is not as restrictive as most accounting systems in that it allows "free form" data for identification of customers, products and tax rates. As a result, Keystone maintains an internal translation table that uniquely matches Keystone customers, products and tax authorities to their QuickBooks counterparts. QuickBooks maintains a unique, hidden "Ref Id" field for each customer, product and tax rate. This "Ref Id" field is what Keystone uses to link the systems together.

Keystone has a generic translation table (CCTXTRL) that is used to translate customer, product and tax authority IDs from Keystone to QuickBooks. Translations can be set up ahead of time by generating records in the CCTXTRL table, or "on the fly" during export.

Customer Translation

Unless a translation record is found for the customer in the CCTXTRL table, Keystone will attempt the following default translations before displaying a dialog box asking the user to map the customer:

• Keystone Customer ID <=> QuickBooks Customer Name
• Keystone Customer Name <=> QuickBooks Customer Name

CCTXTRL fields for QuickBooks Customer Translation
--------------------------------------------------
XLT_TYPE	'QBI'
TABLE_NAME	'ARTCUST'
FIELD_NAME	'CUST_NO'
FIELD_VALUE	Keystone CUST_NO
EXT_VALUE	QuickBooks Customer Ref Id

Product (QuickBooks Item) Translation

Unless a translation record is found for the product in the CCTXTRL table, Keystone will attempt the following default translations before displaying a dialog box asking the user to map the product:

• Keystone Product ID <=> QuickBooks Item Name
• Keystone Product Description <=> QuickBooks Item Name

CCTXTRL fields for QuickBooks Product Translation
-------------------------------------------------
XLT_TYPE	'QBI'
TABLE_NAME	'ARTPROD'
FIELD_NAME	'PRODUCT_CODE'
FIELD_VALUE	Keystone PRODUCT_CODE
EXT_VALUE	QuickBooks Item Ref Id

Tax Authority (QuickBooks Tax Item) Translation

Unless a translation record is found for the tax authority in the CCTXTRL table, Keystone will attempt the following default translations before displaying a dialog box asking the user to map the tax authority:

• Keystone Tax Authority <=> QuickBooks Tax Item Name
• Keystone Tax Authority Description <=> QuickBooks Tax Item Name

CCTXTRL fields for QuickBooks Tax Authority Translation
-------------------------------------------------------
XLT_TYPE	'QBI'
TABLE_NAME	'ARTTXAUT'
FIELD_NAME	'TAX_AUTHORITY'
FIELD_VALUE	Keystone TAX_AUTHORITY
EXT_VALUE	QuickBooks Tax Item Ref Id

MAS-90

Keystone Location Transfer Out Parameters

EXPORTTYPE=MAS90
FILE=\\SERVER\MAS90\KSEXPORT.CSV

BusinessWorks Gold

Keystone Location Transfer Out Parameters

EXPORTTYPE=BWG4
FILE=\\SERVER\BWG4\KSEXPORT.CSV

Alkon Spectrum (822-Byte Format)

Keystone Location Transfer Out Parameters

EXPORTTYPE=SPEC822
FILE=\\SERVER\SPEC822\KSEXPORT.TXT

Flexible Configuration

The Spectrum (822-Byte Format) export uses the new export configuration file which can be easily modified in the event future changes are required. The export configuration file is called "tktex_spec822.exf" and is located in the Keystone BIN\RESOURCES folder.

CDS (Binary)

Keystone Location Transfer Out Parameters

EXPORTTYPE=CDS
FILE=\\SERVER\CDSX\KSEXPORT.CDS

CDS (XML)

Keystone Location Transfer Out Parameters

EXPORTTYPE=CDSX
FILE=\\SERVER\CDSX\KSEXPORT.XML

Standard

Keystone Location Transfer Out Parameters

EXPORTTYPE=STD
FILE=\\SERVER\STD\KSEXPORT.CSV

EXF

Keystone Location Transfer Out Parameters

EXPORTTYPE=EXF
EXPORTFILE=<export format file name>  (will be prefixed with "ARTCK_" and suffixed with ".EXF" automatically)
EXPORTMOD=<export format file modifier>  (used when there are minor changes to a standard format within the same export format file)

Note: EXPORTFILE refers to the file that contains the EXF formatting. You will still need to add FILE= to define the output file name and location.

Referencing the same field more than once within an EXF

A given Keystone field can be exported more than once within the same record. As an example, let's say that the INVOICE_NO field needs to be output twice:

INVOICE_NO=1,8,INTEGER
INVOICE_NO(1)=2,8,INTEGER

You can use the suffix (2), (3), (4), etc. to output the same field as many times as needed.

Keywords

Ticket Export EXF End of Day