In the QuickBooks Desktop connector, I noticed that there is a column reference for Accounts Payable in the header section. However, I couldn't find a column for line references to accounts, such as Cost of Goods Sold, Fixed Assets, or other account types typically associated with bill line items. Can anyone explain why this might be the case or if there's a workaround to retrieve this information?
Your insights would be greatly appreciated!
Best answer by Ethem Q
View original
Hi
Based on your inquiry I believe you are referring to the COGSAccount and AssetAccount columns that are associated with the LineItems, is that right?
https://cdn.cdata.com/help/RQJ/ado/pg_table-itemlineitems.htm
It does not look like these columns are exposed by the BillLineItems table directly however, you can make use of a JOIN statement in order to get data for the columns COGSAccountId, COGSAccount, AssetAccount, AssetAccountId by joining BillLineItems with ItemLineItems, as in the following example:
SELECT [BillLineItems].[ID],[BillLineItems].[BillId], ItemLineItems.Account,ItemLineItems.AccountId,ItemLineItems.COGSAccount, ItemLineItems.COGSAccountId,ItemLineItems.AssetAccount, ItemLineItems.AssetAccountId FROM [BillLineItems] JOIN ItemLineItems ON BillLineItems.ItemId=ItemLineItems.LineItemIdIn case this is not what you are looking for or you have any other questions, feel free to raise a support case with our support team at support@cdata.com and we will be happy to further assist you.
Hi
Thanks, for your kind support. It is exactly what I am looking for.
I am trying to create a data model for reporting purposes and encountered an issue with the Credit Memo Line and Invoice Line tables. The Tax column is not associated with the Account table, but it should be linked to the Other Current Liability account. As a result, the tax information is not being reflected in my report. Am I missing something, or is there a way to correctly relate these column?
Any help would be appreciated!
Hi
Both the CreditMemoLineItems and InvoiceLineItems do expose a Tax column
which refers to the total sales tax applied to this transaction: https://cdn.cdata.com/help/RQJ/odbc/pg_table-invoicelineitems.htm
I am able to to get Tax related information from the Accounts table as you can see from the screenshot:
Also, when you say, the tax information is not reflected do you mean that you are not getting data for this column?
What columns are you referring to specifically when you are asking if there is a way to relate these columns?
If you could elaborate more on what you are trying to accomplish and what you are expecting to see in your final report that would give us a better picture of the use case.
Lastly, I would suggest you raise a support ticket here or reach out to the support team at support@cdata.com with the details of this issue so that our team can further assist you via email support.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Data Connectors
ETL/ ELT Solutions
Cloud & API Connectivity
OEM & Custom Drivers