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.

1 Like

Hi Jeff,

Once again your efforts have succeeded. With regards to the rounding up issue on the mileage, I knew I needed to create a scenario where I was able to get the data to a -1 where necessary but I never got close to the solution you came up with, I won’t lie! I now have a mileage and chains system that offers a level of precision that I’ve not seen on any other apps/websites that offer similar.

The 2nd part of your response pleased me (despite the issue still being there at the time of me reading your response) because it was nice to see that I had created templates/relations in the correct manner - a definite positive sign that and an indication of what I have learnt during your period of help for me. I spent a solid amount of time checking and double checking my data was correct and I still couldn’t see anything amiss. So, I decided to head down the Glide equivalent of ‘switch it off and on’ and simply deleted everything relating to the templates and relation I was looking to set up and when I re-entered the information, it worked perfectly. Not sure what causes it but it’s the 2nd time this process has fixed a problem for me.

I am probably in a position to get the prototype out to a few people to get some initial feedback but I also have a couple of things I have been looking at which will become a requirement going forward.

  1. My prototype revolves around one class of train but once things progress there will be multiple different classes which will all be set up in the same way - a ‘home’ screen with a button leading to the fleet detail. However, I would want a front screen which would list each of the classes and a click on any of the classes would then take you to the ‘home’ page for that particular class. I guess this can be done quite easily with buttons but I’m wondering if Inline Lists may work as I think it would be nice if I used the ‘details’ and ‘caption’ part of the inline list to show some data relating to the class before the user has clicked on it?

  2. This actually goes back to my opening post when I asked:

And you will be pleased to know this is the final question and relates to the “Amendments” button towards the bottom of the units page. This is basically a similar issue to that of Q3 - in this case, when I click on the ‘Amendments’ button I would only want to see amendments for that particular unit but at the moment it brings up amendments for all units - sorry to keep repeating this but is this possible? This one is not a major issue if it isn’t possible as you will see I have already implemented a backup option below the ‘Amendments’ button - I just thought too much on one screen would look a bit clustered so a button leading off to a new screen would tidy things up somewhat.

And your response was:

Unfortunately there isn’t a good way to use a button to get to a related sheet record. I would recommend using a relation and an inline list to drill down to a related ammendment. It’s not greate, but you can either use the regular list, or play with tiles or cards to get something that looks like a button.

As you will see in the app, I moved away from the button option for the ‘Amendments’ section and decided to keep it all on the same screen. At the moment, that doesn’t appear to be a major issue but as more amendments to each unit occurs I worry that section could make the screen extremely long and looking a bit cluttered. If you check out unit 350401, you will see that there are 2 amendments in this and how it extends the length of the information on the screen.

To give you an insight into the purpose of this section, it is basically to detail any major changes to the unit - usually a change of colour scheme or change of ownership. The switches are there for the user to activate if they have sighted, photographed or travelled on the unit prior to the amendment in question.

What I would basically like is for the same information that is there now to be available to the user, but on a separate screen. So staying with the example unit of 350401, I would like to scroll down to the bottom and have a button/inline list which takes me through to a screen which shows the relevant amendments for that unit only.

In my mind, I am imagining a need to join the columns relating to the amendments with the unit number (and maybe the user email for the switches as these would be user specific) before creating some sort of relation but I just can’t seem to get it to work (possibly because it is so late in the day haha) as I wouldn’t really want to be having to set up a new sheet in my spreadsheet to make it work as this just increases the row numbers.

There is a 3rd thing I have in my mind but as I have yet to even try this one, I shall refrain from requesting for help on this until I am sure that I cannot solve the problem myself. If you have any suggestions on the 2 points above, it would be appreciated as always.

Thanks

I’ve seen issues, in particular rollup columns, that didn’t work when a relation column was rename. Maybe this was part of that issue.

What I would do is create a sheet that lists all of the different classes and maybe some info about the class. From there you could create a relation to the Fleet Details sheet. I know you currently have the 350 Details sheet, but it may be better to design this to hold details for all classes, so you don’t have to redesign the details for separate fleets. Without have a full visual of your data structure, it’s hard to say exactly how to set this up, but I would think it would be pretty simple using the right relations. I’d envision it something like the multi-level select in https://concepts.glideapp.io/.

I would consider looking at the new Enhanced Link to Screen action if you want to use a button. 🔗 Enhanced Link to Screen Action. You can refer to the same row of data, but design the screen independently.

1 Like

Hi Jeff,

I have been playing around with different things with regards to the creation of a menu system and I think I have found a way to get the sort of menu I am looking for in terms of the structure by making use of the ‘View Details’ Action and making each item independent as shown in the below screenshot:

From the above screen, I will be able to build up the screens I have built in the individual tabs so as an example if I click on Class 350, I will then see the following screen:

And then from here I can click on the 3 buttons (I will probably add a fourth button for ‘Home’ as well) to take me to the relevant screen.

However, the key reason for me using this setup instead of just using buttons in the menu screen is that I want to include details from the ‘Log’ screen in the Details and Caption part of the List. In essence, from the ‘Log’ screen shown in the screenshot above I would want the Sighted numbers (in the example in the screenshot ‘83/87’) to show in the Details part and then the Photographed numbers to show in the Caption part. I have managed to produce the following to give you a visual example of what I am talking about:

I have reached the above by making use of the ‘Single Value’ column type in the data sheet to bring the data from the 350 Detail sheet to the Classes sheet and then made a template column. Unfortunately, as you can see, this only brings the detail for the Class 350 whereas the data in the Detail and Caption part of the list would need to be relative to the Class - ie the sightings and photographed values differ between the 350 and 390 classes. Is something like this possible?

I would consider looking at the new Enhanced Link to Screen action if you want to use a button. :link: Enhanced Link to Screen Action. You can refer to the same row of data, but design the screen independently.

With regards to this, I have read up on the fantastic Enhanced link and this will most definitely work for me to have my Amendments on a separate screen. A great addition to what can be done on Glide.

Thanks

Rather than using a single value, create a relation column by linking the classes (single, not multiple) from the classes sheet to the details sheet and then create lookup columns that use the relation to return specific column data to the main sheet.

1 Like

I did relations to start with but I have become that accustomed to ticking multiple that I did so here and then couldn’t figure out how to get from a multitude of relations down to just the 1 I needed. The answer is that obvious I am slightly embarrassed to have asked but as ever I appreciate your help :slight_smile:

I think we are slotting in the final pieces of the jigsaw at the moment (you’ll be relieved to hear haha) but just thought I would share with you how I have built up the information I required in my last posting to make sure I am doing things correctly.

So I started with the relations as you suggested. Because of the nature of the setup of my app (ie the differing sheets for each of my fleet details screens relating to the differing classes) I have had to setup a relation for each class as shown below:

and then creating the individual lookups for each class before creating a template that will produce the outcome for each individual class:

I have then repeated this process for the Photographed and Haulage parts for this outcome which I am more than happy with:

Is this the only way of producing the relations and lookups for each individual class on the basis of my setup or is my way a long-winded approach that can be improved and tidied up?

Thanks

Looks good. Yeah, that’s probably about the best way to do it if you are going to keep separate sheets. That’s why I like to group similar data into one sheet and use relations to only drill down to the data I want. Similar data in one sheet also gives you the benefit of only having to set up the layout of the details once instead of setting it up for each sheet. I’d consider it if you are going to add several more fleets. It’s all up to you and how you want your data laid out. If it works for you then it’s fine, but also consider any future expansion.