I'm puzzling over what I hope is a fairly sim...

[David_Jones1]

I’m puzzling over what I hope is a fairly simple requirement.

I’m adding something to my app to allow drivers to check a vehicle out and back in.

So I have 2 tables, Vehicles and VehicleCheckSheet.

They are linked using Ref and Reverse Ref fields on vehicle registration number.

Each vehicle can have many check sheets.

A check sheet contains checkout date/time, checkin date/time, checkout mileage, checkin mileage, driver performing the checkout/in.

When a driver takes a vehicle from a depot they will complete a check sheet e.g. tyres, lights, mileage etc.

But I want the action of completing that check sheet to update the Status column in Vehicles .

The status can be Checked Out - DUTY, Checked Out - SERVICE, Checked Out - OTHER, Checked In.

So those values will be selected by the driver from a dropdown when they are completing the check sheet (I’ll be using some showif constraints e.g. Checked In will only be available if current status is one of the Checked Out statuses).

When the driver returns a vehicle they will check it in and record the current mileage, any defects and the location that they have checked the vehicle in to.

What I’m trying to give the fleet manager is a top level view of all vehicles where he can see the status of each vehicle, its location if it’s been checked in somewhere and the current mileage based on the most up to date check-in.

I hope that’s painted a good picture of what I’m trying to achieve.

The core question is how do I keep those vehicle level values (status, location, mileage) updated out of the act of completing a vehicle check sheet?

As always, any help offered will be much appreciated.

Cheers David

[RezaRaoofi]

You can add a Virtual Column to the Vehicles table with MAXROW() function in its formula; that should help you to get some latest updates from VehicleCheckSheet table; check this out for more details:

intercom.help - MAXROW() MAXROW() intercom.help

[David_Jones1]

Thanks @RezaRaoofi

Yes, I can see how based on timestamps I can get the latest vehicle check sheet for a given vehicle and therefore read the status, location and current mileage.

Might struggle with the actual query expression that goes in to the maxrow virtual columns that I put in the vehicle table but I’m sure I’ll work it out.

My data background was very SQL based and I sometimes struggle with spreadsheet style formula queries.

[RezaRaoofi]

@David_Jones If you have SQL background I suggest to play around with QUERY() formula in GoogleSheet too; it could be very useful; for example you can do all sort of summary reports in a separate sheet (tab), and add that as another table to your AppSheet app; that could save you additional calculations and formulas on the AppSheet side.