Hi Jeff,
I am going to work backwards with my responses to your points…
I might need screenshots to see what you mean, but this may be related to not using a relation properly from the unit to the haulage sheet.
Below you will see a few screenshots showing my relation. Pic 1 shows the screen with a relation in place linking the User Profile Template column on this sheet to the User Email Template in the Haulage tab but as you will see the results I am getting are basically adding up all mileage for all units and putting the result of that into each row
Pic 2 below shows the same as pic 1 but with the configuration showing. I suspect I am doing something wrong with the relation which is meaning the unit number and the mileage are not linking.
As always, I have had a play around with different variables to see if I can find a solution to the issue and pic 3 shows the closest I have come. Linking the Number column in this tab to the Traction column (this is the column where unit numbers are entered in the haulage form) of the Haulage tab gives the correct outcome in both the Rollup column and the If/Then column - unfortunately this way doesn’t differentiate between users so it shows the total haulage for all users and not just for the logged in user.
For each of the above points please take note that I have used the miles only detail for the rollup due to the issues with the decimal point in the rollup.
I was really hoping this would have worked. I’m guessing since the column is not recognize as a boolean column with a checkbox visible in the data editor, then it’s just writing the output value instead. It’s nothing you did wrong. An alternative would be use emoji instead of the text ‘true/false’. So you could use someting like this https://emojipedia.org/check-mark-button/ for True and https://emojipedia.org/white-large-square/ for False.
The suggestion of the emoji checkboxes worked a treat so a sound alternative. Is there no way of getting this to work with a switch as it would maintain the continuity of my layout a little bit better. Not a major thing if not!
How would you want to display the resulting value in the rollup? I ask this because 1 mile and 55 chains would not be the same as a total of 1.55. It would actually be 1.6875 miles. Assuming you would want to total up the equivalent decimal form of miles and chains, then instead using a template to join the miles and chains together, you could do a math column instead. We would have to do a little conversion to the decimal equivalent of 80 chains equaling 100 percent of 1 mile. In the math column, the formula would be something like this (miles + chains/80). This should give you a numeric value that you can use for SUM in the rollup. I’m not sure to to split that sum back into miles and chains without doing some magic in the spreadsheet itself.
I’m glad you have brought this up now as it was undoubtedly going to be a question for the future for me to be asking. You are indeed correct that I will require 80 chains to equal 100% of 1 mile - as an example I will need the calculations to work as below:
350101 - user inputs 1 mile 22 chains for one journey and then 2 miles 60 chains for the second journey. Adding these together as a standard decimal would obviously create a total of 3.82 miles where actually I would need it to show 4.02 miles.
The entry of individual records won’t be an issue as anyone inputting into the Add Haulage Form will only ever input from 00-79 in the chains section so this is a greater issue with the totalling up of miles and chains for each individual unit as detailed in the example above. In my Google Spreadsheet, the total mileage for each unit is worked out using the following formula:
=ARRAYFORMULA(SUM(QUOTIENT(IFERROR(FILTER(Haulage!$J$3:J, Haulage!$C$3:C=C3), 0), 1))+ QUOTIENT(SUM(MOD(IFERROR(FILTER(Haulage!$J$3:J, Haulage!$C$3:C=C3), 0), 1)), 0.8)+ MOD(SUM(MOD(IFERROR(FILTER(Haulage!$J$3:J, Haulage!$C$3:C=C3), 0), 1)), 0.8))
which basically adds up the decimal totals but treats .80 as the 100% point of each mile.
So with regards to this point - yes I do need the totalling up for each unit to reflect the fact that .80 is equal to 1 whilst also totalling up the mileage per unit for each individual user of the app.
I hope all of this makes sense
Thanks again for your help