Hi All, i need help please. I am building a trips section of my vehicle mileage app. I user is assigned 60 points at the start of each day for good driving behaviour. If they drive well and no points are deducted for the day they bank the 60 points. If they have any points deducted after each trip then the banked balance for the day is reduced by for example 4 points for mild speeding. So they only bank 56 points on that day.
What I need help with his how to do deduct the total deducted points from all the trips for the day from the daily 60 points and then show the banked points balance?
In your Trips table, create a Query column that targets the same table, and uses the following filters:
– Date is within This Row → Date
– User is This Row → User
Create a Rollup column that targets the Query column and takes a sum of the Points Deducted column
Use a Math column to subtract the above from 60.
The result is the number of points to be banked for each day.
The last step I need is to sum up the total daily banked points from the trips table and add it to the Monthly Reports table. Would I use a query column for that as well?
Would it be best to send a link to join as an admin as the table is very long with other data and i am not sure exactly the data you need to see?
This is the column i need to get the total for. The driver gets a maxmium of 1800 drive points for the month. So his total is banked points less his deducted points.
Okay, so you should do this in your Users table, as your Users table contains one row per user.
Now, as your Trips table will contain multiple entries per user per day, you cannot rollup the daily totals directly because you will have duplicate totals. So you need a way to rollup the unique daily Banked Points for each user. Here is what I recommend:
Start by adding a RowID column to your Trips table, if you don’t already have one.
Next create a Math column that will take the Trip Date for each row and convert it to a number in YYYYMM format. Use the following formula:
Year(Date) * 10^2 + Month(Date)
Now create a Single Value column that takes the first RowID from the Query column that you created earlier.
Now an if-then-else column:
– If Single Value RowID is RowID, then Daily Banked Points.
– Else 0
Now create a Math column in your Users table that uses the same formula as above, but use the Now value as a replacement.
And next a Query column in your Users table that targets your Trips table, with the following filters:
– User is This Row → User
– MathDate is This Row → Math Date
Finally, a Rollup column in your Users table that targets the Query column and takes a sum of the above if-then-else column.
If you do all the above correctly, the last column should give you a sum of the daily banked points for each user for the current month.
I believe i have done all the steps as you instructed and I am getting a total. However the total is incorrect. As you can see from the screenshot the value in the ifSingle column is taking the total points from the first trip of that day and not from the trip that had the deduction, as the banked points for the day.
The daily banked points should have a value in every row, so that shouldn’t matter.
Sounds like you have set up the calculation for daily banked points slightly differently from what I suggested.
Thanks I have found the issue. I had selected a different column instead of the query column for the deducted points rollup. It’s working correctly now.
