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

# Calculation - listing values from several fields

### Title

Calculation - listing values from several fields

### Post

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

pn

• ###### 1. Re: Calculation - listing values from several fields

There are more sophisticated approaches that would use the list function with a table of related records and the  ≠ operator, but just staying with your original expression, you could use this expression:

Substitute ( List ( If ( color_1 = "red" ; "" ; color_1 ) ;  If ( color_2 = "red" ; "" ; color_2 ) ; If ( color_3 = "red" ; "" ; color_3 ) ; If ( color_4 = "red" ; "" ; color_4 ) ) ; ¶ ; ", " )

The List function turns this into a list of values separated by returns. The substitute function replaces the returns with a comma and a space.

• ###### 2. Re: Calculation - listing values from several fields

This gives me an error that says "In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed."

If you have a better solution that my calculation by all means I would prefer it.

Still looking for an answer.  Thanks.

pn

• ###### 3. Re: Calculation - listing values from several fields

NM, Idid something incorrect in the copy process.  It worked. Thank you very much, and sorry for the incorrect reply.

pn