• Status: Solved
• Priority: Medium
• Security: Public
• Views: 118

# Formula to concatenate multiple boolean fields with a delimiter

Gurus,

I need a formula to concatenate and separate 6 Boolean fields together where one or more may be true.

For instance, Field 1 may be True or False.  If True then "Field 1" else ""

I need the formula output result to be:
Field 1
or
Field 2
or
Field 1, Field 2
or
Field 2, Field 4

etc, etc.  I'm adding a comma to separate the values for readability.

Here's the formula I'm using that's close but it's adding a "comma" after conditions where only one field is True

Local StringVar strOut;

if Not(isnull({CONTACT.Automation})) AND {CONTACT.Automation} = True then
strOut := "RA" & ", ";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Hardfacing} = True then
strOut := strOut + "RH" & ", ";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Industrial} = True then
strOut := strOut + "IND" & ", ";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.PMA} = True then
strOut := strOut + "PMA" & ", ";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Tactical} = True then
strOut := strOut + "TAC" & ", ";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Underwater} = True then
strOut := strOut + "UW" & ", ";
Mid(strOut,1)

What am I missing?  Thanks in advance!!
0
SStroz
• 2
1 Solution

Commented:
You could change the last line to

Left(strOut, Length(strOut)-1)

or

Mid(strOut, 1, Length(strOut)-1)

mlmcc
0

Commented:
Another option is to put the comma in front of each value, and then use Mid to exclude the first comma from the output.

``````Local StringVar strOut;

if Not(isnull({CONTACT.Automation})) AND {CONTACT.Automation} = True then
strOut :=  ", " + "RA";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Hardfacing} = True then
strOut := strOut + ", " + "RH";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Industrial} = True then
strOut := strOut + ", " + "IND";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.PMA} = True then
strOut := strOut + ", " + "PMA";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Tactical} = True then
strOut := strOut + ", " + "TAC";
if Not(isnull({CONTACT.Automation})) AND {CONTACT.Underwater} = True then
strOut := strOut + ", " + "UW";

if Length (strOut) > 2 then
Mid(strOut,3)
else
strOut
``````

James
0

Author Commented:
mlmcc, your formula gave me this error:

String length is less than 0 or not an integer

I used James solution for this one.

Many thanks to both of you!!!
0

Commented:
You're welcome.

FWIW, the error on mlmcc's formula was probably from records where none of your conditions were true, so you ended up with an empty string.  Then Length (strOut) - 1 would give you -1, which would give you that error.

You could handle that using

if Length (strOut) > 1 then
Left(strOut, Length(strOut)-1)

or

if Length (strOut) > 1 then
Mid(strOut, 1, Length(strOut)-1)

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.