Knowledgebase: Syntax
[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 paid

Note: The following example uses QuickBooks 2006 USA Sample Rock Castle Construction company file.

TO CREATE THE NEW CUSTOMER

Query Run in QODBC

insert into customer

(name, firstname, lastname, companyName, contact, accountNumber, BillAddressAddr1, BillAddressAddr2,
BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email,
TermsRefListID, SalesTaxCodeRefListID, PreferredPaymentMethodRefFullName, CreditCardInfoCreditCardNumber,
CreditCardInfoExpirationMonth, CreditCardInfoExpirationYear, CreditCardInfoNameOnCard,
CreditCardInfoCreditCardAddress, CreditCardInfoCreditCardPostalCode, JobStatus,
JobStartDate, JobEndDate, JobDesc)

values

('Joe Mama Inc 2', 'Joe', 'Mama', 'Joe Mama Inc 2', 'Joe Mama', '343453', 'Joe Mama Inc', '123 Main St.',
'Suite 456', 'Chicago', 'IL', '60602', '555-555-5555', '666-666-6666', 'joe@mama.com',
'10000-933272658', '10000-999022286', 'Visa', '4111111111111111', 06, 2022, 'Joe Mama',
'123 Main St. Suite 450', '60707', 'None', {d'2017-05-11'}, null, '$39.95 Monthly Subscription - Bill CC Monthly'
)

Result in QODBC

Note:  VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

TO DETERMINE THE ListID OF THE NEW CUSTOMER

Query 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 ====> 800000E6-1639565205

 

TO CREATE A NEW JOB RECORD UNDER A CUSTOMER 

Query Run in QODBC

INSERT INTO Customer (Name, ParentRefFullName, JobDesc, JobTypeRefFullName) VALUES ('JobName-001',  'Roof Worx', 'Description of Job','Commercial')

This should create a new job record under the customer  Roof Worx. 

 

TO CREATE THE INVOICE

Query in QODBC

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('800000E6-1639565205', 'QODBCcip', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0)

Result in QODBC

TO LOCATE THE TXNID OF THE INVOICE

Query in QODBC

     Here we also use the SP_LASTINSERTID sp_command to get the new created TxnID of InvoiceLine.

     SP_LASTINSERTID invoiceline ====> 24309-1639565563

     Or you could the query below to get the TxnID: 

     select TxnID, RefNumber from InvoiceLine where CustomerRefListID = '800000E6-1639565205' 

Result in QODBC

SP_LASTINSERTID Method

Normal Query Method

TO RECEIVE THE PAYMENT

Query in QODBC

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount,
AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, Memo)
Values ('800000E6-1639565205', '80000-933270541', 200.00, '24309-1639565563', 200.00,
{d'2007-12-15'}, 'Payment for Invoice #QODBCcip')

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.
DepositToAccountRefListID - This is the ListID of your Undeposited Funds Account from the Account table.
TotalAmount - The Payment Total amount
AppliedToTxnTxnID - This is the TxnID of the Invoice to pay the Invoice table.
AppliedToTxnPaymentAmount - The Payment Amount to apply .... normally the same amount as TotalAmount.
TxnDate - The date of the payment transaction in {d'YYYY-MM-DD'} format.
Memo - Any text to identify the payment.

     When you set up your Company file you had to nominate an Account for Undeposited Funds:

(120 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).