Skip to main content

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(TARInvoice.RefNbr],3) = 'INT', 'INT',
IIF(FARInvoice.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’.
 

Hello,

I’d also consider using the ARInvoice.DocType value.  As the Credit Memo amounts are always stored as a positive number, so using a <0 function won’t work.  

You also need to use the drop down values for the doc type field.  i.e. ARInvoice.DocType]=’CRM’

 

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(

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

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

TARInvoice.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

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(dARInvoice.RefNbr],3)='INV' And LEFT(=ARInvoice.DocType],5)='Credit','CRM',LEFT(dARInvoice.RefNbr],2)='CM','CRM',LEFT('ARInvoice.RefNbr],3)='INV','US',LEFT('ARInvoice.RefNbr],3)='INT','INT')

 

Reply


Terms & ConditionsCookie settings