Hello, I have two tables, Payments and Statements. I log payments, then later, once I pull them from the associated sites, I load the Statement. I’m trying to build an action that will set the StatementID on Payments to the Row ID of Statements.
My current attempt is a Statements action that executes an action on a set of rows, and that action sets StatementID to [_THISROW].[Row ID].
This doesn’t seem to be working, however.
Is there a way to do this? Or should I look more into a LOOKUP() of some sort, trying to find the Statement that matches the Payment?
Welcome to the AppSheet Community! I’ve looked into similar past queries and found a very helpful solution from one of our expert members. Please review the details here [Link] and let us know if this addresses your request.
Thank you! I ultimately ended up having an idea that was very similar, and with a little ChatGPT assist, got it working, but I looked at the post you linked, and yeah, I think that would have gotten me where I needed!
Saving that post, and I’m going to mark yours as the solution, because if I hadn’t gotten it myself, that post would have gotten me there.
The trick was using the LOOKUP(). I ended up using a
ANY(
SELECT(
Statements[Row ID],
AND(
[Debt_ID]=[_THISROW].[Debt ID],
[_THISROW].[Date] >= [Statement_Start],
[_THISROW].[Date] <= [Statement_End]
)
)
)
structure to assign the [Statement_ID] on Payments table. (“Action 2”)
Action 1 was a “execute an action on a set of rows”, with a Referenced Rows formula:
SELECT(
Payments[Row ID],
AND(
[Debt ID] = [_THISROW].[Debt_ID],
[Date] >= [_THISROW].[Statement_Start],
[Date] <= [_THISROW].[Statement_End],
ISBLANK([Statement_ID])
)
)