User End explanation: Our app tracks the Liters filled and Odometer reading at each fill-up. This means when the User specifies that they have filled a full tank, we can track the kilometers per liter that they have done up until they next fill their tank to full. This calculation ONLY works from when they fill a full tank, until the next time they they fill a full tank. (Hope that makes sense.)
Back-End explanation: So this is how I’d do it, if they select the boolean that says it was a full tank, we write the vehicle ID in transactions with tracking boolean checked like so -
We would then use a Query column in Vehicles like so:
And from there I can do the math etc we have that figured out.
The issue: Once they have now filled their tank to full for the second time, and we’ve told them so this is your KM/L since your last fill to full, we need to reset the tracking. AKA clear the value of the tracking booleans in all the rows for that Vehicle ID…
Solution needed: To clear multiple column values at once with a single button, OR a different solution that any one can think of.
I’m not following why you would need to clear the boolean in multiple columns.
I have a very similar app. In mine I have a boolean to track if a fuel tank was partially filled or if the prior fill was not recorded, so the opposite of what you are doing. I agree that you can’t determine fuel economy if the tank is not filled full. In my app, every time a tank is filled, I retrieve the previous odometer reading and write it into a new row with the current odometer reading and the number of gallons that were put into the tank. That way I have everything I need in one row to calculate fuel economy. If the boolean is checked then that means it was a partial fill or I forgot bro record the prior fill, so I don’t have enough information to calculate fuel economy. The boolean tells me not to do the calculation for that row, but I still have an odometer reading that I can later pass into the next new row the next time the tank is filled.
I guess what I’m getting at is that I don’t ever have to clear any booleans. I feel like that is something you would want to track and not erase. Each of my rows has all of the information it needs to calculate fuel economy in that single row, so I don’t need any queries or relations to look at other rows.
I think I would need more of an explanation about your setup to understand why you need to clear the boolean, or what it’s purpose is. It’s going to be a lot easier to change your setup than try to figure out how to clear a single column in multiple rows.
4 Likes
It’s possible using the API, but I agree with Jeff in that your approach seems overly complicated. So you’re probably better off simplifying your approach so that a multi-row reset isn’t necessary.
3 Likes
Yeah I didn’t think my way was the best but it was the only one I could come up with in the moment, I really like the idea you gave though. To implement I’m guessing something like:
I have a vehicles table, which is related into Users when that vehicle has a transaction. When the vehicle does a full fill for the first time, I set 3 column values in the REL Vehicle, with the Odometer reading, the Litres filled and the Date of fill with Look-up columns for them in the Users table. Every fill up done from that date(including that transaction) goes into Transactions table as normal. The next time a full fill is checked, we subtract the Odo reading at that time from our look-up of the Odo reading at the last time of full fill. We do a query to Transactions table for all transactions for that vehicle ID between the Date of fill we are looking-up from the last time of full fill, and the current date and time. Then do a roll-up of all the liters filled. Once that transaction is closed and our kilometers per liter is calculated, we now write the new date of last fill, the new odo reading, and the new liters filled into vehicles overwriting the previous.
I think that makes sense? Kind of just writing it for my own minds sake and a second opinion.
(Just realised don’t even need to record the liters filled in vehicles as it will already be in transactions)
