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.
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.
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:
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
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.
@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.
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.
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])
@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
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:
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.
