Concatenate values with a delimiter: How to simulate the TEXTJOIN spreadsheet function

[dbaum]

The TEXTJOIN function in Google Sheets and Excel is pretty handy in various situations. It's way more efficient than explicitly concatenating each individual value from a list--especially when you need to separate the values with a delimiter and extra-especially when any given list item might be empty (including the first or last items), creating the need to figure out how to avoid initial, final, and duplicate delimiters. Unfortunately, AppSheet doesn't provide a direct equivalent to this spreadsheet function. Nonetheless, it's possible to efficiently produce the same result without much complexity.

Use case

One situation where I use this technique is to reduce screen clutter. Detail views can get pretty long when you need to provide the user with information from lots of columns. This is especially problematic when the view includes quick edit columns that users need to access quickly; getting to those columns can require plenty of scrolling to get past the other information that necessarily precedes them since the user needs that information first for reference before then taking action via the quick edit columns. I use this technique to collapse multiple read-only columns in such views into a single virtual column.

Technique

1. Use the LIST function to construct a list of values (e.g., across a row's columns).
2. Use list subtraction to remove any empty list items.
3. Use the TEXT function to concatenate the list's items into a comma (actually, space-comma-space) delimited text value.
4. Optional: Use the SUBSTITUTE function to do things like:
   • Control for the potential presence of space-comma-space strings within list items
   • Replace the default delimiter with other characters you prefer

Sample output

Here are some screenshots that illustrate how you can summarize multiple columns of information in a single column that requires far less space than is required to include each of the individual columns.

Sample expression

Here's the underlying data and corresponding expression for the sample output. The expression illustrates various additional techniques you can incorporate within the list items (lines 5-8), such as conditionally including a column, combining a column value with other text, etc.

SUBSTITUTE(
  SUBSTITUTE(
    TEXT(
      LIST(
        IFS(ISNOTBLANK([Risks]), SUBSTITUTE("⚠️Risks: " & [Risks], " , ", "***,***")), 
        [Status], 
        IFS(AND([Progress] > 0, NOT([Status] = "Complete")), "Progress: " & TEXT([Progress])), 
        TEXT([Due Date])
        ) - 
      LIST("")
      ), 
    " , ", 
    " | "
    ),
  "***,***", 
  " , "
  )

Improvements welcome

I imagine I'm not the first to figure out this technique, but I don't find it documented in searching the community and AppSheet help. I'll be happy to learn from the replies any even better approaches for accomplishing this function and/or for more effectively searching available resources if it's indeed already documented.

[diogolupcosta]

Damn, that is beautiful 

[Joseph_Seddik]

Yes,  it is beautiful 🙂 Thanks David!

[dbaum]

A couple additional points for anyone interested in learning more details or considering this applying this in their app.

• Bear in mind that using the technique of subtracting an empty list eliminates duplicates.
• You can get creative with the delimiter--e.g., use an emoji, include a line break within the delimiter to create an unordered list.

The following mock-up illustrates both these points. For an example from the wild, in another thread that touches on enumerating import issues for the user see the "Parent Record" screenshot's list of flagged issues.

CONCATENATE(
"Favorite fruits:
  ⭐️", 
SUBSTITUTE(TEXT(LIST("Banana", IFS(false, "Ignored"), "Banana", "Apricot", "Grapes") - LIST("")), " , ", "
  ⭐️")
  )

[jyothis_m]

 @dbaum  Is it further possible to list items with serial number as pre-fix. Like

1. Orange

2. Red

3. Yellow

I had a requirement as above and what i did was a fixed list of 10 items with condition to show only if each index is not blank. However if more than 10 items are there in list then it's omitted. Is there a better method for this which does not limit the number of items in the list. 

[dbaum]

I can't think of a straightforward to do this. The only idea that occurs to me is to populate (perhaps only temporarily) a separate table with one item per row, create a column that includes the index number and whatever other content (e.g., CONCATENATE([_ROWNUMBER] - 1, ". ", [Column])), and then use that list within this tip's expression to create a column value (i.e., a single concatenated value delimited by a line break) for the pertinent row in your main table. While it may not be worth the effort, it would presumably be at least literally possible to run an automation to do all that each time pertinent data changes.

[jyothis_m]

Thank you @dbaum Interesting idea but i am bit confused about the implementation.  I shall try anyways