[QODBC-ALL] & [QXL- ALL] SQL LANGUAGE SUPPORT (SYNTAX)
STORED PROCEDURES COMMAND LIST
Applicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL-
Desktop] & [QXL- Online]
STORED PROCEDURES
SP_COLUMNS _table name_
INSTRUCTION: Returns a record...
[QXL-DESKTOP] & [QXL-ONLINE] FUNCTION LIST
QXL Desktop uses the QODBC Desktop driver to query QuickBooks Desktop.
Commands available in QODBC Desktop will also work in the QXL desktop
application.
QXL Online uses the QODBC Online driver to query Quick...
[QXL- DESKTOP] & [QXL- ONLINE] STORED PROCEDURES COMMAND LIST
QXL Desktop uses the QODBC Desktop driver to query QuickBooks Desktop.
Commands available in QODBC Desktop will also work in the QXL desktop
application.
QXL Online uses the QODBC Online dri...
[QODBC ALL] - HOW TO INCREASE THE UPDATE OR DELETE SQL STATEMENT
PERFORMANCE.
PROBLEM DESCRIPTION:
Ran this SQL to move an invoice from one Customer to another:
Update invoice set CustomerRefListID = '1500' where TxnNumber = 8453
It took about 15+ min...
TROUBLESHOOTING - THERE WAS AN ERROR SAVING AN EMPLOYEES LIST. THE
ADDRESS IS INVALID
PROBLEM DESCRIPTION:
We have searched the wiki and internet and tried endlessly to 'Insert
an Employee.'
Below is our latest effort, all to no avail-
Insert into E...
HOW TO USE POWERSHELL WITH QODBC
Download Sample
[http://support.flexquarters.com/esupport/newimages/PowerShell/DisplaySQL.zip]
SAMPLE CODE FOR USING QODBC DSN:
$connectstring = "DSN=QuickBooks Data;"
$sql = "Select TOP 10 Name from Customer"
$conn ...
TROUBLESHOOTING - UPDATE FROM A SELECT STATEMENT NOT WORKING
PROBLEM DESCRIPTION:
I am trying to update records through the select statement. But it is
not working.
UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM (
SELECT ListID, {fn...
TROUBLESHOOTING - GETTING INCOMPLETE DATA WHEN RUNNING THE
PROFITANDLOSSBUDGETVSACTUAL REPORT
PROBLEM DESCRIPTION:
Good Day To You. Using This Sp_report And Having Some Problems With
The Column Layout. Command Text Is As Follows: Sp_report
Profitandlo...
TROUBLESHOOTING - QODBC RECORD SET ISSUE
PROBLEM DESCRIPTION:
I am using the below code. It is returning records & giving me the
correct result. But If I change the query from "SELECT Name FROM
customer" to "SELECT * FROM customer," it does not return...
SAMPLE CODE FOR C++ WITH QODBC
#include
#include
#include
#include
#include
#include
#include
#include
using namespace std;
void ConvertTimeStampStructToString(TIMEST...
TROUBLESHOOTING - HOW DO I SEE BACKORDER ITEMS THAT HAVE BEEN
INVOICED BUT NOT SHIPPED
PROBLEM DESCRIPTION:
Could someone kindly provide me with a sample, or is there a stored
procedure that would allow me to only pull out back ordered invoices
from t...
TROUBLESHOOTING - TRYING TO UPDATE THE ITEMINVENTORYASSEMBLYLINE
TABLE
PROBLEM DESCRIPTION:
All our products are Inventory Assemblies. I am using MS Access
connected to QuickBooks via QODBC. I am using queries in MS Access to
loading/synchronize Inven...
TROUBLESHOOTING - HOW DO I UPDATE CUSTOMER CUSTOM FIELDS
PROBLEM DESCRIPTION:
How do I update Customer custom fields?
SOLUTION:
You can update Customer custom fields using the update query.
For Example:
Update Customer Set CustomFieldContract='QO...
TROUBLESHOOTING - GETTING ERROR "INDEX WAS OUTSIDE THE ARRAY BOUNDS."
WHEN USING THE IMPORT/EXPORT TOOL IN SQL SERVER
PROBLEM DESCRIPTION 1:
I recently installed QODBC to get data out of a QuickBooks company
file, but I get this error: "Index was outs...
TROUBLESHOOTING - HOW DO I REMOVE SUBTOTALS AND BLANK LINES IN
SP_REPORT
PROBLEM DESCRIPTION:
I'm trying to link a general ledger report in Excel. I've created the
sp_report SQL query in Microsoft Query just fine, but the data it
returns contains subt...
TROUBLESHOOTING - UPGRADING TO QUICKBOOKS AND QODBC
PROBLEM DESCRIPTION:
I have a custom program written in Access 2003 communicating with
QuickBooks 2013 using QODBC 13, which works fine. Will upgrade to the
latest QuickBooks and QODBC require any co...
TROUBLESHOOTING - HOW TO USE BETWEEN IN QODBC
PROBLEM DESCRIPTION:
I need to use Between, in a range of dates, to return new customers in
one period. I try this:
SELECT FullName FROM Customer Where TimeCreated Between
{d'2007-12-18'} AND {d'2007-12...
TROUBLESHOOTING - ODBC DRIVER DOESN'T SUPPORT THIS STATEMENT.
UPDATING EXISTING RECORD
PROBLEM DESCRIPTION:
I'm trying to make Inventory Items In-Active after a sale
programmatically or Active after voiding a sale.
I thought the following SQL statem...
TROUBLESHOOTING - HOW DO I GET TABLE RECORD COUNT THE FASTEST WAY
USING QODBC
PROBLEM DESCRIPTION:
What is the fastest way to get the number of records from a table?
SOLUTION:
QODBC would need to scan the whole table to get the record or row
count....
TROUBLESHOOTING - HOW CAN I GET THE LAST ITEMS SOLD TO CUSTOMER
DETAILS
PROBLEM DESCRIPTION:
I created a report using a custom set with the customer as the parent
and InvoiceLine as the child giving me items ordered in the past.
My problem is I only...
TROUBLESHOOTING - HOW TO EXTRACT PRINTED CHECKS INFORMATION USING
QODBC
PROBLEM DESCRIPTION:
We are involved in a new project with our bank involving a 'Positive
Payment' solution that requires I send them a file with the check
summary data each time ...
TROUBLESHOOTING - CORRELATED SUBQUERIES IN THE UPDATE STATEMENT
PROBLEM DESCRIPTION:
I want to update the salesrep field in my existing invoices. I want to
use the salesrep value stored in the Customer file. I would normally
do this in SQL using a cor...
HOW TO IMPORT DATA TO QUICKBOOKS THROUGH CSV USING QODBC
NOTE: QODBC does not support direct import, But you can write VBA code
that reads a CSV / Excel file & generates insert statements.
There are two ways to Import data to QuickBooks through CSV us...
TROUBLESHOOTING - HOW TO STOP ANY QUERY ON VB DEMO APPLICATION
PROBLEM DESCRIPTION:
How to Stop any query on VBDEMO when it takes time.
How to convert the date to QuickBooks date.
I want to insert TxnDate in the ReceivePayment table, but it pops u...
TROUBLESHOOTING - HOW TO COMPUTE SUM WITHOUT COMPUTE CLAUSE
PROBLEM DESCRIPTION:
The following selection gives me the correct data using DISTINCT, but
how do I get a sum of it, which is what I need?
Select distinct billtopayduedate, billtopayrefnumb...
TROUBLESHOOTING - HOW DO I VOID AN INVOICE?
PROBLEM DESCRIPTION:
I don't see any indicator in the Invoice table. How do I void an
Invoice? And How do I query for invoices that have been voided?
SOLUTION:
You can use SP_VOID just like you would use ...
TROUBLESHOOTING - HOW DO I GET TIMEFRAME INFORMATION - SALES BY HOUR
REPORTING USING QODBC
PROBLEM DESCRIPTION:
This is a no technical question but one I need to answer myself before
jumping into QODBC. I'm considering working with QODBC to get the
in...
TROUBLESHOOTING - HOW DO I DELETE ORPHANED INVOICELINE RECORDS?
PROBLEM DESCRIPTION:
The InvoiceLine table contains orphan records (the parent invoice
record no longer exists). I tried:
DELETE FROM Invoice WHERE TxnID = 'xxxx-xxxxxxxxx'
The TxnID is...
PROBLEM DESCRIPTION:
How can I get daily payment bill information? And How can I get daily
Bill Payment information using QODBC, and How can I get these details?
SOLUTION:
You can use the query below to get details about daily Bill Payments.
For Exa...
TROUBLESHOOTING - HOW CAN I GET ALL THE RECORDS FOR A SPECIFIED YEAR
PROBLEM DESCRIPTION:
I need to compute a Year to Date total paid using the Invoice table.
I'm working in MS-Access and need to run the following statement:
SELECT SUM(Subtotal) FRO...
TROUBLESHOOTING - HOW CAN I CHANGE THE PRICING AND QUANTITY OF ITEMS
IN AN ITEMGROUP?
PROBLEM DESCRIPTION:
How can I change the price of each item in a group? Is it possible? If
yes, please give me an example.
SOLUTION:
The ItemInventory, ItemServi...
TROUBLESHOOTING - EXPENSES WRITTEN TO BILLEXPENSELINE ARE NOT SHOWING
AS BILLABLE WHEN VIEWED IN QUICKBOOKS
PROBLEM DESCRIPTION:
The field ExpenseLineBillableStatus contains the status of the
BillExpenseLine and uses the following stored procedure "sp...
AN EXAMPLE OF PURCHASEORDER ITEM GROUPS
CREATE AN ITEMGROUP WITH THREE LINES
Note: Let's create a new item group example with three lines using
QODBC. Note the FQSaveToCache field, set to True except on the last
one.
FIRST LINE
INSERT INTO "ITEMGRO...
TROUBLESHOOTING - HOW DO I LIST THE SUBTOTAL OF INVOICES WITHIN THE
DATE RANGE
PROBLEM DESCRIPTION:
I want to list the subtotal of all Invoices by a customer for not only
that particular customer but all jobs assigned to that customer within
a specifi...
TROUBLESHOOTING - INVOICE LINE RATE IS NOT LOADING INTO QUICKBOOKS
ONLINE.
PROBLEM DESCRIPTION:
The InvoiceLineRate value passed in the insert query to create the
InvoiceLine record is not being loaded into QBO.
Below are the two queries I am using t...
TROUBLESHOOTING - [QODBC] INVALID OPERAND FOR THE OPERATOR:
PROBLEM DESCRIPTION:
I am getting the error "Invalid operand for the operator:
" while trying to insert an invoice line item:
INSERT INTO InvoiceLine ( CustomerRefL...
TROUBLESHOOTING - GETTING UNEXPECTED EXTRA TOKEN ERROR IN DATE FIELD
QUERY
PROBLEM DESCRIPTION:
I am trying to query the Transaction table based on the TxnDate field.
I tried the below code.
OdbcCommand SourceCmd = new OdbcCommand("Select * from Tra...
TROUBLESHOOTING - SELECT STATEMENT IS NOT WORKING.
PROBLEM DESCRIPTION:
I am evaluating the product. When I made this SQL Statement:
Select * from Customer where ListId=557
I was getting a column not found the error. The QBO online
documentation says...
TROUBLESHOOTING - [QODBC] ERROR 3000 - THE GIVEN OBJECT ID "IN THE
FIELD "LIST ID" IS INVALID
PROBLEM DESCRIPTION:
I get a generic error when using QODBC to create an invoice.
I am using the below query:
INSERT INTO InvoiceLine ("TemplateRefListID...
PROBLEM DESCRIPTION:
How to insert employee time information from MS Excel time spreadsheet
into QuickBooks using QODBC
SOLUTION:
You cannot perform insert/update operations directly in MS Excel
because Excel is just exporting tables in the spreadsh...
PROBLEM DESCRIPTION:
How can I add a blank InvoiceLine in QuickBooks using QODBC?
SOLUTION:
You can create an empty Invoice, neither in QODBC nor QuickBooks. But
in QuickBooks, you could leave lines blank and ensure at least one
line with data in your...
TROUBLESHOOTING - HOW TO CONVERT BIT TO INTEGER USING QODBC
PROBLEM DESCRIPTION:
I am trying to get our ODBC extension module interface working with
QODBC. Everything seems to work fine except trying to read SQL_BIT
type fields. It causes the Script BA...
TROUBLESHOOTING - HOW TO CHANGE THE STATUS OF THE INVOICE IN
QUICKBOOKS ONLINE
PROBLEM DESCRIPTION:
I had inserted it into the table ReceivePaymentLine, but it not
changes the status to "Paid"? How do I do it?
I use InvoiceLine to insert Item sales an...
HOW TO GET CONVERSION RATIO INFORMATION FROM
UNITOFMEASURESETRELATEDUNIT TABLE IN QODBC
Conversion ratio information is stored in a table called
UnitOfMeasureSetRelatedUnit.
PROBLEM:
How to get Conversion Ratio information from
UnitOfMeasureSetRelated...
[QODBC-Desktop] Troubleshooting - QODBC Driver dropping a few transactions on insert to InvoiceLines
TROUBLESHOOTING - QODBC DRIVER DROPPING A FEW TRANSACTIONS ON INSERT
TO INVOICELINES
PROBLEM DESCRIPTION:
We seem to have an issue with an insert into the InvoiceLines table,
dropping a few transactions.
We need to create about 1000 invoices per month...
TROUBLESHOOTING - QODBC CONNECTION TO TALEND OPEN STUDIO
NOTE: THE JDBC-ODBC BRIDGE DRIVER WAS REMOVED IN JAVA 8, PREVENTING
ODBC DRIVERS FROM BEING USABLE IN NEWER VERSIONS OF JAVA OR OTHER
JAVA-BASED APPLICATIONS.
Refer: Oracle JDBC-ODBC Bridge
[http...
TROUBLESHOOTING - HOW TO GET VBA TO EXECUTE TWO STATEMENTS AT THE SAME
TIME
PROBLEM DESCRIPTION:
We use a Visual Basic app with an SQL Server database to write data
to QB through QODBC. We are executing two SQL statements as described
in your help. We ...
GENERAL CONNECTION STRING
INSTRUCTIONS
The most difficult part of using QRemote in programming is the
connection string. The simplest form includes a reference to a DSN. A
system DSN called "QuickBooks Data QRemote" for 32-bit applications &
"QuickBook...
TROUBLESHOOTING - HOW TO RECORD THE ISSUE I AM FACING AND SHOW/SHARE
THE SCREEN.
PROBLEM DESCRIPTION:
How to record the issue I am facing and show/share the screen.
SOLUTIONS:
Please follow below steps for recording the issue you are facing.
1) Dow...
TROUBLESHOOTING - HOW TO LOCATE DATA FOUND IN QUICKBOOKS BY TABLE AND
FIELD NAME INSIDE QODBC
PROBLEM DESCRIPTION:
I have a question about How to locate data found in QuickBooks by
table and field name inside the QODBC system.
SOLUTIONS:
The fastest ...
TROUBLESHOOTING - HOW TO GIVE REFUND TO EXISTING CREDITMEMO USING
QODBC
PROBLEM DESCRIPTION:
How can I apply a refund check to an existing credit memo using QODBC?
In QuickBooks, I can refund by opening the Create Credit Memos/Refunds
dialogue box usin...
TROUBLESHOOTING - GETTING QODBC NOT SUPPORTED ERROR WHILE INSERTING
INVOICE
PROBLEM DESCRIPTION:
I am trying to insert an Invoice, But I am getting the below error:
[QODBC] Not supported (#10003)
I am using below SQL statements:
INSERT INTO "Invo...
TROUBLESHOOTING - ERROR WHILE INSERTING BILL
PROBLEM DESCRIPTION 1:
I am following the below steps and getting the error.
1. First, we inserted the records in Bill & BillExpenseLine table, and
the papers got inserted successfully.
2. Second, we are i...
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 ...
TROUBLESHOOTING - HOW TO GET BANK RECONCILIATION DETAILS USING QODBC
PROBLEM DESCRIPTION:
How to get Bank Reconciliation details using QODBC
I've not successfully identified any field/flag identifying the bank
reconciliation status. Is this informatio...
TROUBLESHOOTING - QREMOTE DOES NOT CONSIDER FQSAVETOCACHE WITH WORKING
WITH ODBCCOMMAND & PARAMETERS
PROBLEM DESCRIPTION:
QRemote Does not consider FQSaveToCache with working with OdbcCommand
& Parameters.
I have an application that creates Sales Orde...
TROUBLESHOOTING - GETTING MULTIPLE-STEP OLE DB OPERATION GENERATED
ERRORS IN SQL SERVER
PROBLEM DESCRIPTION:
Trying to insert into SalesOrderLine and getting a 'Multiple-step'
error.
INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID,
TemplateR...
PROBLEM DESCRIPTION:
How to use parameters in OPENQUERY
SOLUTIONS:
OPENQUERY does not accept variables for its arguments. You need to
Pass Basic Values as below example:
SELECT QUERY:
DECLARE @TSQL VARCHAR(8000), @ID VARCHAR(25)
SELECT @ID = '1...
TROUBLESHOOTING - HOW TO SELECT A RECORD WHEN THE VALUE IS NULL
PROBLEM DESCRIPTION:
I am trying to run a report that returns ItemInventory where
LastReceived is NULL. I have tried in vain to accomplish this. What
syntax do you use to select a blank/nu...
TROUBLESHOOTING - HOW TO USE DATE() AND DATEADD() FUNCTION IN QODBC
PROBLEM DESCRIPTION:
I want to write some select statements on InvoiceLine and
SalesReceiptLine that return all records dated WITHIN the past 30 days
relative to whatever TODAY is. I'm...
TROUBLESHOOTING - INVOICE REFNUMBER FAQ
PROBLEM DESCRIPTION:
I have the invoice number (RefNumber) from my table outside
QuickBooks. But if a QuickBooks user creates invoices directly in
QuickBooks, then QuickBooks keeps new incrementing invoices
autom...
TROUBLESHOOTING - HOW TO CREATE A BLANK INVOICE IN QUICKBOOKS ONLINE
USING QODBC
PROBLEM DESCRIPTION:
How do I create a note (or a blank line) on the invoice? If I do it
the same way that I do it using QODBC for QuickBooks, I get an error :
Error send...
TROUBLESHOOTING - GETTING [QODBC] NOT SUPPORTED ERROR WHEN TRYING TO
MODIFY THE TXNDELETED TABLE
PROBLEM DESCRIPTION:
I am trying to modify the TxnDeleted table from QuickBooks in QODBC
but getting the "[QODBC] Not Supported" error.
SOLUTIONS:
The T...
TROUBLESHOOTING - MS ACCESS CRASHES HARD ON DATE QUERY
PROBLEM DESCRIPTION:
I've linked all the QODBC tables into an Access 2010 DB. I have
created table subset queries on the original tables and would like to
do selects based on date ranges - other thi...
TROUBLESHOOTING - INTERNAL ERROR WHEN PROCESSING THE QBXML REQUEST
PROBLEM DESCRIPTION:
I have QuickBooks 2014 version & I am using QODBC's latest version. I
have a problem with querying the customer table using QODBC.
QODBC driver consistently gets s...
TROUBLESHOOTING - UNABLE TO FIND TABLE QBADVANCEDREPORTGROUP.
PROBLEM DESCRIPTION 1:
1. I'm attempting to utilize your ODBC driver for reporting through
Excel from QuickBooks Enterprise. The tables in your report help guide
aren't accessible to me and a...
PROBLEM DESCRIPTION:
We modified an invoice in QuickBooks and are trying to get the data
out in Excel, but QODBC gives us the old data.
Why?
SOLUTIONS:
QODBC failure to return full records is usually caused by corruption
of the QODBC optimizer file....
PROBLEM DESCRIPTION:
When inserting an invoice via a looping SQL procedure, I have invoice
lines (InvoiceLine table) items that can potentially be assigned to
varying classes. Using the ClassRefListID field, I class the lines
accordingly and do not addre...
TROUBLESHOOTING: SP_REPORT PROFITANDLOSSBUDGETPERFORMANCE IS
RETURNING NO ROWS.
PROBLEM DESCRIPTION 1:
I am running ProfitAndLossBudgetPerformance through MS Query & It is
returning no rows. But using QuickBooks UI, I can get records from the
ProfitAnd...
HOW CAN I FIND A SOURCE OF SP_REPORT
PROBLEM DESCRIPTION 1:
Is there a way to see the SQL QuickBooks uses to generate their
reports?
Also, Which tool do they use to create reports?
PROBLEM DESCRIPTION 2:
I need to access information used in the Qu...
USING QUICKBOOKS DATA WITH VBA
FOR 64 BIT, PLEASE REFER: HOW TO CONFIGURE QODBC TO WORK WITH 64-BIT
MS ACCESS
[HTTP://SUPPORT.FLEXQUARTERS.COM/ESUPPORT/INDEX.PHP?/DEFAULT/KNOWLEDGEBASE/ARTICLE/VIEW/2472/57/MICROSOFT-ACCESS-2003-AND-QODBC].
PROBLEM DESCR...
PROBLEM DESCRIPTION:
How can I use the Case When statement query in QODBC
SOLUTION:
You can use the Case When statement query in QODBC as below:
The Simple CASE Statement is only used in the SELECT Statement to
check whether the value is equal.
B...
INSTRUCTION:
When inserting multiple records into a line item table, you can use
column FQSaveToCache.
Set column FQSaveToCache to 1 in all insert queries except the last
line set to 0. But table TimeTracking is not a line item table; there
is no FQSa...
PROBLEM DESCRIPTION:
How to insert InvoiceLine item into an existing Invoice.
I am trying to run an INSERT statement that I have run before on
version 10, but I am now getting the following error.
SQL Statement: INSERT INTO InvoiceLine (TxnID, RefNum...
PROBLEM DESCRIPTION 1:
I'm working in PHP and using the QODBC Test Tool. I am trying to
Insert value into the Customer table.
But I am getting the below error:
EXPECTED LEXICAL ELEMENT NOT FOUND:
My insert statement is:
Insert into Customer (...
HOW TO INSERT OPENING BALANCE IN CUSTOMER TABLE USING QODBC
PROBLEM DESCRIPTION
Initially, I received the error "[QODBC] Field not allowed in insert"
when I tried to insert data into the Customer table through QODBC. I
was trying to insert customer d...
PROBLEM
I need to update the old terms of a customer in QuickBooks. They used
to be due upon receipt, and we need to move them to net 30. I need to
go back and change all the old due dates to reflect the new terms. How
can I update the DueDate to remembe...
TROUBLESHOOTING: EXPECTED LEXICAL ELEMENT NOT FOUND
PROBLEM DESCRIPTION
When trying to execute a query statement, I get the error message "
EXPECTED LEXICAL ELEMENT NOT FOUND."
SOLUTIONS
It seems to be the issue in the SQL Statement. Please check al...
There are multiple ways to execute SP_OPTIMIZEUPDATESYNC or
SP_OPTIMIZEFULLSYNC for selected/multiple tables.
For a Single Execution, follow the below steps.
Click on "START" and "PROGRAMS" and locate QODBC DRIVER FOR THE
QUICKBOOKS program group. Cl...
HOW TO ADD A NEW LINE ITEM TO AN EXISTING PURCHASE ORDER
DESCRIPTION: We know how to create a new PurchaseOderLine; refer to
How to create a Purchase Order using QODBC
[http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/Vie...
Find the transaction-related Class information
HOW TO LINK CLASS INFORMATION FROM TRANSACTIONS AND BILL
INSTRUCTIONS
Table Transaction in QODBC shows summary information of all
transactions. But it doesn't contain Class information. You could use
the ...
Manually modify your Item Inventory quantity
HOW TO CREATE AN INVENTORY ADJUSTMENT USING QODBC
INSTRUCTIONS
Although QuickBooks automatically adjusts your inventory quantities
after every purchase and sale, sometimes you still need to adjust them
you...
How to Pay an Existing Invoice
HOW TO APPLY A PAYMENT TO THE INVOICE
INSTRUCTIONS
This article is about receiving payment and applying it to an existing
invoice. But if you want to keep the payment as customer credit,
please refer: How to Receive A P...
HOW TO FIND PURCHASE ORDER CLOSED CHECK MARK DATA USING QODBC
INTRODUCTION
There is a closed check mark for a PurchaseOrder record or a given
line item in a Purchase Order. Normally it's related to the fields
"IsManuallyClosed" and "PurchaseOrderLineIsM...
HOW TO PRINT INVOICES WITH DIFFERENT STATUS USING QODBC
NOTE: There are two columns in the invoice table called "IsPending"
and "IsPaid" you could give them the specified value to get the
different statuses of Invoices.
You could try the queries below...
EXAMPLES OF CREATING INVOICE USING QODBC
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 Inv...
NOTE: OpenQuery requires a result set to be returned, but UPDATE,
DELETE, and INSERT statements used with OpenQuery and QODBC do not
return a result set.
WORKAROUND
You can work around this problem in the following two ways:
USE FOUR-PART NAMES
C...
GENERAL CONNECTION STRING
INSTRUCTIONS
The most challenging part of using QODBC in programming is the
connection string. The simplest form includes a reference to a DSN. A
system DSN called "QuickBooks Data" is automatically created when
QODBC is insta...
NOTE: Here, I take " How do I create a multi-line invoice using QODBC
with a blank line between two items for a job" as an example to tell
the detailed steps of creating a multi-line invoice.
STEP 1 - FIND THE TEMPLATE LISTID
PREPARATION
Wit...
NOTE: The primary rule is first to save the data to the child record.
The child record for each parent/child pair has all the data required
by the parent record.
Below are some SQL Commends examples of various types of updates.
CREATING LINES IN INVO...
AN EXAMPLE OF SEEING PENDING UNINVOICED SALES IN THE SALES ORDER TABLE
QUERY SALESORDER TABLE
To do this, you can query the SalesOrder table using a where
IsFullyInvoiced = False clause like below:
SELECT TxnNumber, CustomerRefFullName, TxnDate,
R...
AN EXAMPLE OF CREATING A SALES ORDER
NOTE: This example creates one Sales Order with two order lines. Note
how the FQSaveToCache field is set to True except on the last line.
FIRST LINE
INSERT INTO "SalesOrderLine" ("CustomerRefListID",
"TemplateRef...
INSTRUCTIONS FOR FOREIGN CURRENCY
Foreign currency is supported in versions of QuickBooks that support
it. The stored procedure reports in QODBC "sp_report" shows the
foreign currency type in the "ACCOUNT" column. For example, in this
report :
sp_rep...
AN EXAMPLE OF CREATING A VENDOR (SUPPLIER)
NOTE: If you need to create a supplier, you can use a format similar
to the one below.
FOR USA EDITIONS OF QUICKBOOKS
INSERT INTO VENDOR (IsActive, Name, CompanyName, VendorAddressAddr1,
VendorAddressAddr2, V...
DATE AND TIMESTAMPS FORMAT USED FOR NORMAL
NOTE: If you have any problems with the date format, use this format
for direct SQL calls to our driver: {d'YYYY-MM-DD'}
For Example, {d '2006-01-27'}
EXAMPLES
DATE FORMAT
SELECT * from InvoiceLine WHERE...
AN EXAMPLE OF DELETING AN INVOICELINE
CREATE AN INVOICE WITH THREE LINES
FIRST LINE
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLine...
AN EXAMPLE OF A CHANGE IN THE INCOME ACCOUNT FOR A STOCK ITEM THAT HAS
BEEN INVOICED
CREATE AN INVOICE FOR A STOCK
Here we create an invoice for a stock part called 'Door Frame':
INSERT INTO "InvoiceLine" ("CustomerRefFullName", "RefNumber",
"Inv...
EXAMPLES OF CREATING ITEM GROUPS
CREATE ONE ITEMGROUP WITH NO LINES
INSERT INTO "ITEMGROUP" ("Name", "IsActive", "ItemDesc",
"IsPrintItemsInGroup") VALUES ('TestWOLines', 1, 'Test Item Desc', 1)
CREATE ONE ITEMGROUP WITH THREE LINES
NOTE: This cre...
INNER JOINS AND LEFT JOINS IN QODBC
NOTE: Inner Joins, and Left Joins can be done with the following
syntax (for a right join, reverse the table order and use a left
join).
EXAMPLES
SELECT * FROM {OJ Invoice INNER JOIN Customer ON
(Invoice.CustomerRef...
EXAMPLES OF USING INVOICELINKEDTXN QUERY IN QODBC
INSTRUCTION OF TABLE INVOICELINKEDTXN
Basically, the InvoiceLinkedTxn tables show Payments, Credit Memos,
and Deposit Line Items but don't include reimbursements, transfers
from Sales Orders, or Estim...
TWO EXAMPLES OF WRITING DEPOSITS
INSTRUCTIONS
NOTE: Deposit inserts are done only to the DEPOSITLINE table. The
Deposit table is a header table that cannot be inserted without cached
DepositLine transactions.
TROUBLESHOOTING NOTE
If you encounter ...
AN EXAMPLE OF CREATING CREDIT MEMOS
CREATE ONE CREDITMEMO WITH THREE LINES
NOTE: This creates one CreditMemo with three lines. Note the
FQSaveToCache field, set to True except on the last one.
NOTE: The primary rule is first to save the data to the ...
AN EXAMPLE OF IMPORTING A BILL WITH ITEM LINE AND EXPENSE LINE
NOTE: It's simple, but you must create either the Expense or Item
lines first and then add the Item or Expense lines afterward, like
below.
NOTE: The primary rule is first to save the data...
AN EXAMPLE OF CREATING ESTIMATES FOR USA QUICKBOOKS USERS
NOTE: This creates an Estimate with Three Lines with all shared header
information. Note the FQSaveToCache field, set to True except on the
last line.
NOTE: The primary rule is first to save the...
HOW TO CREATE INVENTORY ADJUSTMENTS USING QODBC
EXAMPLE OF CREATING ONE INVENTORY ADJUSTMENT WITH THREE LINES
This example creates one Inventory Adjustment with three lines. Note
the FQSaveToCache field, set to True except on the last one.
NOTE: You...
AN EXAMPLE OF CREATING AN OTHERCHARGE ITEM
NOTE: VB DEMO IS DEPRECATED.
Please refer to How to use the QODBC Test Tool for testing
[https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/3069/]
NOTE: To create a standard Ite...
AN EXAMPLE OF CONVERTING AN ESTIMATE TO A SALES ORDER
NOTE: VB DEMO IS DEPRECATED.
Please refer to How to use the QODBC Test Tool for testing
[https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/3069/]
CREATE AN ESTIMATE ...
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.
CREATE ONE CHECK WITH THREE ITEM LINES AND ONE EXPENSE LINE
This creates one Check with...
ADD A NON-INVENTORY ITEM WITH THE "THIS ITEM IS USED IN ASSEMBLIES OR
IS PURCHASED FOR A SPECIFIC CUSTOMER: JOB" FIELD CHECKED.
NOTE: VB DEMO IS DEPRECATED.
Please refer to How to use the QODBC Test Tool for testing
[https://support.flexquarters.com/es...
AN EXAMPLE OF CREATING VENDORCREDIT
CREATES ONE VENDORCREDIT WITH THREE LINES
NOTE: This creates one VendorCredit with three lines. Note the
FQSaveToCache field, set to True except on the last one.
Note: The primary rule is first to save the data to...
EXAMPLE OF CREATING BILLS USING BILLITEMLINE TABLE
CREATE ONE BILL WITH THREE LINES
NOTE: This creates one Bill with three lines. Note the FQSaveToCache
field, set to True except on the last line.
NOTE: The primary rule is first to save the data to t...
TWO EXAMPLES OF CREATING JOURNAL ENTRIES
CREATE A JOURNAL ENTRY WITH 2 CREDIT LINES AND 2 DEBIT LINES
NOTE: This example creates one Journal Entry with two credit lines and
two debit lines. Note the FQSaveToCache field, set to True except on
the last ...
AN EXAMPLE OF RECEIVING ITEMS AGAINST A PURCHASE ORDER
NOTE: Here, we will do a simple one-line purchase order example to
understand the process.
CREATE THE PURCHASE ORDER
QUERY IN QODBC
The following SQL statement will create a new purchase order...
AN EXAMPLE OF CREATING A TIMETRACKING RECORD
NOTE: The following INSERT commands work on the QuickBooks 2022 USA
Edition Sample Company file using QODBC v22.00.00.345 or later. Modify
the essential data elements to match your system.
CREATE A SIMPLE T...
AN EXAMPLE OF CREATING A PURCHASEORDER
NOTE: This example creates one PurchaseOrder with three lines. Note
the FQSaveToCache field, set to TRUE (1) except on the last line.
FIRST LINE
INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",...
EXAMPLE OF ADD AN INVENTORY ITEM
NOTE: This is an example of how to add an ItemInventory Item. You can
also use the stored procedure "sp_lastinsertid ItemInventory"
immediately after the insert statement to retrieve a record set with
the ListID of the n...
REFUNDS WITH QODBC
APPLYING A REFUND CHECK TO AN EXISTING CREDIT MEMO
APPLY REFUND CHECK IN QUICKBOOKS
You cannot use QODBC to create refund checks applied to credit memos.
To do this, open the Create Credit Memos/Refunds dialog box using the
QuickBoo...
EXAMPLE OF CREATING SALES RECEIPTS FOR USA QUICKBOOKS USERS
CREATE ONE SALES RECEIPT WITH A SINGLE LINE
NOTE: This creates one SalesReceipt with a single line with all the
billing address details.
QUERY IN QODBC
insert into SALESRECEIPTLINE (Custo...
EXAMPLE OF CREATE AN ITEMSERVICE ITEM
NOTE: VB DEMO IS DEPRECATED.
Please refer to How to use the QODBC Test Tool for testing
[https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/3069/]
NOTE: This is an example of how to ...
AN EXAMPLE OF INVOICE ITEM GROUPS
CREATE AN ITEMGROUP WITH THREE LINES
Note: Let's create a new item group example with three lines using
QODBC. Note the FQSaveToCache field, set to True except on the last
one.
FIRST LINE
INSERT INTO "ITEMGROUPLINE...
EMPLOYEEEARNING INSERT
CREATE A NEW EMPLOYEE AND EMPLOYEEEARNING LINE
INSERT INTO EmployeeEarning (FirstName, MiddleName, LastName,
PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate,
FQSaveToCache) VALUES ('Tom','T.','QODBC', '10000-9...
HOW TO RECEIVE A PAYMENT NOT BE APPLIED TO AN INVOICE ( HOW TO HANDLE
CUSTOMER CREDITS ON ACCOUNT )
NOTE: Below is how to receive a payment from a customer that will not
be applied to an invoice. In other words, it will be a credit on
account, or a depo...
HOW TO CREATE AN ITEM INVENTORY ASSEMBLY USING QODBC
AN EXAMPLE OF CREATING ONE ITEMINVENTORYASSEMBLY WITH TWO LINES
Note the FQSaveToCache field, set to True except on the last one.
FIRST LINE
INSERT INTO "ItemInventoryAssemblyLine" ("Name", "Is...
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
(n...
AN EXAMPLE OF CONVERTING A SALES ORDER INTO AN INVOICE
NOTE: To understand the process for everyone, I will do a simple
one-line estimate example, make it a Sales Order, and then make it
into an Invoice.
TO CREATE THE ESTIMATE
QUERY IN QODBC
The fo...
AN EXAMPLE OF USING LIKE IN QUERY
NOTE: There's no problem with the LIKE any value.
Here we are running the following query as an example to show how
alike "i%" works to find "Invoice" type sales:
SELECT Sales.RefNumber, Sales.BillAddressAddr1,Sale...
Help Desk Software by Kayako