Tips to improve my prototype app

Also, in my initial setup (before I needed to covert it to a multiple user friendly app) I had the checkboxes in the ‘Travelled In’ section setup with a formula in my spreadsheet which meant that if the relevant carriage number next to the checkbox was input into the Add Haulage form, the checkbox would automatically be ticked.

I would like this function to remain but obviously would need to it to be setup to suit the specific user. I’ve been having a play around with relations and templates to see if I could get it to work but have failed (as normal - although I did solve an emoji problem which I have now made user specific so I do solve the odd thing by myself :laughing: )

Thanks

Dividing by 80.5 wouldn’t be a viable solution. It might work for some numbers, but with rounding it would bite you in other places. That’s where I mentioned I was stuck with the decimal form of the mileage and chains. At the time I couldn’t come of with a way to mathematically convert the total back to miles and chains within glide and you would need to use forumulas in the sheet and then split the mileage and chains on the decimal so you could calculate the decimal separately back to chains…but I have an idea that might work. It’s a little messy, but it keeps everything with glide.

Assuming you already have the total figured out in the decimal format, here’s what I came up with. It’s a little weird because of how glide handles a math column with precision set (not sure if this is a bug, but its been mentioned here: Ugly Date in If-then-else). The underlying value doesn’t show the values as a whole number, so I had to create a template column to fully convert the math column into a whole number to be used to calculate the chains separately. (Confused yet?)
image

  • The first total column is the total you have already calculated with the mileage + (chains/80) formula.
  • The second column is a math column with precision set to a whole number so we can separate the mileage from the chains.
  • The third column is a template column to make sure the math column is fully converted to a whole number (This feels like a glide bug that I shouldn’t have to do). Otherwise I couldn’t subtract the whole number from the total to separate out the chains decimal value.
    image
  • The forth column is a math column that subracts the mileage template column whole number from the total to give us only the decimal value. This is then multiplied by 80 to convert the decimal value back to chains as a whole number. (Technically this converted back to 2.4 chains, but with rounding as a whole number it gives us 2 which is what I think you are looking for.)
  • At this point you have the 2 separate mileage and chains columns if you choose to display them separately, but the 5th column is there to demonstrate that you can use a template to display the mileage and chains as a decimal by joining the 2 values with a decimal point in between, or you could write it out in sentence format like this: “You traveled xxx miles and yyy chains”. I think the sentence format would make more sense to the user. A simpler format could be “xxx miles | yyy chains”. It’s all up to you.
    image

For you second issue with the checkboxes, do you want the Travelled In checkboxes to be clickable or would they only be set from the haulage form? I would maybe consider a similar setup to the haulage checkbox where you would use an if/then column to determine which emoji to display instead. What I would do is create a template column on your unit sheet that would join the signed in user’s email from the user profile and the unit number together. Create the same template on the haulage sheet, but with the unit and email submitted from the form. Then, using the template columns, create a relation column to link the unit sheet to the haulage sheet template column. The relation will return something only when the signed in user has the matching unit and email in the haulage sheet. Then based on that, you can create an if/then column, which would return a checkbox :white_check_mark: if the relation is not empty, else return :white_large_square: if the relation returns nothing. You can display this emoji checkbox in your unit list details by itself, or use a template to join it to the unit number and display that template in the list instead.

2 Likes

Hi Jeff,

I don’t have much to say about the your work-through for the miles and chains issue I was having - except that what you have come up with is genius and works a treat :smiley: If you have the time and desire to do so, I would love to hear how you reached that solution :slight_smile:

Onto the second part of your response and I might be wrong but I’m thinking we may be slightly at cross purposes with regards to what I am looking for - forgive me if that isn’t the case but here is a breakdown of what I am looking for:

Each of the units in question are made up of 4 carriages and each of these carriages carry separate numbers to the main unit number. As an example 350101 is made up of carriages 63761, 66811, 66861 and 63711 and it is these carriage numbers which are listed in the ‘Travelled In’ section of my app.

Basically, when (as an example) a specific user enters 350101 as the unit number in the Add Haulage Form and then 63761 as the carriage, I would want a checkbox next to 63761 to be activated.

I thought that to do this I could adapt what you had suggested but to include the carriage numbers as well.
So in my main fleet screen, I set up a template that joins the user sign up email, the unit number and the carriage 1 number as shown below

Then in my Haulage sheet, I set up a template which joins the email taken from the Add Haulage form, the unit number and the carriage number as shown below

I then returned back to the unit sheet and set up a relation to join these 2 templates together as shown below

I had become some sort of Glide expert by this point and was next going to set up and if/then column and include the emoji you have kindly supplied previously but as you will see this failed to return anything in the relation column where it should have done so in a number of rows. I have checked the information and that the detail in both template columns match and can’t see where I am going wrong.

So my questions to you would be, 1) is the above approach (ie setting up templates and relations for each individual carriage number) the best way? and 2) if it is, where am I going wrong with my template and relation in the example screenshots above?

Again, thanks very much for your help with the miles and chain issue and for your ongoing support.

Matt

1 Like

Have to say everytime I come back to this thread I’m humbled to read everything you guys write about here. Learn something new everytime.

1 Like

Not half as much as I am learning - it has been most enjoyable to learn all of these new things and I am indebted to @Jeff_Hager for all of this help - and of course to yourself for your contributions.

Despite my continual questions for Jeff, I am picking plenty up as well and I have managed to solve an issue today which required me to setup templates, relations, if/then and rollups and it is safe to say that none of this meant much to me a few weeks back - thanks to the support within this community I am now feeling a lot more confident with all of the jargon and what each function can offer me and most pleasing perhaps, I am nearing the completion of my prototype app :smiley:

1 Like

Jeff,

Apologies to throw this back at you having asked another question so recently but having started a bit of heavier testing on the miles & chains I have hit a bit of a snag which I think relates to the below info with regards to the second column you detailed in the post below:

The second column where we, in essence, extract the mileage away from the chains works fine until the value in the total column (which is the one produced with the mileage + (chains/80) ) is .50 or above as this then rounds up the mileage number in the second column. As an example for you I have input 23 miles and 42 chains into my add haulage form and this has then converted to 23.53 in my total column (thanks to the mileage + (chains/80) formula). Unfortunately in the second column which you labelled “mathmileage” in your example above, the value I receive is 24 because (I assume) it is rounding up 23.53 to 24 and then because of this the formula in the maths column for the chain is churning out an incorrect result - in this case a negative figure as shown in the screenshot below

(please note that ‘Mileage Maths’ is my equivalent to the ‘total’ column in your example)

And obviously this produces a skewed result in the app.

If something jumps out at you straight away with regards to how to fix this then great but if it is a problematic issue with no obvious fix, I don’t want you to spend loads of time on it and I will consider reverting to the metric results (as per the total column in your example) and see what feedback I get on that - maybe this is a lot of effort just to create an overly specific set of results, I don’t know?

Thanks

Matt

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.
    image
  • 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.
    image
  • 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.
    image

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.