Getting virtual column count of using table key reference in another table enum list

[ComfortablyNumb]

Hi there,

Trying to associate the following (have a 'Events' table and a 'Players' table)

There are many events, and the 'Events' table consists of an enum list of [Players]

To be clear: 

Events[Players] (with [Players being an enum list of players added to a event)

I'm trying to add a virtual column in the Players data definition to be able to trawl all the Events and identify all the events they played. Tried the following with no luck:

SELECT(Events[Players], Players[Row ID]=[_THISROW])

Eventually, once I have the right command, I will create another column which is the sum of that total of events. Also need to put a filter on the select too at some stage (eg date range from 1/1/2023 to 31/12/2023)

Little help please from some experts, thank you.

Thank you.

[baba_sawane]

Hi,

I'm trying to add a virtual column in the Players data definition to be able to trawl all the Events and identify all the events they played. 

If you mean that you are in your Players table and want to identify every events each user played, you can try :

SELECT(Events[Players], contains([Players]=[_THISROW].[PlayerName])

Replace PlayerName with wathever is your column for the name.

For the filter : select(Events[Players], and([Players] = [_THISROW].[PlayerName],[startDate] = [_thisrow].[startfilterdate], [endDate] = [_thisrow].[endfilterdate]) ) 

____

Hope it helps ! If it's not that, can you detail what exactly is the filter you want in SELECT(Events[Players], Players[Row ID]=[_THISROW]) ?

[ComfortablyNumb]

Amazing reply thank you will give it a try 

All the best

[ComfortablyNumb]

Hmm close - the statement below returns all the players (not the event ids / complete related events?) - e.g. I want to display & interact with the events, present those identified events another table (on the player display page).

SELECT(Events[Players], contains([Players],[_THISROW]))

(That returns the Player ids (including an unrelated player:

eg. The player exists in two events.

4oJkc0UUss4nqVlVpvliYb , nR9rAR7WsUxQkiPxpHMxEK , (lets say event 3)

4oJkc0UUss4nqVlVpvliYb , nR9rAR7WsUxQkiPxpHMxEK (lets say event 5)

I want it to return the unique row ids of each event (or the event reference) for display.

eg

EYaVkKKeq94Bax8lBXwne8 (event 3)

uAtk1iuAA-4269H4kMlDGa (event 5)

[ComfortablyNumb]

Eg: (This nearly works, see screenshots)

Lets call this column RelatedEvents now..

RelatedEvents = SELECT(Events[Players], contains([Players],[_THISROW]))

COUNT([RelatedEvents]) <- That returns 4 (but should only be 2?!)

Maybe I haven't setup the enum list properly on the events table?

I wanted each event to have a enum list of pre-existing player references.

E.g. I can't add pre-existing players from the database to an event? (Creates a new related players ref). I think I need a proper many to many relationship?

[baba_sawane]

Okay so you want the referenced events of one player to be clickable so that you see the full record.

This is linked to a previous thread (if you want more details): https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Filtering-with-combining-lists/m-p/668061#M2303...

__

1. What you can do is creating in Players table an Enumlist column of base type text that i'll name [eventlist]. 

2. Then create a second column, virtual, set it as List of ref with the formula [eventlist], and reference to the table "Events".

3. In the enumlist, you can construct the formula to select  the list of rows, then concatenate them into text, with a formula like this  : 

concatenate(select(Events[eventskeycolumn],contains([Players],[_THISROW].[PlayerName])))

> returns the list of rows referenced by Events key in Events table, that contains in Players the actual Player name. 

The most important thing in the formula is the filter : in the event table, I guess the Players are an enumlist reference to your Player table. If so, use that same column to replace [PlayerName]. 

[ComfortablyNumb]

This worked?!

select(Events[Row ID],contains([Players],[_THISROW])

[ComfortablyNumb]

I've thought of another way to describe this, take this app for example

https://www.appsheet.com/templates/Basic-ParentChild-References?appGuidString=12b8ef4c-29a7-4e67-ae2...

It had two tables, - Department and Employees.

A Department can consist of many unique individual employees.

What I want is unique employees to be able to belong to multiple departments (and report on that).

Thanks

[ComfortablyNumb]

I found something that kind of does it (but I worry about ongoing efficiency)

Many to Many relationships:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Many-to-Many-Relationships/m-p/261816