Tips to improve my prototype app

Hi Jeff,

Thanks very much for your response.
I have amended the detail relating to my Q1 by using a simple join of data within my spreadsheet - annoyed I never thought of this before as it looks so much better on the app.

I have also spent the last couple of hours learning about emoji’s and using them within formulae in Google Sheets. This has lead me to creating 3 new columns with IF formulae in - 1 relating to sighted, 1 to photographed and 1 to Haulage - and then I have joined these columns to use within my app. Must admit that joining cells is not something I do too often and so is mainly kept at the back of my mind - thanks to your help I will certainly be keeping it in mind as I move forward with this.

Looking at Q5, I think I will remove the button and just keep the info within the same page and see what feedback I get on this when I get a few people to test the app for me.

So that just leaves my questions 3 and 4 of which you have advised checking out Inline lists and relations of which I have had only minor success with previously so I think I need to do some more reading up on these - I think I will bask in my couple of successes today and hit the Inline Lists tomorrow but I just wanted to respond to offer my sincere thanks for your response and your help and support :slight_smile:

1 Like

Hi Jeff,

Once again, thanks for your previous response. Really helped me and as I said before your advice regarding my Q’s 1 & 2 lead me to quick solutions for these. I have now (after a weekend of trial and error and lots of learning about inline lists and relations - and maybe the odd scream to release a bit of stress haha) solved the issue regarding my Q3 as well.

That leads me to Q4 and my notes issue of which you advised me to head down the form route. I just wanted to get a bit of a comparison of the notes option I am currently using and the form route you mentioned - will the form route lead to a need for a sheet within my spreadsheet and will this then count towards my row limit? Does the note function also count towards my row count as it appears to be an internal function that doesn’t require information being added to a spreadsheet?

On another subject, I have also added a couple of test photos to some of my items (I am unsure whether this is a feature I will stick with) and was wondering whether there was a way that users would be able to add their own photos to the app or whether it would just be me who would be able to do it?

Thanks

Notes aren’t internal. They are still stored in the sheet. For clarification, are the notes visible to all users? If not, then you could probable get away with creating a user specific column to hold the notes that would only be visible to each individual user. If you need them to be visible to all users, then yes, you would need to create new sheet and a row for each note using a form button. You could set it up so a user could edit their own note if you wanted, which would prevent even more rows from being created. If you wanted a simple solution that wouldn’t use up any rows, you could add a comments component so you could write notes in the form of a chat between all users. The only down side would be that you can’t edit comments and only you as the admin would be able to delete any comments.

You can allow users to add photos using the image pickers. Again this would ideally be a sheet to hold the photos and a row for each photo if you intend to allow multiple photos under one item.

Thanks again for your response Jeff.
In respect of the notes situation, I have decided to just have notes that I would add where necessary and not have any facility for individual users to add notes themselves. I would want any notes I added to be visible to all users though and I guess this leads me on to the next worry I have…

Having reached a point where the template of how my app would look being complete and therefore ready for some testing and feedback from potential users, my concerns regarding row count have resurfaced again. I have begun the process of setting up the sign in process along with profiles for users but I am struggling to see how I can make the app work for individual users without having a mass of rows.

Using the ‘Sighted’ switch in ‘350101’ as an example - if I activate this switch at the moment, this then checks a box in L3 of a worksheet in my spreadsheet. However, if I allow 2 other people to test the current app and User A activates this same switch, that will also show up on User B’s app as well whereas what I actually require is for User A to be able to activate that switch and for that not to impact on User B’s app. Is there a way to make this work using the users email address and relations or are loads of extra rows and columns the only way?

Also, I have noticed that a new row is created in the App:Logins tab, that has now appeared following my activation of this process, every time someone signs in irrelevant of whether they have signed in before or not - does this all count towards the row count?

If you need me to allow access to the data of my app to help you answer any of this then let me know and I will sort it :slight_smile:

Thanks

For your Sighted switch, you could create a user specific column in the data editor and assign the switch to that column. This will keep the switch value unique for each user. You or anybody else will not have access to what the user selected for the switch. This would allow for storing values unique to each user without using up additional rows.

https://docs.glideapps.com/all/reference/privacy-and-per-user-data/user-specific-columns

You don’t have to worry about that App: Logins sheet. That is there only as a log for you to use anyway you want. It is written by Glide, but not accessible or usable by the app.

2 Likes

In addition to what Jeff has proposed, I recorded a video here to visualize the process.

2 Likes

@Jeff_Hager and @ThinhDinh - that is the perfect solution to my problem. Also may lead me to reinstating my original idea of a user specific notes section as well as I assume that to be the same process?

It has lead me to another minor issue with regards to the ‘Haulage’ switch as in my spreadsheet this is linked via a basic formula to my mileage column. In essence, if the mileage column has a value greater than 0, then the ‘Haulage’ switch is automatically activated. Is there a way to make this switch user specific whilst also maintaining the formula I use to automatically activate the switch? (I’ve had a play around with the options in the new column function of the data sheet of my app - thought the If>Then>Else function might help but couldn’t get it to work).

This will also lead on to the ‘Mileage’ column needing to be made user specific. Not sure if this is more confusing as it is linked to the ‘Add Haulage’ button on my front screen of the app.
In essence the user fills in the fields of the form after clicking the Add Haulage button and then this information appears in the ‘Haulage’ worksheet of my spreadsheet. I then use an ‘Arrayformula’ in the mileage column of worksheet ‘350 Detail’ which seeks out mileage for a specific unit, adds it together and this is then shown under ‘mileage’ within each unit of my app (for example - within 350101 you will see a mileage of 80.22). Again, I would want this so if User A fills in the fields on the Add Haulage form, this mileage would then not show up on User B’s app and vice versa - (so had User A been the one to enter the 80.22 miles for 350101 in their form - this mileage wouldn’t then show up when user B clicked on 350101 on their app).

Is there a similar process as to what you have already taught me for the above to make this user specific?

Slightly off-topic as well but I found it extraordinary that Sergio Aguero appeared to be messaging @ThinhDinh whilst he was screen recording that excellent visual example of the solution to my issue - of no relevance, but I found it funny when I noticed it :rofl:

Thanks again for your help and support guys!

Have not experienced that myself, does a setting of row owner solve the switch problem?

Would need @Jeff_Hager for this one.

Indeed, I had another read up of a few potential options and a further play around with these options yesterday but had no luck at all unfortunately :frowning:

1 Like

Here is how I would approach this. I’m assuming that you are also capturing the user’s email when they submit the haulage form. Forgive me for not being familiar with the terminology, so I may get some of this wrong. Hopefully you’ll still understand what I mean. My dad was a brakeman/conductor and later an engineer for freight trains, so I’ve spend some time watching an riding on trains. First of all, since the haulage switch is controlled by a formula in the sheet, flipping the switch will break the formula for everybody. I would instead remove that column from the sheet and we’ll build the functionality in glide.

  • First I would create a template column in your Fleet sheet that will join the user’s email from the user profile to the fleet/unit number or whatever number you are using to relate to the haulage.
  • Create the same type of template column in the Haulage sheet.
  • Create a multiple relation column to join the template column in the Fleet sheet to the template column in the Haulage sheet.
  • Create a rollup column against the relation column to sum the total mileage from the related user’s haulage records.
  • Create an If/Then column that will return ‘true’ if the mileage is greater than 0. Else return ‘false’.
  • Finally, replace the Switch component on the screen with a Basic Table component and set it to display the value of the If/Then column. This will display a checkbox on the screen that the user cannot alter.

This should give you total mileage and a haulage checkbox that will be unique to each user without the use of formulas in the sheet.

2 Likes

Forgot to reply about this, but it was a group of journalists I have been working with for several years haha. We play Fantasy Premier League and assign players’ name to each one as funny nicknames. I am an Arsenal fan myself and have worked as an Arsenal correspondent for Goal.com Vietnam in the past.

1 Like

Hi Jeff,

Once again, thanks for your response.
I see the logic in your process but unfortunately I have been unable to action it despite my best efforts today. I just wanted to seek clarity regarding your first 2 points - where you refer to a template column, are you referring to creating a new column in the data tab and then selecting template as the column type? I assume this to be the case but having watched the Template Column video in the learning section, I can only see how this works if I wish to have a lump of text with bits that are specific to a certain user and not in the manner you are wanting - particularly as the dropdown choices are specific to the sheet I am applying the new column to and don’t relate to other sheets (in this case the user profiles sheet).

Apologies if this is a mis-interpretation on my part but hoping you can assist further.

Thanks

Matt

1 Like

Yes, I’m referring to creating a template with the Glide data editor. All I’ve mentioned is performed within the Glide editor. What I mean in my first point is if you have enabled user profiles, you can access everything for the signed in user from any sheet.

https://docs.glideapps.com/all/reference/privacy-and-per-user-data/user-profiles

In the haulage sheet, I am assuming that you already have the user email in the sheet from when they submitted the form, so it would be much simpler to create a template on that sheet.

2 Likes

Hi Jeff,

That last update really helped, especially with regards to enabling user profiles.
So just as an update as to where I have reached now…

Template columns are complete in both sheets!
Relation has been formed between these 2 template columns!
The rollup column is now where I am struggling with - I think the reason for this is that the data I am looking to sum in this is decimalised and when I select this column in the rollup, I am only offered the choice of ‘count’ or ‘count unique’ and not to ‘sum’. So that has become my sticking point for today.

To give a little background on this in the form on my app, I ask the user to input the miles and chains into the form separately (UK railways are still measured in this form - 80 chains equates to 1 mile) and then I join these together with a decimal place in another column within my spreadsheet and it is this column that I am trying to use in my rollup.

However, I did proceed beyond this to see if I could then complete the final 2 points of your run through - creating an if/then and adding this to a Basic Table component. I did this by just using the miles column in my rollup (this isn’t decimalised and allowed me to choose ‘sum’) and I then went through the process of the If/Then column which I am happy I have done correctly. However, when I then set the Basic Table component to display the true or false from the if/then column I simply get this in written form and I can’t see any way of adding the checkbox you mentioned as there is limited options in the Basic Table component. Not sure what I have done wrong there?!?

Also, I might as well ask this now in case it is something I am doing wrong. I trialled my progress by making a form submission relating to unit 350101 and me travelling 24 miles on it - this worked fine but the details of this also appear on every other row relating to other units as opposed to just the row relating to the unit number. Would this suggest I haven’t linked everything correctly?

Apologies for my constant questions as it kind of feels like I’m just asking you for answers to everything but I can assure you, I do reach the hair pulling stage after hours of trying different things before I resort back to this thread.

Thanks again

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 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 :white_check_mark: ✅ Check Mark Button Emoji for True and :white_large_square: ⬜ White Large Square Emoji for False.

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.

1 Like

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 :white_check_mark: https://emojipedia.org/check-mark-button/ for True and :white_large_square: 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 :slight_smile:

Thanks again for your help

1 Like

On your first point, I think you are close, but it looks like your templates only contain an email address. The templates should contain a combination of the email as well as the unit number. So you will need to put 2 values in your templates.

For you second point, the problem with using a switch is that it’s still can be changed by the user. There is no way to lock it down as it’s an entry component. Even then, I don’t think you can assign an if/then column to a switch component for this very reason. You can try it, but I don’t think it will work. I hear you on the continuity aspect, but the functionality is different between the first two switches and the third switch, which sound like it should not be editable.

For your math, instead of your formula (yuck! :wink: ), I think you could create 2 rollup columns against the relation. One for Miles, and one for Chains. So your first rollup would equal 3 and the second rollup would equal 82. Then you could use the formula below in a math column to get the total miles. The final result for this is 4.025 miles

Again this wouldn’t address getting a result seperated into 4 miles and 2 chains. For that you would probably need to do the math in the sheet, but then split the result on the decimal point and put the first part of the split (miles) into one column and the second part of the split (.025) into another column and multiplying that value by 80 to get a result of 2. A lot of work, but possible, unless you are fine with the decimal form of total miles.

2 Likes

Jeff,

Progress!!! :smiley:

My template is solved now that I have added the unit information as a 2nd value.

With regards to the Haulage switch, I am going to stick with the option you have given me. You are correct that even if I was happy to leave the switch open to be edited by the user, I am unable to assign the if/then column to a switch. This will be something I will open dialogue with my testers on to see if they would prefer the switch to be there for them to use and instead of using the add haulage form, have a note there for them to input their own mileage (obviously requiring them to add it up).

Those are the good bits of news :wink: I am 100% (or is that 80% :confused: ) sure you’ll be pleased to know that I have more questions regarding the mileage totalling. The formula you have advised me on (I am amazed you’re not a fan of my spreadsheet formula :laughing: ) has worked in the sense that where I have input 2 journeys for unit 350101 - 1 at 50 miles 12 chains and the other at 10 miles 70 chains, the total has come out at 61.03 (which is 00.01 out as it should be 61.02 but a quick amendment to 80.5 in the formula seems to have worked on that - not sure if that is a long term fix or not). However, this only works on those units that have had multiple journeys and where the chains added together goes over 80 - on the units where only 1 journey has been submitted, the ‘mile+chains/80’ formula is increasing the total - ie one journey showing as 10 miles 19 chains is then showing in the math column as 10.24 miles - any way of having the best of both worlds on this one please?

Thanks again :slight_smile:

1 Like

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