Quickbooks Notes
- Malcolm Mackay
Parameter Script
Adds 7 new Control parameters into Control Parameters under Data Transfer. Controls the information that comes through memo fields into Quickbooks.
Parameter Name | Parameter Valid Values, Default Value and Description |
Export Procedure BCP Parameters | null, null, 'If specified, this parameter string is used on the BCP command which creates the output file in an export procedure. Otherwise the default settings are used. The Server Name place holder is <SERVERNAME> this will be substituted automatically. i.e. -T -S <SERVERNAME> -m 1 -c -t Note: that this setting is not used on all export procedures. |
Quickbooks Description Field in AP | Any or all of the following 2-3 letter codes enclosed in <> and seperated by semi colons: JTD (JobTransaction.Details), JC (Job Code), JD (Job Description), WC (Work Centre), AC (Activity Centre), BNo (Batch No) {i.e <JTD>;<JC>;<JD>;<WC>;<AC>;<BNo>}', '<JTD>;<JC>;<JD>;<WC>;<AC>', 'Decides the format of the Description field in AP Exports for Quickbooks. |
Quickbooks Job Field | {Null},Profit Centre, Job, PC Job Suffix, Work Centre Desc', null, 'Determines what data is output in the Job field in Quickbooks. |
Quickbooks Description Field in AR | Any or all of the following 2-3 letter codes enclosed in <> and seperated by semi colons: JC (Job Code), JD (Job Description), SC (Sales Code), SCD (Sales Code Description), ILD (Invoice Line Description), BNo (Batch No) {i.e <JC>;<JD>;<SC>;<SCD>;<ILD>;<BNo>}', '<JC>;<JD>;<SC>;<SCD>;<ILD>', 'Decides the format of the Description field in AR Exports for Quickbooks. |
Quickbooks Journal Memo Field in AR | Any or all of the following 2-3 letter codes enclosed in <> and seperated by semi colons: JD (Job Description), JIT (Job Invoice Title), JID (Job Invoice Details), BNo (Batch No) {i.e <JD>;<JIT>;<JID>;<BNo>}', '<JD>;<JIT>;<JID>', 'Decides the format of the Journal Memo field in AR Exports for Quickbooks. |
Quickbooks Default Sales Item | A valid Item from Quickbooks',null,'The Item to use when exporting Sales Invoice to Quickbookss where an Item code is not entered in "External Code" in the Workbench Sales Code. |
Quickbooks line incl GST | <Yes, No>', 'No', 'Include GST in the Amount on the Line. |
A default SalesItem parameter is provided to allow a Quickbooks SalesItem to be specified rather than specifying on every Sales Code.
Package of Exports
- wbExportAPCreditQuickbooks_2012
- wbExportAPInvoiceQuickbooks_2012
- wbExportARCreditQuickbooks_2012
- wbExportARInvoiceQuickbooks_2012
- wbExportAdjustmentQuickbooks_2012
- wbExportDisbursementQuickbooks_2012
- wbExportDocketQuickbooks_2012
- wbExportLabourQuickbooks_2012
- wbExportPlantIssueQuickbooks_2012
All exports produce an .iff file (this is Inuit’s own extension for a tab delimited file). The export process uses the bcp command system in SQL server.
Quickbooks iif Import Checks
The Quickbooks iif file import process provides only basic validation checking of the import file. It checks the format of the file, checks that the number and format of columns present agree to the file header, and checks for a balanced transaction. If an account code is missing, the transaction will be treated as unbalanced, if a GST Code is missing, the transaction will be treated as invalid.
The IIF process also checks for duplicate transaction references, (Invoice Numbers for AP and AR) for a particular customer or supplier. If this is found the operator will be prompted whether they want to allow or reject the duplicate number.
The import process will allow for part of a batch to be imported. Error messages will appear when individual transactions are invalid, with a message asking whether to continue to import the rest of the transactions in the file.
Account Numbers
Quickbooks has the option to use Account Numbers for GL Accounts. This needs to be turned on. If this is not turned on then the import is expecting the Quickbooks GL account name to be used on the import (which can be up to 31 characters, the GL field in Workbench Activity setup is only 20)
This is done in Quickbooks in Edit>>Preferences under the Accounting Preferences item.
GL Accounts Created on Import
The IIF import will create a GL Account in Quickbooks on import where it does not already exist. This account will be created as at type Bank.
Good controls need to be put into place in Workbench for the client setting up Activity and Sales Codes to make sure they agree to Quickbooks GL codes.
Supplier & Customer Names
Quickbooks uses the Supplier and Customer name field as the identifier on import. This field is up to 41 characters long.
Good controls need to be put into place in Workbench to ensure that the name in Workbench matches the name in Quickbooks.
Suppliers and Customers Created on Import
The IIF import will create Quickbooks Suppliers and Customers on import where they do not already exist (based on the name).
Good controls need to be put into place in Workbench for ensuring that the new suppliers in Workbench are actually new suppliers and not duplications.
The Batch Export process will continue to check whether all Invoices in an export batch have an AP or AR Account number. It is suggested that
- control parameters be changed to make APAccountNumber mandatory and ARAccountNumber manadatory set to No
- in Security Admin for general users, set No Access to Company APAccount and No Access to Company AR Account to yes
This will force a batch of invoices to be rejected where there is a new Supplier or Customer but they have not been given an AP or AR number in Workbench. This will require an Admin level security person to check the name, check that it is less than 41 characters, add in an AP or AR Account in Workbench as needed and/or manually setup the company in Quickbooks.
Credit Notes (AR & AP)
Credit Notes (negative invoices) need to be imported as a Credit Note. Any allocations to invoice do not occur during the import, however where an ‘Allocate To’ invoice is chosen in Workbench, that invoice number will appear in the Memo field of the Credit Note in Quickbooks.
Classes
For Quickbooks versions that use Classes, the Profit Centre in Workbench will be automatically created as a Class when the transaction is imported.