Expression syntax to join two tables to find MAX value

[David_Jones1]

Hi all. I’m struggling with the syntax of an expression to go in an app formula to automatically populate a value. It’s a simple membership database:

Table A - Members - details of each member. Includes a column to store the date they last paid their annual subscription

Table B - Subscriptions - records their subscription payments. 1 record for each time they pay. So there is a one to many relationship from members to subscriptions.

This is the app formula in the column Date Last Subs Paid column in the Members table which doesn’t show any errors except a warning that the expression may slow down sync times. But it just doesn’t produce a result in the Date Last Subs Paid column which remains blank.

MAX(SELECT(subscriptions[Date Paid], text(subscriptions[membershipnumber]) = text([membership number])))

Any help much appreciated.