FieldA is a lookup (to another SP list) with the option to select more than one choice from 9 options: let's call them 1 thru 9.
FieldB needs to be a calculated field that evaluates FieldA.
Certain values in FieldA trump others. Sort of like a roll-up, maybe.
- If there is a "6" selected, then nothing else matters and FieldB = Red.
- If there is not a 6 but there is a 2, then nothing else matters and FieldB = Blue.
- If there is neither a 6 nor a 2 but there is a 4, then nothing else matters and FieldB = Green.
- For any other scenario, FieldB = Yellow
I've done some simple nested if statements in calculate fields, but I've never done anything against a LOOKUP field and I don't know what complication the multi-select adds to this mess. Any functional samples?