Well, I won’t dive too deep into the solution since it sounds like it wasn’t a good solution. I just know that the values need to be split on the decimal and that’s the only way I could think of to do it entirely within glide so the decimal would be separated to properly convert back into chains. I didn’t consider rounding in this case. Here’s a slight rework of the logic.
- Total is still the total as calculated from the rollup from the Haulage sheet.
- mth-MileageWhole is still the total value, but with a precision of 1 (whole number)
- tmp-MileageWhole is still the template column to strip the underlying decimal. Otherwise, using the math column would still include the decimal and defeat the whole purpose of trying to get the whole number.
- mth-MileageWhole is where I veered off from the first attempt. This is just subtracting 1 from the mileage whole number in anticipation if it was rounded up due to the decimal. This will make sense later.
- mth-TotalMinusMileageWhole is used to subtract the whole number mileage from the total withe the decimal value. Here it is also a negative number because the whole number rounded up. This will also make sense below.
- if-MileageNotRoundedUp is the key to determining if the whole number was rounded up or not. If the total minus the mileage was a negative number, then we know that the whole number was rounded up, so we will return the mth-MileageWholeMinusOne column value. If the value was zero or above zero then we use the template mileage whole number.
- mth-Chains is the same as before, where we take the determined Mileage whole number determined in the IF statement above and then subtract it from the Total value, which gives us the Chains in decimal form. Then we multiply by 80 to get the Chains in Whole number form.
- Finally we create a template column to make it look pretty.
This could have all been done with the sheet and with less columns, but it involves Arrayformulas(which have their own rules) and a SPLIT, SUMIF, VLOOKUP, and all kinds of other fun along with being laggy since the calculated values would need to resync to the app. If would probably be harder to explain all the ins and outs if you aren’t overly familiar with sheet formulas.
Nothing is jumping out to me as far as your templates and the setup of the relation. I would double check that you are grabbing the correct columns and that the templates do in fact contain matching values. I can’t tell for sure in your screenshots…unless you’ve already solved this based on one of your previous posts.