By using this website, you agree to our Terms of Use (click here)
Hi Everyone, I am trying to build a Generic Inquiry for sales by branch that will match our financials. I have determined that the best way to pull the data is from the ARTran Table using the Branch ID. I have also determined that the best field for the dollar amount in the CuryTranAmt field. However, For some TranTypes such as CRM and others the result needs to be subracted. How can I make the numbers negative in the GI for only certain TranTypes and not for others?
Hi Lorne
I would use something like this =IIf( [ARTran.Type] = 'CRM',[ARTran.Amount]*-1,[ARTran.Amount])
Create a group by and sum of this field
The fields might not match the actual ones, but just the idea.
How about this:
=IIf([ARTran.DrCr] = 'D', -1, 1) * [ARTran.Amount])
This handles all tran types without having to specify them in the formula.
@roycelithgo Brilliant! Thank you. One other question. do you know what I put in the schema field if I want the FinPeriodId to only filter by year?
Much appreciate your suggestion