Replace a nested If that has many if's


So i have a nested if statement that checks the status of a shipment.
"cancelled" or "storage" first using boolean, the progresses through a series of dates to see which have been added, finally defaulting to "At Origin" if it has not left yet.

Multiple IF's are not ideal, but I can't see how I get round this one.
Even if I break it down into 6 separate lines I still only need one 'Tank Status' for each line item, so would have to get the result somehow.


Best Answer

  • TristanS
    Answer ✓

    @DeveloperCYT That's the simplest way you can do it really. There are other alternatives to do it but I would not recommend it as it makes it less readable. Posting it here just to give you an idea.

    The alternative I am providing is based on the following assumptions:

    • Cancelled can occur at any point of the process
    • Status changes occur in the following sequence:
    1. At Origin
    2. On Water
    3. At Port
    4. Bottler Booked
    5. Produced
    6. Storage

    Step 1: Assign numeric code to each status

    Tank Status


    At Origin


    On Water


    At Port


    Bottler Booked








    Step 2: Create numeric line items for each of the variables used your nested IF.

    Line Item


    ShipDate Value

    IF ISNOTBLANK('Ship Date (Act)') then 1 else 0

    ArrivalDate Value

    IF ISNOTBLANK('Arrival Date (Act)') then 1 else 0

    BottlerDate Value

    IF ISNOTBLANK('Del to Bottler Date (Act)') then 1 else 0

    Prod Date Value

    IF ISNOTBLANK('Prod Date (Act)') then 1 else 0

    Storage Value

    IF IN Storage? Then 1 else 0

    Cancelled Value

    IF CANCELLED? Then 7 else 0

    Final Value

    MAX(Cancelled Value, ShipDate Value + Arrival Date Value + Bottler Date Value + Prod Date Value + Storage Value)

    Step 3: Status = FINDITEM(Tank Status, TEXT(Final Value))

    As advised, I don't recommend this alternative compared to what you currently have in place. Just did it to give you an idea what other options are available.


  • Hi, later i did something very similar to that, using formulas equalling 99,1,2,3,4,5,6 and then max of the fields, so very similar, but same result.
    Thanks for confirming, much appreciated.

  • TristanS

    @rob_marshall curious which of the two approaches above do you prefer and why?