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.

[Suvrutt_Gurjar]

Hi @David_Jones,

Please explore if following helps.
If the Members table is referenced in the Subscriptions tables, then you may have a system created VC labled something like [Related Subscriptons] in the members table.

In that case the expression something like below will give you the latest subscription paid date in another VC [Date Last Subs Paid] in the Members table.

MAX( [Related Subscriptons] [Date Paid])

Here [Date Paid] is the subscription payment column in the Subscriptions table.

[WillowMobileSys]

David_Jones:

text(subscriptions[membershipnumber]) = text([membership number])))

This portion of your SELECT is comparing a list to a single value. It doesn’t report an error because you have wrapped both with a TEXT function (it probably did before you added it ).

I think you may want to use the IN() function instead:

IN([membershipnumer], subscriptions[membershipnumber])

Your final expression would be:

MAX(SELECT(subscriptions[Date Paid], IN([membershipnumer], subscriptions[membershipnumber])))

[David_Jones1]

Hi John. Thanks for the response. I’ve tried your suggestion and I am at least getting a date value through now. Unfortunately the MAX date being returned is for the whole subscriptions table regardless of the membership number i.e. there is a record in subscriptions dated 3/10/19 for member 253. But if I edit my own membership record with membership number 134 then it picks up the date 3/10/19 which is for member 253. I tried editing other records and always end up with 3/10/19

[WillowMobileSys]

Yes. Sorry about that! I didn’t think it through was just replacing what you had that wasn’t working.

@1minManager 's expression was correct. I assume that’s what you ended up using?

Well, all is not lost, at least you learned something different!!

[1minManager]

Not sure how the whole text() thing fits into this but you could use this in a virtual column of Members[Date Last Subs Paid]

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

Though its probably more elegant to create a virtual column of LIST type which looks at [Related Subscriptons] like @Suvrutt_Gurjar said

Simon@1minManager.com

[David_Jones1]

Hi Simon. Many thanks for the response. That seems to have worked although I didn’t need to use it in a virtual column. I’ve just added it to the actual column and when I edit the member record it’s picking up the correct value. All I need to do now is figure out if I can kick off a workflow that will update the value in the membership record when a new record is added to subscriptions without having to edit the membership record to trigger the app formula.

[1minManager]

@David_Jones the automatic updating you describe is exactly what a virtual column does. VCs recalcuate on every sync

[Oladipupo_Ologu]

@David_Jones @1minManager @Suvrutt_Gurjar
Hi David
Kindly share the exact expression you inputted in the Actual Column that worked for the MAX query.
I am having a similar issue but I am getting 0 results.
I tried these expressions
max(select([Related Enumerate Buildings][Number of Floors],[enumerate_id]=[_thisrow].[id]))
max([Related Enumerate Buildings][Number of Floors])

Like your example, I have a premise with multiple buildings, What I want to arrive at is the height of the tallest building on a premise.

Many thanks in advance.

[1minManager]

Hi @Oladipupo_Ologun

Please detail your tables & heirarchy. Also the relevant columns in each

Send screenshots if this is easier

Simon@1minManager.com

[Oladipupo_Ologu]

Hi @1minManager
See table information, same sent to your mailbox.
Thank you

[1minManager]

Hi @Oladipupo_Ologun

So create a virtual column in the Enumerate Table called [Max Floors]. The formula could then be either:

MAX(Select(Enumerate Building[Number of Floors],[Enumerate_ID]=[_ThisRow].[A]))
where A = the Enumerate table key column

Or

Max(Select([Related Enumerate Buildings][Number of Floors]))

Either should work the later is more elegant and a bit more efficent

But I’ve also spoted a potential cause of why your origional formulas might be failing. You’ve got Enumerate Building[Number of floors] set as an ENUM. The App maybe reading this as text. In which case Max(Select(… possibly won’t work correctly. Trying changing [Number of floors] to a NUMBER type column with a min of 1 and max to be set by you.

Hope this helps

Simon@1minManager.com

[Oladipupo_Ologu]

Thank you so much @1minManager.

This worked as last.
MAX(Select(Enumerate Building[Number of Floors],[Enumerate_ID]=[_ThisRow].[id]))
where A = the Enumerate table key colmn = id

I had this formula all along but the issue was the Enum type for Number of floors in Enumerate Building Table.
I chose enum in order to minimize errors in the user’s entry.
However, your suggestion worked.

I set Number of Floors to NUMBER Column Type and then select RANGE display mode which helps to achieve the same result ie to minimize manual entry by users.

However, I applied this formula to column called Total Building Footprint in Enumerate table
(sum(SELECT(Enumerate building[Building_Total], [enumerate_id] = [_THISROW].[id]))) the answer but when [Number of Floors] or [Building Footprint/Area Sq. m.] is modified, it doesnt recalculate Total Building Footprint

In Building_Total (Number), the app formula (virtual column) =
IF(
([Building Footprint/Area Sq. m.]*[Number of Floors])<50,
0,
[Building Footprint/Area Sq. m.]*[Number of Floors])

Thanks in advance

[1minManager]

Ok, the syntax of your formula is wrong. Write what you want it to do in words

Simon@1minManager.com

[Oladipupo_Ologu]

@1minManager
Total Building Footprints should multiple each building footprints by its number of floors, if answer is less than 50, it should not be added to the rest of the building info.
Building A, 20 x 2 = 40
Building B, 330 x1 = 330

Total Building footprint answer should be 330.

However, when Building A is 30 x 2 = 60
Total Building Footprints = 390

----ref to my last question, However, I applied this formula to column called Total Building Footprint in Enumerate table
sum(select([Related Enumerate Buildings][Building_total],[enumerate_id]=[_thisrow].[id])) and i got the answer

but when [Number of Floors] or [Building Footprint/Area Sq. m.] is modified, it doesn’t recalculate Total Building Footprint

[1minManager]

Right so you need a virtual column in the Enumerate Table called [Total Building Footprints] so that if you update or add any any items to the Enumerate Building table this will automatically recalculate, after a sync. Its formula should be:

SUM(Select(Enumerate Building[Building Total],AND(
[Building Total]>=50,
[Enumerate_ID]=[_ThisRow].[id])
))

Enumerate Building[Building Total] needs an App formula of

[Building Footprint/Area Sq. m.]*[Number of Floors]

It doesn’t need to be a virtual column. But it does need to be a number.

Hope this helps

Simon@1minManager.com

[Oladipupo_Ologu]

@1minManager
Thank you for your help. It worked and but it does not update whenever information Enumerate Building(Building_total) is updated.

[Steve]

1minManager:

[…] this will automatically recalculate, after a sync.

“After a sync.”

[Oladipupo_Ologu]

@1minManager
it is not updating and I have an actual column for the expression.
Could that be the reason?
Or changing to virtual column is best?

[Steve]

A normal, non-virtual column will only update when its row is modified (e.g., when edited in a form). A virtual column is updated each time the app syncs.

[Oladipupo_Ologu]

@Steve
In this particular case, the row information is edited including the related tables and yet no recalculation in the particular column. Other columns on the same rows get updated when edited.

[Steve]

In order to recalculate a normal, non-virtual column value each time the row is updated, the column must be configured in EITHER BUT NOT BOTH of the following ways:

1. Set the column’s App formula to the expression to be evaluated each time the row is updated.

OR

2. Set the column’s Initial value to the expression to be evaluated each time the row is updated AND set the Reset on edit property to ON.