How to pull a value from a table using a list of keys as a reference

[Jordan_Davis1]

I have the following tables: Opportunities, Videos, Songs, Requests. Each Opportunity has many Videos and each Video contains a Song. If someone makes a Request, it makes a new row in the Requests table which contains a Request ID, the Opportunity ID, ref values for the Videos, and ref values for the Songs.

I need to pull the Request ID into the corresponding rows of the Songs table so that I know "‘Hey Jude’ is included in Request 1 ". I tried the following expressions in a virtual column in the Songs table but nothing worked.

SELECT(Requests[Request ID],IN([Song ID], Requests[Songs])
SELECT(Requests[Request ID],IN([Song ID],SPLIT(Requests[Songs],",")
SELECT(Requests[Request ID,CONTAINS([Song ID],Requests[Songs])

Any ideas?

[Steve]

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Jordan_Davis1]

I’ve read both of these many times, this is a little different. Use the following example.

Requests Table
Request ID = 1, Opportunity ID = 123, Song ID’s = 1001,1002, 1003, 1004,1005

Songs Table
Song ID = 1001, Song Title = 'Hey Jude, Request ID = ?
Song ID = 1002, Song Title = ‘Thriller’, Request ID = ?
Song ID = 1003, Song Title = 'A Song Steve Wrote (Unreleased), Request ID = ?

The Request IDs are getting generated when users submit new requests. From the Songs table I need to say "for any requests that include my Song in the list of Song IDs, give me the Request ID. I can’t dereference from the songs table because I don’t have the reference Id yet. Instead, from the Requests table I’m using an action ‘Execute on action on a set of rows’ and I’m able to fire on the correct rows, but I’m not able to tell the action in the Songs table which Request ID to pull in.

[Steve · Accepted solution]

Ah.

FILTER(
  "Requests",
  IN([_THISROW].[Song ID], [Song IDs])
)

[Jordan_Davis1]

Steve:

FILTER(
  "Requests",
  IN([_THISROW].[Song ID], [Song IDs])
)

Ha, that worked. Not sure how this is different from first Select expression I mentioned but, as always, thanks Steve!