[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')


Results in QuickBooks



Related Data Location


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

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



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

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



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

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



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

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



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

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



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

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


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)


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))
Not helpful

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