I need to generate a label field by concatenating 5 fields, but here's the trick: I only want to add strings that match a value, and not add null strings. I have an example below where four fields are named "red, green blue and yellow" The values can be "Y", "N", or "Null". I need to build a label out of all of the "Y" values:
Red, Green, Blue, Yellow
Y, N, Y, Null --> resultant string would be "Red, Blue"
Y, Y, Null, N --> resultant string would be "Red, Green"
N, Null, N, Y --> resultant string would be "Yellow"
What I've currently done is a cascading series of calculated fields where I check two fields, and put that in a temporary field, then I check the temporary field against the next column and make a new temporary field, etc. Like so (abbreviated code):
If Red is Y and Green is Y then Temp1 = "Red, Green"
Elsif Red is Y and Green <> Y then Temp1 = "Red"
elsif Green is Y and Red <> Y then Temp1 = "Green"
Else Temp1 is 'empty'
The next calculation compares Temp1 to Blue for each condition and then builds temp2, and so on.
Question: Is this the most efficient way to do it? Or is there some clever code that I am missing? Can I do it all in one calculation without an insane number of if/then combinations? It would be nice to not have so many fields.