List multiple column entries from parent table, based on key referenced by child table

Hi,

This is an app for detailing points in racket sports. I have a parent table for entering ‘Match’ level details into the ‘Match’ table. For every ‘Match’, there are multiple points which are populated in the ‘Points’ table.

In the ‘Match’ form, I input details of ‘You’,‘Partner’,'opponent1,‘opponent2’, all of which are populated in their own columns in the ‘Match table’. The child table ‘Points’ has columns of ‘Server’ and ‘Receiver’, for which I want to create buttons options from the multiple columns ‘You’,‘Partner’,'opponent1,‘opponent2’ of the specific ‘Match’ row of the parent ‘Match’ table.

Could you please advise a formula which I can enter in the ‘Server’ and ‘receiver’ child table columns, in order to make a list from the 4 columns of the parent ‘Match’.

I have explored various topics in the help section including column constraints but was not able to retrieve a list fro multiple columns. I was only able to retrieve 1 column for the key from the relevant parent table row by using:

Entered in the ‘server’ column of the child table:
select(Match[Opponent1], ([Key]=[_Thisrow].[Tourney]))
The above gives me just the value of the relevant ‘opponent’ column entry.

However I want to create a list for Match[You],Match[Partner],Match[opponent1],Match[opponent2], for the specific key of ‘Match’ table referenced by the ‘Tourney’ column of the current row in the ‘Points’ table.

How is it you are expecting this list to be formed?


If you have an appropriate Parent/Child relationship between the two tables where you have the Parent ID reference in the child table, then you can retrieve any Parent column using “dot” notation.

For example in your Points table the child, lets assume you have a column named “Match” that hold the ID of that parent Match row. you can then retrieve the 4 columns you mention like so:

[Match].[You]
[Match].[Partner]
[Match].[Opponent1]
[Match].[Opponent2]

If you literally want to create a list our fo these for some reason, you can use an expression like this:

LIST([Match].[You], [Match].[Partner], [Match].[Opponent1], [Match].[Opponent2])

I hope this helps!

4 Likes

This worked beautifully! I had been struggling with this for many hours.

Thank you so much for your prompt and precise explanation. You have certainly made my day, and the app ! :slight_smile:

To answer your question, the list would be used to create buttons in the child table “points” input form. For example, from the 4 players who are playing the match (this is a doubles format of the game), the server/receiver can be any one of these players for the different points. Once the players have been entered in the parent ‘Match’ table, I want only those relevant buttons to populate as options for the child ‘points’ input form. I am using the ‘enum’ column type with ‘buttons’.

For a different match, the opponents will change. So if the parent table is not referenced for that particular match only, then I would have many irrelevant buttons.

Your explanation has cleared my understanding of how the dot notation works in AppSheet!

3 Likes