Expression for query

AppSheet AppSheet Q&A
1

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!

2

@Siger_Smit

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

OR

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

LeventK:

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

Wow, works like a charm. Thanks @LeventK

1 Like
4

@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!

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

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

7

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.

1 Like
8

@Siger_Smit
Can you try:

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

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)).

10

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

2 Likes