We utilize 5 'stop' fields for routing trucks, I would like to populate the last stop in a single field on a saved search, however there are times where stop 5 isn't necessarily the last stop, sometimes there are only 4 stops or 3 or 2 or 1 stops.
I have a working formula, however I'm curious if there is a better way to handle this, here is my formula:
CASE WHEN {stop5} IS NULL THEN CASE WHEN {stop4} IS NULL THEN CASE WHEN {stop3} IS NULL THEN CASE WHEN {stop2} IS NULL THEN {stop1} ELSE {stop2} END ELSE {stop3} END ELSE {stop4} END ELSE {stop5} END
I feel like my formula is clunky and there might be a better way that I'm not aware of, any assistance will be appreciated!
This seems like a perfect use-case for COALESCE, which "returns the first non-null expr in the expression list."
COALESCE({stop5}, {stop4}, {stop3}, {stop2}, {stop1})