Formula Tool - Help with Formula

[lisamccullough]

I have the following formula and I know I'm missing the "ElseIF" but I'm not 100% how to set this up. Basically, this formula is looking for a value and if it's not there it goes to the next column to look for the data...

IF [TIER 1 DATA]="" THEN "1" OR IF [TIER 2 DATA]="" THEN "2" OR IF [TIER 3 DATA]="" THEN "3" OR IF [TIER 4 DATA] = "" THEN "4" OR IF [TIER 5 DATA]="" THEN "5" ENDIF 

[marcusblackhill]

Hey @lisamccullough 

I'm not sure if I understood well what you need but look if that help!

IF [TIER 1 DATA]=""

THEN "1"

ELSEIF [TIER 2 DATA]=""

THEN "2"

ELSEIF [TIER 3 DATA]=""

THEN "3"

ELSEIF [TIER 4 DATA] = ""

THEN "4"

ELSEIF [TIER 5 DATA]="" THEN "5"

ELSE ""

ENDIF 

[AngelosPachis]

Hi @lisamccullough ,

I am sure that the formula tool is one of the ways that will solve your problem. Although I've come up with a workflow that I consider to be more robust, especially if other columns are added in the future

The workflow looks something like this

and the output it gives is 

As you can see, I have the first value for each record. It doesn't matter that I have null fields and you have empty fields, the result remains the same. Hope that helps!

Regards,

Angelos

[grazitti_sapna]

Hi @lisamccullough,

Try Writing the formula as below. I hope it will solv your issue.

IF [TIER 1 DATA]=""

THEN "1"

ELSEIF [TIER 2 DATA]=""

THEN "2"

ELSEIF [TIER 3 DATA]=""

THEN "3"

ELSEIF [TIER 4 DATA] = ""

THEN "4"

ELSEIF [TIER 5 DATA]="" THEN "5"

ELSE ""

ENDIF

Just asking a question. What if you have blanks in multiple columns? Lets suppose you have TIER 2 DATA="" and TIER 4 DATA is also equal to "" then the above formula will return 2 and not 4. If this is your expected output then you should go with the above formula, otherwise you can share your sample input and output and will try and create a workflow for your use case.

I hope I Have been able to help you.