AnsweredAssumed Answered

Calculation - listing values from several fields

Question asked by precociousninja on Apr 7, 2010
Latest reply on Apr 7, 2010 by precociousninja


Calculation - listing values from several fields


I can't seem to figure out a way to make a field that looks at several other fields and gives me their values in a comma sorted list, while omiting one from the value set.


Here is the example:


field          value

color_1      red

color_2      blue

color_3      green

color_4      orange


I want a field that looks at these four values and omits the one I choose. for example the calculation would give me "blue, green, orange" if I choose to omit "red." (in my case, I will always want to remove "red" but this color may be in any of the 4 fields)


The calculation as I have it written now, is surely not the best way to do this.  Here is what I have:


If ( color_1 = "red" ; "" ; color_1 ) &  If ( color_2 = "red" ; "" ; color_2 ) & If ( color_3 = "red" ; "" ; color_3 ) & If ( color_4 = "red" ; "" ; color_4 )


This gives me what I want, but they are listed together "bluegreenorange" like that.

Any ideas as to how to get what I need?


Thanks a bunch