Knowledgebase: Tutorials
[QODBC-Desktop] How to create Invoices using QRemote from SQL Server
Posted by Jack - QODBC Support on 05 June 2015 09:45 AM

Examples of Creating Invoice using QRemote from SQL Server

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with 3 lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-2000',200.00000,200.00,'20000-999022286',1)

Note: QRemote is linked server name you need to change it with your linked server name.

If you don't setup linked server in SQL Server. Please refer below mentioned link for creating the linked server.

For 32-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote

For 64-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote

Second Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-3000',200.00000,200.00,'20000-999022286',1)

Third Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-4000',200.00000,200.00,'20000-999022286',1)

Header Information

INSERT INTO QRemote...InvoiceLine(CustomerRefListID,ARAccountRefListID,TxnDate,RefNumber,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState, BillAddressPostalCode,BillAddressCountry,IsPending,TermsRefListID,DueDate,ShipDate,ItemSalesTaxRefListID,Memo,IsToBePrinted,CustomerSalesTaxCodeRefListID) VALUES('160000-933272658','40000-933270541', {d'2014-10-01'}, '1', 'Brad Lamb', '1921 AppleseedLane', 'Bayshore', 'CA', '94326', 'USA', 0, '20000-933272658', {d'2014-10-31'}, {d'2014-10-01'}, '2E0000-933272656', 'Memo Test', 0,'10000-999022286')

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step1.png

Results in QuickBooks

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step2.png

 

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '240000-933272656' and found like this:

     Select ListId, FullName from QRemote...Item where FullName like 'F%'

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step3.png

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'N%'

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step4.png

CustomerRefListID

CustomerRefListID is '160000-933272658' and found like this:

     Select ListId, FullName from QRemote...Customer where FullName like 'Aber%'

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step5.png

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, FullName from QRemote...Account where Name like 'Account%'

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step6.png

ItemSalesTaxRefListID

ItemSalesTaxRefListID is '2E0000-933272656' and found like this:

     Select ListId, Name from QRemote...ItemSalesTax where Name like 'San%'

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step7.png

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'T%'

http://support.flexquarters.com/esupport/newimages/SQLInvoice/step8.png

Example of adding InvoiceLine items to an existing Invoice

You can add line to existing Invoice using SQL Linked Server without any issue. Please refer below sample query for the same:

Insert into QRemote..InvoiceLine (TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('1B308-1513325705','320000-1071525597', '11440DECA', 5,5)

OR

Insert openquery(QRemote, 'select TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnID=''0''') values ('1B308-1513325705','320000-1071525597', '88880DECA', 5,5)

Note: In the INSERT statement, a where should be TxnID=''0''. The TxnID=''0'' predicate is used to avoid retrieving data from the remote server, which can result in slower performance.

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