Using an IIF to change a value

Question

We have a GI called AR-Daily Sales History. We would like to, by using an IIF in the following manner.

=IIF(LEFT([ARInvoice.RefNbr],3) = 'INV', 'US',
IIF(LEFT([ARInvoice.RefNbr],3) = 'INT', 'INT',
IIF([ARInvoice.OrigDocAmt] < 0.00, 'CRM')))
If the RefNbr starts with an INV then its a US invoice, if INT then International. That part is covered. The issue starts when we get to the Credits. There are some credits that the RefNbr starts with an INV which makes it US but then the DocType is a Credit Memo but because of the INV it still comes across as US. Using the last line in its present form, I get an error message: “Index was outside the bounds of the array”. I’ve also attempted this coding: =IIF(LEFT([ARInvoice.RefNbr],3) = 'INV', 'US',
IIF(LEFT([ARInvoice.RefNbr],3) = 'INT', 'INT',
IIF(LEFT([ARInvoice.RefNbr],2) = 'CM', 'CRM',
IIF(LEFT([ARInvoice.RefNbr],3) = 'INV' AND LEFT([ARInvoice.DocType],5) = 'Credit', 'CRM', 'CRM')))) which still comes up as ‘US’.

lbarker · Accepted Answer

Also try Switch instead of Iif as sometimes a performance issue if slow.

Iif works like If then else statements. Switch is like a Case statement so it evaluates if the condition is true and puts the value if it matches.

=Switch(

[ARInvoice.DocType]=’INV’ AND LEFT([ARInvoice.RefNbr],3) = 'INV',’US’,

[ARInvoice.DocType]=’INV’ AND LEFT([ARInvoice.RefNbr],3) = 'INT',’INT’,

[ARInvoice.DocType]=’CRM’ ,’Credit’)

But maybe u should rather be looking at the address for this rather than using numbering? or I assume the branch as you must be getting the numbering from the branch which will make it easier

darylbowman · Answer

Your statement will evaluate from beginning to end, only continuing if the condition is not ‘true’. Your last statement would probably work if you placed the last condition first, since all ‘INV’ documents would be tested for being a Credit Memo first, and if they weren’t, would then be evaluated on only the ‘INV’.

But @lbarker is right, you should be using a switch statement instead:

=Switch(LEFT([ARInvoice.RefNbr],3)='INV' And LEFT([ARInvoice.DocType],5)='Credit','CRM',LEFT([ARInvoice.RefNbr],2)='CM','CRM',LEFT([ARInvoice.RefNbr],3)='INV','US',LEFT([ARInvoice.RefNbr],3)='INT','INT')

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded