Concatenate Values from Row in Another Table in Virtual Column

[mykrobinson]

It's been a while since i last asked a question (yay), but here i am once again asking for help, just like old times 😂
I want to create a virtual column to concatenate some values from a row in a different table, and format them a certain way. i'll try to explain.

i added a button/behavior to "add dialysis schedule," and that action calls a LINKTOFORM() function to open up a dialysis schedule form, prefilling the patient name, timestamp, and user name, and allows the user to select a few options to note the patient's current dialysis schedule.

My first test wrote the data successfully to the sheet:



and of course the ref automatically created the Related virtual column.

I now want to make another virtual column that essentially takes the most recent timestamp for the given patient.

I can find the most recent by way of a MAXROW function:
MAXROW("Dialysis," "Timestamp", [_patientIdentifier]=[_THISROW])

but how would i wrap this in something to pull the most recent record and make a string like
CONCATENATE([STATUS], ", ", [Modality], ", ", [Company])

and so on?

I want to display the desired columns of the most recent row for the current patient. I feel like i may be approaching this wrong, or at least inefficiently.

[WillowMobileSys · Accepted solution]

Yes, that is a bit of a pain.  You cannot immediately dereference the row returned from MAXROW(), MINROW(), or any other function that returns the row reference as a result.  You will need to assign the MAXROW() to a separate VC and then use that column to dereference the row to get at the other values.

Another option is to concatenate the fields in that source table as a new column.  Then pull in that column with the already concatenated values into your VC using a SELECT() expression. 

[mykrobinson]

that is brilliant, i don't know how long i would have thought about this before considering making the concatenation INTHE DIALYSIS TABLE.....

Alas, my lack of experience is showing. 

Thank you.