By using this website, you agree to our Terms of Use (click here)
I am trying to create what I thought would be a simple formula in a GI to show Credit Memos as negative amounts, and all others as positive. Keep in mind there are credit memos that are zero, but I can't seem to get the syntax correct. I was close but the zeros threw it all off. I am using the ARInvoice.DocType and want to make Doc Type of Credit Memo negative for the ARTran.curyTranAmt. I thought this would be simple, but I am clearly doing something wrong and keep getting syntax errors. If there is some kind of document that can assist me with syntax you can point me to, I'm sure I could figure it out.
You can try something like this:
=IIf([ARInvoice.DocType]='CRM',-[ARInvoice.CuryOrigDocAmt],[ARInvoice.CuryOrigDocAmt])
The key is knowing the code for the document type (CRM in this case). The way I find that out is dropping a new field and using the following formula which converts the display value to the code value:
=CStr([ARInvoice.DocType])
@timrodman Nice! Didn't know that! I've used the filters to filter the report the way I want it then use trace to see the SQL which shows the value.
I can get this formula to work in the GI, but when I export to Excel the fields are not being recognized as a number. So, I can't do any further manipulating of the data. Has anyone else experienced this? Anyway around it?
Hi Kristin,
Here is the formula I use and I get negative numbers.
=IIF([ARInvoice.DocType]='CRM',[ARInvoice.OrigDocAmt]*-1,[ARInvoice.OrigDocAmt])
You may need to change the fields to suit your needs but I believe the *-1 is the key.
Hope this works for you!
@jmmarasco Thank you for your response! I'm not having any issues getting the negative numbers to show in Acumatica. It's when I export to Excel. The numbers aren't exported as "numbers" even though they look like it. So I can't do any calculations with them. I tried formatting the cell, but that doesn't change anything. I was able to copy and paste values in a different column. Then I could convert text to numbers. I adjusted my formula in my GI to match yours, but I have the same results.
Hi Katie, I just tested an export of my AR Invoices GI and I was able to sum the columns and create different formulas without any issues. Maybe its a version or setup issue? I am running the latest version of Microsoft 365 on a Mac and Acumatica 2022 V2. Also, maybe a crazy thought, but I wonder if it has anything to do with the "Schema Field" selection in your GI? I included a pic of what I use for reference.
@kphillips I wish that Schema Field was just called Format because that's really what it is.
WOW, that was kinda a shot in the dark, happy to hear it worked for you!!
I know this is a bit of an old thread, but thought I'd post a Report Designer fix for getting credit memo values to show as negative in the report. Found this in the Community forum: https://community.acumatica.com/reports-and-generic-inquires-115/print-values-as-negative-in-report-designer-16991.
Can't say if this would work in a GI or not, just thought I'd throw it out there:
"In the ARRegister and ARInvoice tables there are fields titled “SignAmount” that reflect 1 for invoices and -1 for Credit Memos. This could be used as a multiplier in a formula for the document balance - ARINVOICE.CURYDOCBAL * ARINVOICESIGNAMOUNT."
I used these quite successfully in my field definitions and didn't require I call the doc type that increased the complexity of the definition.