RSS Feed
Knowledgebase : Tutorials > SQL Language Support (Syntax)
     

[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...
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 Ca...
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...