Skip to main content
Solved

Using an IIF or Switch to change the value of a field based on the value of another field


Forum|alt.badge.img+1

I have an Generic Inquriy column called orig_ord_type which, depending on the ARInvoice.DocType could either be a ‘C’ for Credit or an ‘O’ for Invoices and Debit Memos. My logic for this  is =IIF(LEFT([ARInvoice.DocType], 6)  =  'Credit', 'C', 'O') which accounts for Credit Memos and Credit WO (Write Offs) which should be coming up as ‘C’, but everything is coming up as an ‘O’. I’ve even tried using =IIF([ARInvoice.OrigDocAmt] < 0.00, ‘C’, ‘O’) and anything listed as a Credit still had an O in the column. If anyone has other ideas, they would certainly be appreciated, even using the =SWITCH function. I’ve tried the =SWITCH and I received an error about the MEMO token.

 

Best answer by Laura02

Hello,

Document amounts aren’t stored as negatives in the database; that’s why everything turned into “O”.

I use nested IIf statements to solve a situation like yours.  (I’m not a programmer).  Try something like this:

IIF(LEFT([ARInvoice.DocType], 6)  =  'Credit', 'C',

IIF(LEFT([ARInvoice.DocType], 5)  =  'Debit',  ‘D’,

IIF(LEFT([ARInvoice.DocType], 5)  =  ‘Prepa’, ‘P’,    [etc.]

'O' ) ) )

 

Laura

View original
Did this topic help you find an answer to your question?

5 replies

Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • Answer
  • August 23, 2024

Hello,

Document amounts aren’t stored as negatives in the database; that’s why everything turned into “O”.

I use nested IIf statements to solve a situation like yours.  (I’m not a programmer).  Try something like this:

IIF(LEFT([ARInvoice.DocType], 6)  =  'Credit', 'C',

IIF(LEFT([ARInvoice.DocType], 5)  =  'Debit',  ‘D’,

IIF(LEFT([ARInvoice.DocType], 5)  =  ‘Prepa’, ‘P’,    [etc.]

'O' ) ) )

 

Laura


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 112 replies
  • August 23, 2024

In my initial entry I did try the =IIF(LEFT([ARInvoice.DocType], 6)  =  'Credit', 'C', 'O') which should have worked because the Credit Memo and Credit WO would have fallen under that caveat. 


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 112 replies
  • August 23, 2024

I attempted to use your suggestion and it’s not validating. The error is: Missing operator before the ’, IIF(LEFT([ARInvoice.DocType], 5) = ‘Prepa’


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2621 replies
  • September 10, 2024

Hi @wmatthews1877 were you able to find a solution? Thank you!


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 112 replies
  • September 11, 2024

Everything has worked out, thank you for the followup.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings