1 Reply Latest reply on Dec 9, 2010 7:57 AM by FentonJones

    Text Conditional Concat



      Text Conditional Concat


      Does anyone know how or if I can do a conditional text concatenation?  Here is the scario.  I have a table with three fields, each containing a committee name.  The individuals in the table may be on one, two, or three different committees, which is why I have the three fields.  On a layout, I am trying to include a text field that shows the committees the individual participates on.  I figured out how to use a merge field to include all three fields in one text item.  However, because people may belong to only one or as many as three committees, the format doesn't look as professional as I would like.  For example, a person who is a member of only one committee will be displayed on my layout like the following:  committee 1, ,

      I'm looking to dig deeper and make this merge field more conditional, so it evaluates whether or not the field containing the committee is empty.  If it is empty, it not only is not included, but it also doesn't include the comma separator - which just makes it look messy.  Here are some examples of what it does and what I would like it to do:

      Member of only one committee
      What I get currently:  committee 1, ,
      What I would like to have:  committee 1

      Member of two committees
      What I get currently:  committee 1, committee 2,
      What I would like to get:  committee 1, committee 2

      Does this make sense, and does anyone know if/how to do it?  Thanks!

        • 1. Re: Text Conditional Concat

          First, for proper relational design, you should (or could) be using a small "join" table, to store the assignments of committees to people. The "flat" structure of "field 1, field 2, field 3" is (very) awkward to work with. Doing a Find on 3 fields is kind of a PITA. Almost any kind of reporting on 3 is worse.

          A relational design would have the person ID, the committee ID (or name, but really, ID is best; especially with long names of things; I imagine you have or could create a table with committee names (and IDs). 

          But whether using the above or your 3 fields, this solution would work the same. The List() function will take either multiple fields or a single related field, and return non-empty values as a return-separated list, in the order the values (or records) were entered (or the sort order of the relationship, if any).

          Sometimes you'd want the values to be comma-separated instead (usually for display only, since you already have the original data somewhere else). 

          Substitute ( List ( committee 1; committee 2; committee 3); ¶; ", ")

          ¶ is the carriage return symbol. It is on the buttons in the calculation box, or you can type it, Opt-7 on a Mac (likely Alt-7 on Windows). If the field is for display only, not for Finds, then you can make its Storage, [x] Do not store.