[QODBC-Desktop] How to Create a new Customer, Job, Invoice them and Mark the Invoice as paid
Posted by brad waddell on 12 March 2009 05:21 PM
|
|
How to Create a new Customer, Job, Invoice them, and Mark the Invoice as paidNote: The following example uses QuickBooks 2006 USA Sample Rock Castle Construction company file. TO CREATE THE NEW CUSTOMERQuery Run in QODBC insert into customer (name, first name, last name, company name, contact, accountNumber, BillAddressAddr1, BillAddressAddr2, values ('Joe Mama Inc 2', 'Joe', 'Mama', 'Joe Mama Inc 2', 'Joe Mama', '343453', 'Joe Mama Inc', '123 Main St.', Result in QODBC Note: QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool. TO DETERMINE THE ListID OF THE NEW CUSTOMERQuery in QODBC Here we use a sp_command named "SP_LASTINSERTID" to get the ListID of the newly created customer. Result in QODBC SP_LASTINSERTID customer ====> 8000007A-1481800232
TO CREATE A NEW JOB RECORD UNDER A CUSTOMERQuery Run in QODBC INSERT INTO Customer (Name, ParentRefFullName, JobDesc, JobTypeRefFullName) VALUES ('JobName-001', 'Joe Mama Inc 2', 'Description of Job','Commercial') This should create a new job record under the customer Joe Mama Inc 2.
TO CREATE THE INVOICEQuery in QODBC INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", Result in QODBC TO LOCATE THE TXNID OF THE INVOICEQuery in QODBC Here we also use the SP_LASTINSERTID sp_command to get the newly created TxnID of InvoiceLine. SP_LASTINSERTID invoiceline ====> 24309-1639565563 Or you could use the query below to get the TxnID: select TxnID, RefNumber from InvoiceLine where CustomerRefListID = '8000007A-1481800232' Result in QODBC SP_LASTINSERTID Method Normal Query Method TO RECEIVE THE PAYMENTQuery in QODBC INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, Result in QODBC See also: How to Receive A Payment not be applied to an Invoice ( How to handle Customer credits on account ) for more information on the procedure for receiving the Payment before the Invoice. Related Data Description CustomerRefListID - This is the ListID of the Customer in the Customer table. When you set up your Company file, you had to nominate an Account for Undeposited Funds: | |
|