RSS Feed
Knowledgebase : Tutorials > Syntax
AN EXAMPLE OF USING LIKE IN QUERY NOTE: There's no problem with LIKE anyvalue. Here we are running the following query as an example to show how a like "i%" works to find "Invoice" type sales: SELECT Sales.RefNumber,Sales.BillAddressAddr1,Sales.Typ...
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 fol...
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 (na...
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 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...
EMPLOYEEEARNING INSERT CREATE A NEW EMPLOYEE AND EMPLOYEEEARNING LINE INSERT INTO EmployeeEarning (FirstName, MiddleName, LastName, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate, FQSaveToCache) VALUES ('Tom','T.','QODBC', '10000-9...
AN EXAMPLE OF INVOICE ITEM GROUPS CREATE AN ITEMGROUP WITH THREE LINES Note: Let's start by creating 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 "IT...
EXAMPLE OF CREATE AN ITEMSERVICE ITEM NOTE: This is an example of how to add a new ITEMSERVICE Item. You can also use the stored procedure "sp_lastinsertid ItemService" immediately after the insert statement to retrieve a record set that has the ListID ...
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...
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 that are applied to credit memos. To do this, open the Create Credit Memos/Refunds dialog box using the...
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 recordset that has the ListID of th...
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",...
AN EXAMPLE OF CREATING A TIMETRACKING RECORD NOTE: The following INSERT commands work on the QuickBooks 2005 or 2006 USA Edition Sample Rock Castle Construction Company file using QODBC v6.00.00.143 or later. Modify the actual data elements to match you...
AN EXAMPLE OF RECEIVING ITEMS AGAINST A PURCHASE ORDER NOTE: To understand the process, here we will do a simple one line purchase order example. CREATE THE PURCHASE ORDER QUERY IN QODBC The following SQL statement will create a new purchase order...
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 2 credit lines and 2 debit lines. Note the FQSaveToCache field, set to True except on the last line...
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 to save the data to the chi...
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 to save the data to the c...
ADD A NON-INVENTORY ITEM WITH THE "THIS ITEM IS USED IN ASSEMBLIES OR IS PURCHASED FOR A SPECIFIC CUSTOMER:JOB" FIELD CHECKED INSTRUCTIONS FOR THIS OPTION Actually there's not a column for "THIS ITEM IS USED IN ASSEMBLIES OR IS PURCHASED FOR A SPECIFI...
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 into. CREATE ONE CHECK WITH THREE ITEM LINES AND ONE EXPENSE LINE This creates one Check...
AN EXAMPLE OF CONVERTING AN ESTIMATE TO A SALES ORDER CREATE AN ESTIMATE WITH ONE LINE QUERY IN QODBC The following SQL statement will create a new estimate: INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListI...
AN EXAMPLE OF CREATING AN OTHERCHARGE ITEM NOTE: To create a standard ItemOtherCharge item you can use an INSERT statement like follows: INSERT INTO ItemOtherCharge ("Name", "IsActive", "SalesORPurchaseDesc", "SalesTaxCodeRefListID", "SalesOrPurchas...
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 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 to save the data ...
AN EXAMPLE OF IMPORTING A BILL WITH ITEM LINE AND EXPENSE LINE NOTE: It's simple, but you need to first create either the Expense or Item lines first and then add the Item or Expense lines afterward like below. NOTE: The primary rule is to save the da...
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 to save the data to the child ...
TWO EXAMPLES OF WRITING DEPOSITS INSTRUCTIONS NOTE: Deposit inserts are done only to the DEPOSITLINE table. The Deposit table is a header table and cannot be inserted into without cached DepositLine transactions. TROUBLESHOOTING NOTE If you encoun...
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 Estima...
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.CustomerRefL...
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...
AN EXAMPLE OF CHANGE 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", "InvoiceL...
AN EXAMPLE OF DELETING AN INVOICELINE CREATE AN INVOICE WITH THREE LINES FIRST LINE INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLine...
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 CREATING A VENDOR (SUPPLIER) NOTE: If you need to create a supplier, you can use a format similar to the below. FOR USA EDITIONS OF QUICKBOOKS INSERT INTO VENDOR (IsActive, Name, CompanyName, VendorAddressAddr1, VendorAddressAddr2, Vendo...
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 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...
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 TxnNumb...
NOTE: The primary rule is to save the data to the child record first. The child record for each parent/child pair has all of the data required by the parent record included in it. Below are some SQL Commends examples of various types of updates. CREA...
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       With...
GENERAL CONNECTION STRING INSTRUCTIONS The most difficult 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 install...
NOTE: OpenQuery requires a result set to be returned, but UPDATE, DELETE and INSERT statements that are 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 NA...
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...
HOW TO PRINT INVOICES WITH DIFFERENT STATUS USING QODBC NOTE: There are two columns in table Invoice called "IsPending" and "IsPaid", you could just give them the specified value to get the different status of Invoices. To print pending, unpaid and pa...
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 two fields named "IsManuallyClosed" and "PurchaseOrderL...
How to Pay an Existing Invoice HOW TO APPLY A PAYMENT TO THE INVOICE INSTRUCTIONS This article is about how to receive a payment and apply it to an existing invoice. But if you want to keep the payment as a customer credit, please refer: How to Recei...
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...
Find the transaction related Class information HOW TO LINK CLASS INFORMATION FROM TRANSACTION AND BILL INSTRUCTIONS Table Transaction in QODBC shows summary information of all transaction. But it doesn't contain Class information. You could use TxnID ...
HOW TO ADD A NEW LINE ITEM TO AN EXISTING PURCHASE ORDER DESCRIPTION: We have already known how to create new PurchaseOderLine, Refer How to create a Purchase Order using QODBC [http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Ar...
There are multiple ways to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for selected/multiple tables. For a Single Execution follow below steps Click on the VB DEMO icon to launch the Visual Basic Demo program supplied with QODBC. From the...
PROBLEM I need to update 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 reflect wha...
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 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 am now getting the following error. SQL Statement: INSERT INTO InvoiceLine (TxnID,RefNumber...
INSTRUCTION: When you want to insert 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 i...
PROBLEM DESCRIPTION: How can I use Case When statement query in QODBC SOLUTION: You can use Case When statement query in QODBC as below: In SELECT Statement, Simple CASE Statement is only used to check whether the value is equal. Below is an exam...
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 DESCRI...
HOW CAN I FIND SOURCE OF SP_REPORT PROBLEM DESCRIPTION 1: Is there a way to see the SQL that QuickBooks uses to generate their reports? Also, Which tool do they use to generate reports? PROBLEM DESCRIPTION 2: I need to access information that is u...
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 am able to get records from ProfitA...
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 addr...
PROBLEM DESCRIPTION: We modified an invoice in QuickBooks and we are trying to get the data out in Excel, but QODBC gives us the old data still. Why? SOLUTIONS: QODBC failed to return full records is usually caused by either corruption of QODBC opti...
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 listed in your report help guide aren't accessible to m...
TROUBLESHOOTING - INTERNAL ERROR WHEN PROCESSING THE QBXML REQUEST PROBLEM DESCRIPTION: I have QuickBooks 2014 version & I am using QODBC latest version. I have a problem with querying customer table using QODBC. QODBC driver consistently gets stuck o...
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 - GETTING [QODBC] NOT SUPPORTED ERROR WHEN TRY TO MODIFY TXNDELETED TABLE PROBLEM DESCRIPTION: I am trying to modify the TxnDeleted table from QuickBooks in QODBC but getting "[QODBC] Not Supported" error. SOLUTIONS: The TxnDeleted t...
TROUBLESHOOTING - HOW TO CREATE 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 sending...
TROUBLESHOOTING - INVOICE REFNUMBER FAQ PROBLEM DESCRIPTION: I have invoice number (RefNumber) from my own table outside of QuickBooks. But if QuickBooks user creates invoices directly in QuickBooks then QuickBooks keeps new incrementing invoices autom...
TROUBLESHOOTING - HOW TO USE DATE() AND DATEADD() FUNCTION IN QODBC PROBLEM DESCRIPTION: I want to write some select statements on both InvoiceLine and SalesReceiptLine that returns all records dated WITHIN the past 30 days, relative to whatever TODAY ...
PROBLEM DESCRIPTION: How to use parameter 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 = '19...
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...
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 Order...
TROUBLESHOOTING - HOW TO GET BANK RECONCILIATION DETAILS USING QODBC PROBLEM DESCRIPTION: How to get Bank Reconciliation details using QODBC I've not had success identifying any field/flag that identifies the Bank Reconciled status. Is this informatio...
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 - ERROR WHILE INSERTING BILL PROBLEM DESCRIPTION 1: I am following the below steps and getting the error. 1.First, we have inserted the records in Bill & BillExpenseLine table and records got inserted successfully. 2.Second, we are in...
TROUBLESHOOTING - GETTING QODBC NOT SUPPORTED ERROR WHILE INSERTING INVOICE PROBLEM DESCRIPTION: I am trying to insert Invoice, But I am getting below error: [QODBC] Not supported (#10003) I am using below SQL statements: INSERT INTO "InvoiceLine...
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 - 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 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...
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 application & "QuickBooks...
TROUBLESHOOTING - HOW TO GET VBA TO EXECUTE TWO STATEMENTS AT THE SAME TIME PROBLEM DESCRIPTION: We are using a Visual Basic app which works with an SQL Server database to write data to QB through QODBC. We are executing two SQL statements as described...
TROUBLESHOOTING - QODBC CONNECTION TO TALEND OPEN STUDIO PROBLEM DESCRIPTION: QuickBooks Enterprise comes with a tool called QODBC that enables connecting to a QB database to read and write data. I have installed it, and I can successfully see QB data ...
TROUBLESHOOTING - QODBC DRIVER DROPPING A FEW TRANSACTIONS ON INSERT TO INVOICELINES PROBLEM DESCRIPTION: We seem to be having an issue with an insert into the InvoiceLines table dropping a few transactions. We need to create about 1000 invoices per m...
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 - HOW TO CHANGE STATUS OF INVOICE IN QUICKBOOKS ONLINE PROBLEM DESCRIPTION: I had inserted into table ReceivePaymentLine, but it not changes status to "Paid"? How do I do it? I using InvoiceLine for insert Item sales, and ReceivePaymen...
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...
PROBLEM DESCRIPTION: How can I add 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 just make sure there is at least one line wi...
PROBLEM DESCRIPTION: How to insert employee time information from MS Excel time spreadsheet into QuickBooks using QODBC SOLUTION: You cannot perform insert/update operation directly in MS Excel because Excel is just exporting table in the spreadshee...
TROUBLESHOOTING - [QODBC] ERROR 3000 - THE GIVEN OBJECT ID "" IN THE FIELD "LIST ID" IS INVALID PROBLEM DESCRIPTION: I get a very generic error when I try and use QODBC to create an invoice. I am using below query: INSERT INTO InvoiceLine ("Templa...
TROUBLESHOOTING - SELECT STATEMENT IS NOT WORKING PROBLEM DESCRIPTION: I am evaluating the product. When I did 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] INVALID OPERAND FOR OPERATOR: PROBLEM DESCRIPTION: I am getting the error "Invalid operand for operator: " while trying to insert an invoice line item: INSERT INTO InvoiceLine ( CustomerRefListID, T...
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 to...
TROUBLESHOOTING - HOW DO I LIST THE SUBTOTAL OF INVOICES WITHIN DATE RANGE PROBLEM DESCRIPTION: I would like to do is list find the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer, wi...
AN EXAMPLE OF PURCHASEORDER ITEM GROUPS CREATE AN ITEMGROUP WITH THREE LINES Note: Let's start by creating 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 IN...
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 using the following stored procedure "s...
TROUBLESHOOTING - HOW CAN I CHANGE THE PRICING AND QUANTITY OF ITEMS IN AN ITEMGROUP? PROBLEM DESCRIPTION: How can I change the price of each individual item in a group? Is it possible? If yes please give me an example. SOLUTION: The price of an it...
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 GET DAILY BILL PAYMENT INFORMATION? 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 ...
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...
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 start working with QODBC to get ...
TROUBLESHOOTING - HOW DO I VOID AN INVOICE? PROBLEM DESCRIPTION: How do I void an Invoice? And How do I query for invoices that have been voided? I don't see any kind of indicator in the Invoice table. SOLUTION: You can use SP_VOID just like you wo...
TROUBLESHOOTING - HOW TO COMPUTE SUM WITHOUT COMPUTE CLAUSE PROBLEM DESCRIPTION: The following select gives me the right data using DISTINCT, but then how do I get a sum of it, which is what I need? select distinct billtopayduedate, billtopayrefnumb...
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...
HOW TO IMPORT DATA TO QUICKBOOKS THROUGH CSV USING QODBC NOTE: QODBC does not support direct import, But you can write VBA code which reads a CSV / Excel file & generates insert statements. There are two ways to Import data to QuickBooks through CSV u...
TROUBLESHOOTING - CORRELATED SUBQUERIES IN UPDATE STATEMENT PROBLEM DESCRIPTION: I would like to update the salesrep field in my existing invoices. I would like to use the salesrep value that is stored in the Customer file. I would normally do this in...
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 - HOW CAN I GET LAST ITEMS SOLD TO CUSTOMER DETAILS PROBLEM DESCRIPTION: I was able to create a report using a customer set with the customer as the parent and InvoiceLine as the child giving me items ordered in the past. My problem ...
TROUBLESHOOTING - HOW DO I GET TABLE RECORD COUNT 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. So ...
TROUBLESHOOTING - ODBC DRIVER DOESN'T SUPPORT THIS STATEMENT. UPDATING EXISTING RECORD PROBLEM DESCRIPTION: I'm trying to programmatically make Inventory Items In-Active after a sale or Active after voiding a sale. I would have thought that the foll...
TROUBLESHOOTING - HOW TO USE BETWEEN IN QODBC PROBLEM DESCRIPTION: I need to use Between, in range of dates to return new customer in one period, I try this: SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12-19'...
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 upgrading to latest QuickBooks and QODBC require any code ...
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 that it returns contains...
TROUBLESHOOTING - GETTING ERROR "INDEX WAS OUTSIDE THE BOUNDS OF THE ARRAY." WHEN USING IMPORT/EXPORT TOOL IN SQL SERVER PROBLEM DESCRIPTION 1: I just recently installed QODBC to try to get data out of a QuickBooks company file but I get this error: "...
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 - TRYING TO UPDATE ITEMINVENTORYASSEMBLYLINE TABLE PROBLEM DESCRIPTION: I am using MS Access connected to QuickBooks via QODBC. I am using queries in MS Access to load/synchronize Inventory data from my master inventory list, which is ...
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...
SAMPLE CODE FOR C++ WITH QODBC #include #include #include #include #include #include #include #include using namespace std; void ConvertTimeStampStructToString(TIMEST...
TROUBLESHOOTING - QODBC RECORD SET ISSUE PROBLEM DESCRIPTION: I am using below code it is returning records & giving me the correct result. But If I change query from "SELECT Name FROM customer" to "SELECT * FROM customer" it does not return any recor...
TROUBLESHOOTING - GETTING INCOMPLETE DATA WHEN RUNNING PROFITANDLOSSBUDGETVSACTUAL REPORT PROBLEM DESCRIPTION: Good Day To You. Using This Sp_report And Having Some Problem With The Column Layout. Command Text Is As Follows: Sp_report Profitandlossbud...
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...
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 ...