Problem Description:

What does 'Red text' in the QODBC driver dialogs mean

Solutions: 

Whenever you run any query for the first time, QODBC will first build the optimizer.

Please refer to the link below on how the optimizer works.

Refer to: How Optimizer Works

The Display Driver Status will show the current status in 'Red color,' which means data is fetched from the QuickBooks SDK instead of the QODBC optimizer.

Either you are fetching the data for the first time, or you might not be using QODBC Optimizer.

If the QODBC Display Driver Status is showing in 'Red color,' the data fetching process will be longer.

The reason is that QODBC has to fetch all the data from the QuickBooks SDK.

The time required to fetch data will depend on the number of records in your QuickBooks company file.

The data will be stored in an Optimizer file to increase the retrieval performance of queries.

From the second time onwards, you will notice the current status inside the QODBC Display Driver Status in 'Black color,' which means data is fetched from the QODBC Optimizer.

Tags: Optimizer, Red text, QODBC optimizer

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 Profitandlossbudgetvsactual Show Account name, Account number, Amount, Budget Parameters Fiscalyear=2015, Classfilterfullnames =' health Center,' 'administrative.'

I'm Getting 12 Columns, As One Would Expect, But The 12th Column Is The Sum Of The Previous 11 Columns, Not The 12th Month. This Is True For Both The Amount And Budget Segments. Also, The Budget Amount For The 11th Month is exactly one-half of the Actual Budgeted Amount. I Have Run The Actual Report, And The Totals For Each Month Are Correct For Both the Amount And Budget Until The Last Two Months In The report.

Also, if I replace the FiscalYear=2015, with DateMacro=’ThisYear’ I get this



Any Assistance Is Appreciated.  

Solution:

Please ensure that you are passing the same parameter in the report query you passed in QuickBooks UI.

Also, for the budget report, the Fiscal Year parameter is required. Without it, a report will not execute. So you need to keep the Fiscal Year parameter & add other date parameters.

For Example:

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, DateFrom={d'2015-10-01'}, DateTo={d'2016-09-30'}, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

Problem Description:

Has anyone run a report that will pull all the details for a range of invoices? In particular, I'm looking to run a report that shows the ship-to addresses for specific products over a date range. 

Solution:

Please use the query below to get the ship-to addresses for particular products over a date range.

Select InvoiceLineItemRefFullName, RefNumber, TxnDate, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressAddr5, ShipAddressCity, ShipAddressState, ShipAddressPostalCode, ShipAddressNote from InvoiceLine where TxnDate >= {d'2009-01-01'} and TxnDate <= {d'2009-03-01'} and (InvoiceLineItemRefFullName = 'Cabinets' or InvoiceLineItemRefFullName = 'Window') order by InvoiceLineItemRefFullName, RefNumber, TxnDate.

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 subtotals and blank lines. I know I can write a script/filter in Excel to eliminate these lines, but I can't figure out what parameters to use to do it using QODBC.

Solution:

In QODBC, you can remove subtotals and blank lines in sp_report by adding the following condition.

where RowType='DataRow'

For example:

sp_report ProfitAndLossDetail show TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisYearToDate' where RowType='DataRow'

Problem Description:

I am using the following SQL statement:

sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' , EntityFilterFullNameWithChildren = '{?customer}'

I then create a customer parameter in the parameter list. The customer name is pretty long, so is there a way to search by using a wildcard, i.e., *50-126*, rather than having to enter:

Customer name. location.50-126.projectdescription??

Solution:

Sorry, you can't use wildcards, and you need to use the full name path for a specific job:

sp_report JobProfitabilityDetail parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Abercrombie, Kristy:Remodel Bathroom', SummarizeColumnsBy = 'TotalOnly'

If you wanted, for example, to compare all Remodel Bathroom (or 50-126) jobs for all customers, you would need to run the Job Profitability detail report for each customer to a data file (like an append query in MS Access) and report from the data file instead.

Problem Description:

We need to bill all payroll costs to various customers. Payroll costs are defined as all of the following:

1. Employee's gross salary
2. Company share of social security
3. Company share of Medicare
4. Federal unemployment taxes paid
5. Texas unemployment taxes paid
When a payroll run is processed, all the above costs are coded to the appropriate customer, and QuickBooks flags them as "billable" at this point.

To bill the payroll costs, we go to the Customers menu and select "Invoice for Time and Expenses." All employee costs outlined above are then brought up in a list. We check the items we want to bill (all of them) and then select "create an invoice."

The above five items are listed separately when the invoice is created, but valuable descriptions are not displayed. We need the invoice to record the employee's name and the nature of the expense associated with each cost being billed out. E.g.

John Doe Gross Salary $500
John Doe Company Social Security $25
Etcetera
We have tried using various invoice forms, and these fields are never available to select for inclusion.

We can see the fields needed in the payroll module by viewing the details of the employee check. However, we have not been successful in finding these fields in the QuickBooks tables so that we can pull them into the invoice using your software. If I can see the data in the tables, I am confident I can extract it using QODBC and SQL to format it into a suitable report.

Thanks in advance for any help.

Solution:

You can get these details from the PayrollTransactionDetail report:

For this example, PayCheck:



You can find all the details using the following:

sp_report PayrollTransactionDetail show PayrollItem, WageBase, Credit, Debit, Amount parameters DateFrom = {d'2007-12-01'}, DateTo={d'2007-12-01'}, IncludeAccounts = 'All,' ReportDetailLevelFilter = 'AllExceptSummary,' EntityFilterFullNameWithChildren='Dan T. Miller' Where PayrollItem is not NULL

Change the bold entries to the employee and paycheck date you're after

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 DELETE. It will void the invoice instead of deleting it.

For Example:

SP_VOID FROM Invoice WHERE TxnID = '1B03F-1513346399'



You can use the query below to get a list of Voided Invoices.

Select RefNumber, CustomerRefFullName, and Memo from Invoice, like 'VOID:%.'

If no InvoiceLine is returned, you can resync your optimized InvoiceLine table by running:

Table Differences:

QODBC for QuickBooks Desktop has 150 tables while QODBC for QuickBooks Online has 83 tables. 

Table in QODBC for QuickBooks Desktop But not in QODBC for QuickBooks Online:

Table in QODBC for QuickBooks Online But not in QODBC for QuickBooks Desktop:

Field in QODBC for QuickBooks Desktop But not in QODBC for QuickBooks Online: