I have an app enables a user to keep a record of the trips they take with their vehicle. I have a rollup column that keeps track of the latest odometer reading. When a user creates a new trip the latest odometer reading is prefilled in the start odometer field. Then they just need to complete the end date and time of the trip as well as the end odometer reading.
This all works fine when you have one vehicle loaded int he app. As soon as you load a new vehicle and use that for the trip it still using the latest odometer reading from the other vehicle.
My question is how do I go about filtering the odometer reading so each vehicle has uses it’s own odometer reading?
You should be able to use a Query column for that.
If you’re not sure how to set it up, please provide a screen shot of your tables and we can guide you.
As you can see from the screenshots, the table column is using a roleup of the end trip odometer reading set to look for the maximum value. I need this to be separate for each vehicle.
Do you have a column in your Trips table that records the Vehicle used for each trip? (I assume there is one, but I can’t see it).
Assuming that you do, then a multi-relation plus a rollup should do the trick.
Create a multiple relation column in your Vehicles table that matches each Vehicle in the Trips table.
Then use a rollup on that relation. It should give you the max odometer reading for each Vehicle.
Ok, I have done that. The data is in the vehicles table though. Would I then use a lookup column in the trips to get that rollup value so it can be the default value in the trips add form? It seems that no matter which vehicle I select it still shows the maximum odometer reading or just taking the odometer reading form the first row?
I added a trip with a distance of 20km. When I go to add another trip for the Tiggo it still shows the 127238km odometer reading of the X-Trail.
The first approach would be to change your flow slightly. Instead of adding trips from a screen attached to your Trips table, start by presenting a list of Vehicles. Then select a vehicle and add a trip from there. Your screen would be attached to the row for that Vehicle, and so you would be able to pass the Max Odometer as a screen value with the form (as well as the selected Vehicle).
The other approach would be to switch to a custom form. This way you could write the selected vehicle to a user specific column, then use that to build a relation to the Vehicles table and fetch the Max Odometer value via a Lookup.
I’d recommend the first approach, as it’s much simpler and it won’t incur any extra updates (which is a downside of custom forms).
