Nested Expression Assistance

[mykrobinson]

I need some help either correcting this nested expression, or possibly approaching what i am trying to do in a different way.

I am creating an app to help our billing department with tracking issues they are having with claims and/or the billing platform.

I have two primary tables, one to track the billing issue (billing_issues) , and a secondary table (issue_updates) to track comments or updates to the issue, with a ref back to the key of the issue. When adding an update to an issue, there is an option to mark this update as the resolution to the issue. I have an action that sets the primary issue as resolved if an update is added and the update is marked as the resolution to the issue.

I have a virtual column on the billing_issues table called Resolution and i would like to craft an expression to search the issues_updates table to find the row that has the same record (key for the primary billing issue record) and also has the completed column marked as yes, then grab the resolution  column value for that row.

This is what i currently have, but it is not passing validation. Trying to figure out what i have done wrong here.

LOOKUP(
FILTER(
"issue_updates", AND(
[record]=[_THISROW].[record],
[Completed]=yes
))
,"issue_updates","record" "Resolution")

[mykrobinson]

here's something else i tried, which passed the syntax check, but is not returning what i am expecting. I am just getting a blank field 🤔

text(SELECT(issue_updates[Resolution],AND([Completed]=yes,[record]=[_THISROW].[record])))

[mykrobinson]

another approach that passes syntax validation but does not produce actual results:

LOOKUP(
AND([record]=[_thisrow].[record],[Completed]=yes),
issue_updates,record,resolution)

[mykrobinson · Accepted solution]

UPDATE:

I got it 😁

SELECT(issue_updates[Resolution],
AND(
[record]=[_THISROW].[record],
[Completed]=TRUE)
)