Template lookup details from list of keys

[par]

In a table I have an EnumList column which is of type Ref to another table.
When I come to create an email template how do I parse the values in this EnumList to then return the values in the table to which it references.

My template looks like (also attached as image):

sessionID: <<[sessionID]>>
FK_InstIDCompletdBy: <<[FK_InstIDCompletdBy].[Name]>>
FK_LocID: <<[FK_LocID].[Name]>>
Date: <<[Date]>>
FK_InstID-SI: <<[FK_InstID-SI].[Name]>>
FK_InstID-FA: <<[FK_InstID-FA].[Name]>>

instSessionLog

instSessionID	FK_sessionID	FK_instIDCompletdBy	FK_groupIDs	FK_pupilIDs
<<Start: [Related instSessionLogs]>><<[instSessionID]>>	<<[FK_sessionID]>>	<<[FK_instIDCompletdBy].[Name]>>	<<[FK_groupIDs]>>	<<[FK_pupilIDs]>><>

The field [FK_pupilIDs] is a list of the primary key field in the pupils table pupils.pupilID
This will return a list of values such as 123456, 234567, 345678
I would like to display the value in the field pupils.pupilName (e.g., Fred, Bob, Will)

Any ideas on where to start would be appriciated.

Many thanks

Paul

[Rafael_ANEIC-PY]

Hello @Paul_Robson, welcome to the appsheet community !

That’s a very interesting question, how to turn a list of ref to a list of values from their respective table.

Try this:

CONCATENATE(SELECT(Pupil_table[Pupil_name], IN([Pupil_ID],[_THISROW].[Pupil_enumlist])))

Please do test this in a virtual column inside your instSessionLogs table and let me know if it works for you

[Chokri_AOUINA]

Rafael_ANEIC-PY:

CONCATENATE(SELECT(Pupil_table[Pupil_name], IN([Pupil_ID],[_THISROW].[Pupil_enumlist])))

Thanks a lot for this tip that i am looking for !

[par · Accepted solution]

Many thanks for your help.
The final solution didn’t require the dereference and so read:

<<CONCATENATE(SELECT(pupils[pupilName], IN([pupilID],[FK_pupilIDs])))>>