Please Help: How to concatenate list of different rows of another table into one value

Hello! I will try to explain my situation as best I can…

Table 1: Master Store List

  • Contains list of stores, with [#], [Name]
  • Contains 1 column called [Store Visit History]
  • Contains 1 column called [Name/Date/Comments]

Table 2: Daily Report

  • When “Master Store List” row is edited, “Name/Date/Comments” section resets on edit, so you can type in a new entry
  • When saved, a row is added to this table containing the [#], [Name], [Date], and details from [Name/Date/Comments]
  • The [Name/Date/Comments] details would be entered into the [Visit Details] automatically

Goal: Have (Master Store List[Store Visit History]) reflect a list of comments made only for that store, pulling the different rows from the “Daily Report” table. It would be a list of [Visit Details] of the different rows that have the same # and Name as the store list, but compiled into one column.
Details: I would love if there could be a blank line between each entry, and if they could be sorted by the [Date] column of Daily Report, with the newest visits shown at the top, and the oldest visits shown at the bottom.

I would assume the best way to do this is to use a virtual column, and have the formula as the app formula, but my struggle is how to write the formula. I know workflow templates have the Start: function.

Any and all help with this would be appreciated x1000. Thank you!

So I have made a partial formula

Select(Daily Report[Visit Details],AND([#]=[Store #],[Name]=[Store Name],[List Only]=[Store List]))

This formula gets values, but separates them with a comma.
I still need each the Visit Details to be sorted by the [Date] column with newest first, and I still need a space in between each visit. I have tried but failed to implement these tasks into my formula.

First, you’ll need a virtual column in Master Store List with an App formula expression like this to get a sorted list of the appropriate rows from Daily Report:

ORDERBY(
  FILTER(
    "Daily Report",
    AND(
      ([#] = [Store #]),
      ([Name] = [Store Name]),
      ([List Only] = [Store List])
    )
  ),
  [Date],
    TRUE
)

If the virtual column above was called DR Rows (for example) then the App formula expression for Visit Details would be something like this:

SUBSTITUTE(
  [DR Rows][Visit Details],
  ",",
  "
"
)

Note carefully the indentation in that expression: that quotation mark before the closing parenthesis should be on a line by itself with no indenting. The quotation mark on the line before it should be the very last character on its line.

3 Likes

Hi! First, thank you very much. Second, I am having a bit of trouble with the formula. In my new virtual column, it reads as only the row key, and not the actual “Visit Details”. Also, I have an issue with the ", " delimiter. In the comments, the people often use commas, and when the Substitute function is used, it replaces every comma/space with a new line.

Clarification: [Visit Details] is a column in the Daily Report table, and my goal is to compile each [Visit Details] value in the Daily Report that matches store #/Name, and write it out in a new virtual column in the Master Store List table.

Again, thank you very much!

That is the intention.

In the configuration of that virtual column, change the item delimiter to a character that is very unlikely to occur in user comments (like perhaps the vertical bar, |), then also replace "," in the SUBSTITUTE() expression with that same character in quotes (e.g., "|").

1 Like

Ok! I do have an additional question though. How do I get the visit details out of the row key values? Another virtual column? Thank you!

Have a Wonderful Day! - Nick Barry RPC

That’s what the SUBSTITUTE() expression would give you. Use that expression as the App formula for whatever column you want to contain the list of all visit comments.

Ok thanks! I will try plugging this in later

1 Like

Hi! Everything almost works, but I am still having issues with the delimiters. I followed your steps. In the “DR Rows” virtual column, it is a List column, with a Ref, and a delimiter of “===”. Then, I changed the substitute formula like you said. It still only puts the commas, and does not even put in the === in the first place. Thanks!

Please post a screenshot of the virtual column configuration.

DR ROWS


SECOND Column

I actually ended up solving the issue. The separater was a " , ", and that is not a natural way to write a comma, so I have an action that Substitutes a ", " for " , " which lets me use the " , " as a delimiter, so it works now. Thanks!

3 Likes

Outstanding!

Hi @Steve
I have a similar case.
How can I concatenate information from related tables into a virtual column?
For example here I want to concatenate two dates from the REGISTRATION table.
Thanks!

USER

ID NAME PHONE VIRTUAL_COLUMN (CONCATENATED DATES)
**001** Daniel 123456789 30/05/2021 - 03/06/2021

REGISTRATION

ID CHECK_IN CHECK_OUT ID_USER (Ref)
X01 30/05/2021 03/06/2021 **001**
CONCATENATE(
  [Related REGISTRATIONs][CHECK_IN],
  " - ",
  [Related REGISTRATIONs][CHECK_OUT]
)
3 Likes

Thanks a lot @Steve
It works perfect!

1 Like