This month's support incident article comes from a QODBC Pro Read Write Edition user that asked,

"How do I find out what columns are required on table inserts or updates?"

You can use the SP_COLUMNS tablename stored procedure command in VBDemo to get the EXACT table schema rules for the QuickBooks Company file you have open. The last few columns have Queryable, Insertable, Updateable, Required_On_Insert and the format ie:-

sp_columns Invoiceline

You can also use the SP_PRIMARYKEYS tablename stored procedure command in VBDemo to get the EXACT table primary keys for the QuickBooks Company file you have open.

sp_primarykeys Invoiceline

Using a primary key is the fastest method of retrieving rowsets, for example:-

SELECT * FROM InvoiceLine where TxnID='196-1045628533'

One of the most frequently asked questions submitted to our technical staff is,

“How do I join the Invoice table to the InvoiceLine?”

The SQL statement would be:-

SELECT * FROM {OJ Invoice INNER JOIN InvoiceLine ON (Invoice.TxnID = InvoiceLine.TxnID)} where Invoice.TxnID='196-1045628533'

But this is not necessary. In QODBC all the child tables, like InvoiceLine contain all the fields from the Invoice (parent table). Simply do your select using InvoiceLine and reference the same fields you would in the Invoice table. This will increase your performance by many times.

As a general rule of thumb, if you need to reference any field from a table that has the child table name in it, like InvoiceLineItemRefListID, then use the child table (InvoiceLine) otherwise use the parent table (Invoice). This applies to all other parent, child (line and txn) tables.

You can also narrow the rowsets returned by using a date range in transaction child tables to make the execution of queries faster, regardless of the size of your QuickBooks Company file. For example:-

SELECT * FROM InvoiceLine WHERE TimeModified = {ts'2005-06-30 11:54:25.000'}
SELECT * FROM InvoiceLine WHERE TxnDateMacro = 'LastMonth'
SELECT * FROM InvoiceLine WHERE TxnDate > {d'2005-01-01'}
SELECT * FROM InvoiceLine WHERE TimeModified >= {ts'2005-06-01 00:00:00.000'} AND TimeModified <= {ts'2005-07-01 00:00:00.000'}

Back to Top