We frequently get requests for a detailed map of what needs to go where in our database to move information to and from QuickBooks. While the help file covers some of this it’s by no means comprehensive – mainly because so many of the relationships are duplicated throughout.
The major one is ensuring line-items are correctly mapped up to the parent records.
If you are trying to create, say, an invoice. Like QuickBooks, you’ll need at least one line item. So in the OpenSync database you’ll first populate a row in the Invoice table (make sure to take note of the TxnID value you use for that row as you’ll need it to link your line items). Once completed you’ll want to create at least one row in the InvoiceLineDetail table. Once you finish populating that row you’ll come to a IDKEY column at the very end. This is where you place that TxnID from the parent table – in this case Invoice. You’ll need to include that value in the IDKEY column for every line item you create for this transaction.
It’s just that simple. Note: The TxnID value in the InvoiceLineDetail table is generally unused for our purposes.
And as I said earlier, this relationship is replicated throughout the database SalesReceipt.TxnID –< SalesReceiptLineDetail.IDKEY, CreditMemo.TxnID -< CreditMemoLineDetail.IDKEY, and so on and so forth. For those who are interested in a high-level, comprehensive view, the best resource is Intuit's own on-screen reference. Since our database schema was modeled directly against the QuickBooks API, it can serve as a worthy supplement to our own documentation. Here's a link: https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html
The topic ‘OpenSync Database Schema’ is closed to new replies.