By using this website, you agree to our Terms of Use (click here)
Do Generic Inquiries have the ability to perform a subquery for a linked table field similar to a SQL statement? See SQL example for BREarliestEndDate below:
Select
IR.Title,
=SUM([BillingRule.TotalFunded]) as TotalFunded,
(select MIN(br.EndDate) from BillingRule br where br.Status = ‘Open’ and br.InvoiceRuleID = IR.InvoiceRuleID) as BREarliestEndDate,
From
InvoiceRule IR
Would I add the table without the relation and then write an expression in the result grid?
Thanks ahead of time.
Tim
No, GIs cannot do SQL subqueries. There are many things they can't do. For example, grouping on expressions, filtering on aggregated data.
I really hope Acumatica greatly improves GI functionality. Coming from a SQL background as I do, I find them extremely limiting.
I found I could achieve a workaround by using an expression in the results grid for the Earliest End Date.
=Min(IIf([BillingRule.Status] = 'O', [BillingRule.EndDate], '01/01/2199'))
Since, I am only interested in EndDates dates where the Status is a value of 'Open', the expression above works. I just filter out the value = '01/01/2199' in the report.
I hope this helps someone else.
Yep, building a SQL View and feeding it to a Generic Inquiry is my workaround for this situation.
This is a great work around. I was able to use the forumla to find the first and last dates for of all of production orders for each of our project to use in a dashboard widget.