Expression for query

[Siger_Smit]

Hi all,
I am building an app for artist to manage their work. So I have a table Artist and a table Track. Artists connect to a track with a EnumList and a Ref base.
I am not that good with code… (It would make Appsheet more No-code if there was a Visual Expression builder to guide you, step by step in building an expression… )

With which expression can I show all related tracks in a Artistview?
Something like; go to the Tracktable > Show all Tracks where Artist = this Artist

Thanks!

[LeventK]

@Siger_Smit

SELECT(Tracktable[Key Column],[Artist]=[_THISROW].[Artist])

OR

SELECT(Tracktable[Key Column],[Artist]="Metallica")

help.appsheet.com

SELECT()

Gather column values from row in table

help.appsheet.com

FILTER()

Select rows of table or slice

[Siger_Smit]

LeventK:

SELECT(Tracktable[Key Column],[Artist]=[_THISROW].[Artist]

Wow, works like a charm. Thanks @LeventK

[Siger_Smit]

@LeventK This doesn’t seem to work if in the Table Tracks the Column Artist is set to EnumList with a ValidIf (Artists[Artist name]).
With Artist set to Ref it works. Is there a way to get it to work with a EnumList?
Thanks!

[Steve]

FILTER("Artists", IN([Artist name], [_THISROW].[Artist]))

[Siger_Smit]

Hi @Steve That didn’t work either: Error in expression ‘[Artist name].[Artist]’ : Unable to find column ‘Artist’.

[Siger_Smit]

OK, maybe I am not clear enough I have a Table Tracks With columns Titel(text), Artist(EnumList) with Validif.
I have a Table Artists with column Artist name.
With a Virtual column I want to show below the Artist name, all tracks associated with Artist name…
When using Levents SELECT it only works when setting Artist to Ref.

[LeventK]

@Siger_Smit
Can you try:

SELECT(Tracks[KeyColumn],IN([_THISROW].[Artist name],[Artist]))

[Steve · Accepted solution]

Create a virtual column for the Artists table with an app formula of:

FILTER("Tracks", IN([_THISROW], [Artist]))

1. FILTER("Tracks", ...) gathers key column values from the Tracks table from rows that match the selection criteria (..., see (2)). Equivalent to SELECT(Tracks[KeyColumn], ...) where KeyColumn is the name of the key column of the Tracks table.

2. IN([_THISROW], [Artist]) selects only those rows of Tracks (as per (1)) where the key column value of the current row of the Artists table ([_THISROW]) occurs in the EnumList of Ref to Artists contained in the Artist column of the Tracks table (per (1)).

[Siger_Smit]

Yes, @Steve, this expression works! Thanks @LeventK and @Steve for helping me out!