Skip to main content
Solved

Using an IIF to change a value

  • September 13, 2024
  • 3 replies
  • 75 views
W
Varsity I
Forum|alt.badge.img+1

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

Best answer by lbarker

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

View original
William J Matthews
    Kandy Beatty
    1 person likes this
    • Kandy Beatty
      Kandy Beatty
    Did this topic help you find an answer to your question?

    3 replies

    meganfriesen37
    Captain II
    Forum|alt.badge.img+12

    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’

     

    Megan Friesen, Contego Software Solutions, Canada
    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    lbarker
    Varsity III
    Forum|alt.badge.img+1
    • lbarker
    • Varsity III
    • 80 replies
    • Answer
    • September 17, 2024

    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

    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    darylbowman
    Captain II
    Forum|alt.badge.img+13
    • darylbowman
    • 1749 replies
    • September 17, 2024

    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')

     

    Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
    Chris Hackett
    W
    2 people like this
    • Chris Hackett
      Chris Hackett
    • W
      wmatthews1877

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    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
    About Acumatica ERP system
    Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
    © 2008 — 2024  Acumatica, Inc. All rights reserved