3 Replies Latest reply on Apr 7, 2010 3:15 PM by precociousninja

    Calculation - listing values from several fields



      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