Aggregate and Sum on calculated field

Question

Given the following customer's transactions (invoices & credit memos)

DocType OrigDocAmt     CustomerID
------- -------------- -----------
CRM      100            10278
CRM      150            10278
CRM       75            10278
INV      200            10278

I need to get the total amount. Taking into consideration that INV should be negative

select sum(case when DocType = 'INV' then -OrigDocAmt else OrigDocAmt end) TheTotal from 
ARRegister where CustomerID=10278

TheTotal
---------------------------------------
125

How do I write an Aggregated BQL to do that ? I can't just call Sum<> as it will give me 525.

Thanks in advance !

Answer 1

If you are working with DAC fields you can use PXUnboundFormula and SumCalc:

[PXUnboundFormula(typeof(Switch<Case<Where<ARRegister.docType, Equal<ARInvoiceType.invoice>>, 
                         Minus<ARRegister.origDocAmt>>, ARRegister.origDocAmt>), 
                  typeof(SumCalc<DAC.totalField>))]

If you're in the context of executing loose BQL query in a graph you can make 2 BQL queries, one for INV type and one for all others then sum the result of both total without BQL. With more complex business rules declarative solutions like SQL becomes unwieldy and having an iterative approach like Samvel Petrosov suggest is required.

Answer 2

I suggest you use PXSelectGroupBy to group by DocType and CustomerID and loop over that groups and calculate the total amount as is shown below:

int? customerID = 4899;
decimal? totalAmount = 0M;
using (IEnumerator<PXResult<ARRegister>> invoices = PXSelectGroupBy<ARRegister, Where<ARRegister.customerID, Equal<Required<ARRegister.customerID>>>, Aggregate<GroupBy<ARRegister.docType, GroupBy<ARRegister.customerID, Sum<ARRegister.origDocAmt>>>>>.Select(this.Base, customerID).GetEnumerator())
{
    while (invoices.MoveNext())
    {
        ARRegister groupedInvoice = invoices.Current;
        totalAmount += groupedInvoice.DocType == "INV" ? groupedInvoice.OrigDocAmt * -1 : groupedInvoice.OrigDocAmt;
    }
}

The reason that I have written the loop over groups is that Acumatica's Sum<> operator does not support working with Case<> operator and you just can't write Aggregate with a condition on the column.