Hello! I will try to explain my situation as best I canโฆ
Table 1: Master Store List
Table 2: Daily Report
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!
Solved! Go to Solution.
CONCATENATE(
[Related REGISTRATIONs][CHECK_IN],
" - ",
[Related REGISTRATIONs][CHECK_OUT]
)
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.
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., "|"
).
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
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!
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]
)
Thanks a lot @Steve
It works perfect!
Is it possible to include a "Start... End" in this CONCATENATE???
Nope.
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |