Knowledgebase
[QODBC-Desktop] How to create Checks using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of Creating Checks

Note: You can only insert into the CheckItemLine or CheckExpenseLine table. The Check table is a header table and cannot be inserted into.

Create One Check with Three Item Lines and one Expense Line

This creates one Check with three Item Lines and one Expense Line. Note the FQSaveToCache field, set to True except on the last line.

First Item Line

INSERT INTO "CheckItemLine" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Memo 1', 0, '250000-933272656', 'Building permit', 100.00, 100.00, '250000-933272658', 'Billable', 1)

Second item Line

INSERT INTO "CheckItemLine" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Memo 2', 0, '250000-933272656', 'Building permit', 200.00, 200.00, '250000-933272658', 'Billable', 1)

Third Item Line

INSERT INTO "CheckItemLine" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted", "ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Memo 3', 0, '250000-933272656', 'Building permit', 300.00, 300.00, '250000-933272658', 'Billable', 1)

One Expense Line (Last Line)

INSERT INTO "CheckExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "ExpenseLineBillableStatus", "FQSaveToCache") VALUES ('320000-933270542', 600.00, 'Memo 1', '250000-933272658', 'Billable', 0)

Create One Check with Three Item Lines and one Expense Line with All Shared Header Information

This creates one Check with three Item Lines and one Expense Line. Note the FQSaveToCache field, set to True, uses Check to save Check info and save.

First Item Line

INSERT INTO "CheckItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit', 100.00, 100.00, '250000-933272658', 'Billable', 1)

Second Item Line

INSERT INTO "CheckItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit', 200.00, 200.00, '250000-933272658', 'Billable', 1)

Third Item Line

INSERT INTO "CheckItemLine" ("ItemLineItemRefListID", "ItemLineDesc", "ItemLineCost", "ItemLineAmount", "ItemLineCustomerRefListID", "ItemLineBillableStatus", "FQSaveToCache") VALUES ('250000-933272656', 'Building permit', 300.00, 300.00, '250000-933272658', 'Billable', 1)

One Expense Line

INSERT INTO "CheckExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "ExpenseLineCustomerRefListID", "ExpenseLineBillableStatus", "FQSaveToCache") VALUES ('320000-933270542', 600.00, 'Expense Memo 1', '250000-933272658', 'Billable', 1)

Shared Header Information

INSERT INTO "Check" ("AccountRefListID", "PayeeEntityRefListID", "TxnDate", "RefNumber", "Memo", "IsToBePrinted") VALUES ('20000-933270541', '300000-933272659', {d'2002-10-01'}, '1', 'Check Memo 1', 0)

 

An Example of Apply Check Payments or Credits to Bills

Related Data Description

PayeeEntityRefListID = 'C0000-933272656'     (Required)

BankAccountRefListID = '20000-933270541'     (Required)

IsToBePrinted = 1     (Either this one or RefNumber is required)

AppliedToTxnTxnID = '2F78-1071505657','976-933373192','A2D-933373874'     (Find from Bill Table)

AppliedToTxnPaymentAmount = 200.75     (Necessary)

AppliedToTxnSetCreditCreditTxnID = '5522-1197768491'      (Find from VendorCredit Table)

AppliedToTxnSetCreditAppliedAmount = 10.00

TxnDate = 2007-12-15

Apply Check Payments to Bills

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, BankAccountRefListID, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, FQSaveToCache) Values ('C0000-933272656', '20000-933270541', 1, '2F78-1071505657', 200.75, {d'2007-12-15'}, 0)

Apply Credits to Bills

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, BankAccountRefListID, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnSetCreditCreditTxnID, AppliedToTxnSetCreditAppliedAmount, TxnDate,FQSaveToCache) Values ('C0000-933272656', '20000-933270541', 1, '2F78-1071505657', 200.75, '5522-1197768491', 10.00, {d'2007-12-15'}, 0)

Note: If you are adding a check the payee must use the same currency as the source bank account. If you don't, you get an error about the currency not being the same as the base currency but it isn't clear that the message is about the payee and not the account. This message is coming back from QuickBooks qbXML SDK which doesn't handle this very well (in the user interface it will let you do it and just change the bank account to one that is in the correct currency - without telling you).

(152 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).