Creating a calculated field that uses if/then logic

[SethPorrazzo]

Hi All,

I'm attempting to create a calculated field that is based on an asset field with 97 different selections. The way I'm envisioning it is if a particular group of assets are selected from the previous custom field, the calculated field with display text such as "Graphic". There's going to be about 17 different options/text that I want to display.  Is this possible to pull off using a calculated field?

[RandyRoberts]

Short answer: maybe.

Can you post an example of what it might look like (in plain English)? When you say "if a particular group of assets are selected from the previous custom field", what will define that "group"?

You can always say IF({field name} equals "this value", then,display "This", if not then display "That") but I think you're asking for more than a simple if/else statement.

[SethPorrazzo]

sure, when I say group, I mean of the potential 97 asset options from the previous field, maybe 6 of them when selected will have the same answer in the calculated field such as "Graphic".  So, in theory this calculated field will be grouping them together.

[skyeh]

I think Seth might be asking about something like "if I pick apple, banana, orange or grapes from the original field, I want the calculated field to display "fruit", but if I pick broccoli, lettuce, asparagus or spinach, I want it to display "vegetables"". If this is the case, you might want to take a closer look at the IFIN syntax from here:

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/calculated-custom-data/cal...

[SethPorrazzo]

yes, this is what I meant.

[SethPorrazzo]

can you have multiple IFIN syntax in one calculated field?

[Rite18]

i think no, but in this example "if I pick apple, banana, orange or grapes from the original field, I want the calculated field to display "fruit", but if I pick broccoli, lettuce, asparagus or spinach, I want it to display "vegetables"". can be done with IFIN, as it supports two conditions

[SethPorrazzo]

ah ok, so the limit is two.  I was hoping to get up to 6-7 but it looks like I may need to develop a workaround. Maybe create 6-7 IFIN fields and then another concat field to bring all the results together.

[Rite18]

yes it seems to be, don't have environmental access to validate further if any other workaround is possible

[skyeh]

you should be able to do multiples. Have you actually tried? If you’re failing please post what your calc is, so we can troubleshoot. 

[SethPorrazzo]

Honestly, this is a grey area for me.  I wouldn't know where to start with creating a calculated field with multiple IFIN formulas in it.  what formula would I need to start with in order to get that going?

[Rafal_Bainie]

You can certainly do multiple ifs, I just quickly found this one I created at some point

IF(ISBLANK({parent}.{DE:Confirmed Deadline}),"No Value",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=0,"Same day",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=1,"Next day",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=2,"Due in 2 days",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=3,"Due in 3 days",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=4,"Due in 4 days",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=5,"Due in 5 days","Due in more than 5 days")))))))

at the same time I vaguely recall speaking with someone that after exceeding certain number this can run into some sort of issue, so I wouldn't exaggerate 

Good luck!

[skyeh · Accepted solution]

hi Seth, you would start with the original formula in the link I posted above, and just continue to add to it. The original formula is this:

IFIN(value, value1, value2,..., trueExpression, falseExpression)

Let's refer to the above as one complete IFIN statement.

If you had two options, you would want to sub out the falseExpression for another complete IFIN statement, e.g.:

IFIN(value, value1, value2,..., trueExpressionA, IFIN(value3, value4, value5,..., trueExpressionB, falseExpression))

If you had 3 options, you would swap out the falseExpression yet again, like this:

IFIN(value, value1, value2,..., trueExpressionA, IFIN(value3, value4, value5,..., trueExpressionB, IFIN(value6, value7, value8,..., trueExpressionC, falseExpression)))

You mentioned you had 17 options, so you would just do this another 13 or 14 times (I would test each time in order to make sure that each option was coming in, before adding a new option).

Doug dH does give a real world example here as well (with 2 options): 

https://experienceleaguecommunities.adobe.com/t5/workfront-ideas/add-switch-function-to-calculated-f...

[SethPorrazzo]

IT WORKED!!!  the gnarliest line of code I've ever created.  Thanks for the feedback.