Replace a nested If that has many if's

DeveloperCYT

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.

thanks

TristanS

@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	Code
At Origin	1
On Water	2
At Port	3
Bottler Booked	4
Produced	5
Storage	6
Cancelled	7

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

Line Item	Formula
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.

DeveloperCYT

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?

rob_marshall

@TristanS

I would do something very similar in assigning 1's to the booleans but then creating a line item subset that has a Total on it.

TristanS

@rob_marshall Thanks