4 Replies Latest reply: May 15, 2014 10:16 AM by Olly Bond

# One calculated field that has multiple if's

I have a question about a calculated field.  I might be over thinking this or missing the obvious, but here goes.  I have 10 fields I'm trying to concatenate together.  Some of the fields may be empty.  After each field i want to put a comma and a space if the field isn't empty.  So my fields might look like this

A

B

C

D

E

So In the above example I want my output to be A,B,C,D,E.  How do I do multiple if's in one calc field.  So my first if would say if trim(field1)<> "",(field1) + ", ","")  How do I continue that on for fields2, 3, etc.  So if it has data, put the data and then the comma space but if no data then do nothing and check next field.  I can't seem to figure out how to code that if even possible.

Thanks.

Phil

• ###### One calculated field that has multiple if's

I think Olly or Grant would undoubtedly provide the best response to this but in the interests of being helpful (and having done enough of these nested 'if' statements myself!), I thought I'd have a go.

The formula below covers 3 fields but you can just add further 'if' statements where necessary and then make sure you close with correct number of end brackets (which Monarch will guide you on visually too):

If(trim(field1)<> "",(field1) + ", ",If(trim(field2)<> "",(field2) + ", ",If(trim(field3)<> "",(field3) + ", ","")))

Hope I'm right...

Alex

• ###### One calculated field that has multiple if's

Alex,

Thanks for responding.  That's pretty much what I came up with to start and then realized it wasn't working how I thought.  All that does is grab my field1 and never concatenates any of the other fields.  So the example code above gives me "A," using my sample input above.  I think what I will end up doing is one field that concatenates all 10 fields I have together.  Then multiple other fields that do the replace of ',,,' to ',' then another replace that does ',,,,' and so on until I have all possible combinations.  I'm so use to working in VB/Visual Studio, as I'm been programming for 20 years, where I can do these things much easier then in Monarch.

Thanks.

Phil

• ###### One calculated field that has multiple if's

So I did the concatenate but the replacing of commas doesn't seem to work.  If you say replace(field1,",,,",",") it doesn't do anything.  It's like it won't allow you to replaces commas.  I used the same replace to pick out some random text and replace it and it works fine.  So can replace not work with commas or do you have to do something special with them?

• ###### One calculated field that has multiple if's

Hello Phil, Alex,

Replace(Field1;",,";",")[/CODE] should work.

Best wishes,

Olly