Why Does the BillLineItems Table in the QuickBooks Desktop Connector Lack a Column for Account ID/Account Name?

Userlevel 1

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 11 June 2024, 01:09

View original

3 replies

Userlevel 5

Hi @Shakil 

Based on your inquiry I believe you are referring to the COGSAccount and AssetAccount columns that are associated with the LineItems, is that right? 

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.LineItemId

In 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 and we will be happy to further assist you.

Userlevel 1

Hi @Ethem Q 
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!

Userlevel 5

Hi @Shakil 

Both the CreditMemoLineItems and InvoiceLineItems do expose a Tax column     
which refers to the total sales tax applied to this transaction: 

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 with the details of this issue so that our team can further assist you via email support.