Max Date from Related Records

[Andrew_Ford]

Hello Appsheet Community,

I have table sites and table nurseLog. Each row in sites is related to many in nurseLog. nurseLog has a column visitDateTime. I am trying to add a virtual column to table sites that returns the most recent visitDateTime from related records in nurseLog. The column siteID in nurseLog is a ref to the key column UID in table sites.

To put contextually, we have nurses that input logs for their sites. I need to grab the date of the most recent log for each site.

I’ve tried using MAX() and MAXROW() but can’t quite get it to work. The below sorta works but returns the key column of table nurseLog instead of the visitDateTime

LIST(MAXROW(“nurseLog”, “visitDateTime”, ([siteID] = [UID])))

Any help would be much appreciated.

[Steve (Steve Coile)]

Maybe this?

MAX([Related nurseLogs][visitDateTime])

[Andrew_Ford]

That worked when I wrapped it in a list(). Which your guide said but for some reason I was putting list() within MAX instead of outside.

LIST(MAX([Related nurseLogs][visitDateTime]))

Thank you as always, Steve!

[David_Jones]

Hope you don’t mind me jumping in on this with a similar problem. But mine involves a numeric column rather than date.

I have two tables - VEHICLES and VEHICLECHECKS and they’re joined by REG NUMBER
1 VEHICLES record will have many VEHICLECHECKS records. A record is created each time a vehicle is checked out or back in. When a vehicle is checked in, its current mileage is recorded.
I’m trying to get that mileage figure in to a current mileage column in the related VEHICLE record.

I have a virtual column on VEHICLES called MILEAGE AT LATEST CHECKIN
It has an app formula of MAX([Related VehicleChecks][Mileage In])
Then in the real CURRENT MILEAGE column in the VEHICLES table I have the formula =[Mileage At Latest Check-In]

But this doesn’t work and the CURRENT MILEAGE column is remaining as zero.

I tried the suggestion of enclosing the formula in LIST() but that produced the error “The expression is valid but its result type ‘List’ is not one of the expected types: Number”

**** UPDATE **** I was continuing to experiment and have realised that in the background the MAX function is actually working and it is evaluating to the correct max mileage figure for that vehicle. But I only see it if I edit the vehicle record. SO what do I need to do to make sure that this value updates correctly without me having to edit the vehicle record?

Any help gratefully received.

[Steve (Steve Coile)]

A non-virtual column with an App formula will only be reevaluated when the row is modified in some other way, such as when the row is edited in a form, or modified by an action. A virtual column value is updated every time the app syncs, but virtual column updates can be very expensive and can greatly increase your sync time.

In your case, if you’ve just added this non-virtual auto-compute column, existing rows will not get the computed value until and unless the rows get updated in some way. New rows, though, will get the computd value when created.

[David_Jones]

Thanks Steve. I’ve now got the virtual column in a data slice so that the vehicle details view gets the up to date mileage. But I really do need the underlying record in the spreadsheet to have the accurate figure as it’s used in reporting and dashboards outside of Appsheet. I’m really surprised that it’s not possible to fire off something that will properly update the underlying record without having to go in and edit every vehicle record.