I am posting this in the hopes that anyone who follows me down this rathole will find it and not spend hours banging their heads against their desks in frustration.
If you are using Advanced Reporting for Quickbooks, which is derived from Qlik, you may discover that a critical constant value inside Quickbooks has a typo.
One of the constants for the Transactions.Classified Account Type field is "Assest", not "Assets"; attempts to extract the desired data from the database will fail unless you have used the correct (typo) constant.
Resolution
When accessing this field, use the constant value "Assest" not "Assets".
Cause
To the best of my ability to determine this is an error inside the QB system and is not actually a Qlix defect.
Related Content
I discovered this while using the Advanced Reporting tool in Quickbooks Desktop Enterprise, while modifying one of the default reports provided. Specifically, while working on a report to extract sales data, I found this boilerplate code:
sum(if(Not IsNULL([Item.Item Type]) and Not IsNull([Item.Full Name]) and Not IsNull([Transactions.Txn Type]), if(not Match([Transactions.Classified Account Type], 'Assest','Expense'),[Transactions.Quantity], 0)))
I thought that "Assest" was a typo in the example code and fixed it, which broke this function. Restoring the typo restored the functionality. Thus I deduce this constant is actually embedded into the Quickbooks database and needs to be referenced as is.
Environment
- Advanced Reporting tool for Quickbooks Desktop Enterprise
