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

[retailpartnerco]

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!

[retailpartnerco]

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.

[Steve]

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.

[retailpartnerco]

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!

[Steve]

retailpartnercom:

In my new virtual column, it reads as only the row key

That is the intention.

retailpartnercom:

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.

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., "|").

[retailpartnerco]

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

[Steve]

retailpartnercom:

How do I get the visit details out of the row key values?

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.

[retailpartnerco]

Ok thanks! I will try plugging this in later

[retailpartnerco]

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!

[Steve]

Please post a screenshot of the virtual column configuration.

[retailpartnerco]

DR ROWS

SECOND Column

[retailpartnerco]

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!

[Steve]

Outstanding!

[daniel5905]

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**

[Steve · Accepted solution]

CONCATENATE(
  [Related REGISTRATIONs][CHECK_IN],
  " - ",
  [Related REGISTRATIONs][CHECK_OUT]
)

[daniel5905]

Thanks a lot @Steve
It works perfect!

[MarceloB]

Is it possible to include a "Start... End" in this CONCATENATE???

[Steve]

Nope.