Tips to improve my prototype app

Ask for Help
1

Hi guys,

I wasn’t sure if this should go into the ‘Showcase’ section but as it is far from complete I felt it better here - feel free to move it if necessary.

Anyway, I am currently building a prototype app which will consist of one part of the finished article- my plan is to a) ensure I can do what I want to within the app via Glide and b) send my app out to a few friends to play around with and offer me feedback. Once that is sorted I will then build the app in its entirety.

However, I have come across a couple of questions regarding what I want and can’t quite get to meet my plans for my app. Basically the app is a data and logging app for trainspotters in the UK - we all need a hobby after all :wink: and I decided to head down this route for myself because I have yet to find a similar app that covers everything I will be covering - other apps out there do parts meaning I have to have a number of apps to get everything I want.

The link to my app is here - https://5t2w1.glideapp.io (go easy on me as this is my first ever attempt at this).

So onto my questions…

  1. On the front page there is a basic table which basically shows the number of units the user has ticked off as sighted, photographed or travelled on. I would also like a column that shows the ‘Total’ number of units but I can only see that you can add rows to the table and not an extra column - would adding a 3rd column be possible? If not is it possible within Google Sheets for me to show the number as 20/87 (20 units seen out of a possible 87) as an example, and still maintain the ability to have the first part of the fraction increase or decrease based selections made within the app?

  2. Once we enter the ‘Fleet Detail’ button we will see the list of unit numbers (under the page title of ‘Home’) which I have used a checklist format for. This checklist currently relates to the ‘Sighted’ switch within each unit number - if the ‘Sighted’ switch is set to true then the checklist is ticked which is great but I would also like to show if the ‘Photographed’ and ‘Haulage’ switches have been checked next to the unit numbers - is it possible to have more than one checkbox there and also can the checkboxes be made so they can’t be edited and only reflect which way the relevant switches are set?

  3. We now move to within the individual unit numbers where I have set multiple components - some of which is strictly data and some of which - the switches - which are user operated. The first sticking point (and possibly a key one) relates to the section I have titled “Travelled in…” and using the 350101 screen as an example you will see that the numbers in this section relate to the ‘formation’ information further up. This data is different for each unit and therefore I need the numbers in the “Travelled in…” section to relate to that unit’s formation but when I change the label in one screen, it changes the label in them all. Is there a workaround for this?

  4. My penultimate question keeps us within the unit numbers and relates to the ‘Notes’ section. You can see an example of inputted notes on the 350101 screen but I would prefer the notes to be in a list format instead of clumped together as they appear now. Is that possible?

Also, on the topic of notes, is it possible for me to have 2 separate note sections - one that I can amend but no other users can and another that the user can amend for themselves. Is this possible?

  1. 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 that’s all folks - really sorry for throwing all of these out in one go but I thought it better to do it this way rather than post in dribs and drabs. If it is a problem how I have done it then feel free to shout at me and let me know the better way - as I say I am very new to this world!

I have spent the last couple of days trying to find solutions to these problems for myself - I’m not the sort to simply try and get others to find the answers for me, I prefer to learn and solve so feel free to just prod me in the right directions if you prefer :slight_smile:

If you have made it this far, thanks for reading and thanks in advance to anyone who can assist.

Matt

2

  1. I think you are stuck with 2 columns for now. You should be able to build a template column that would join the units seen and the total units into one column (like 20/87). Then display that column in the table.

  2. I would create if/then columns in your row that check if the switch is true, then set an emoji value. Then you can create a template column that joins the if/then columns and the fleet number or formation. For example I’m doing something similar to mark which users in my app have admin or edit rights.
    image

  3. You would need a whole lot components with different visibility set. This may be a good case to rethink you sheet structure so you can display this information using a relation and an inline list in the checkbox layout.

  4. Again, this may be better with a separate notes sheet and a form button to write new notes. Add a relation to the notes with an inline list of notes below the add button. Something like this.
    image
    For separate notes that only you or the user can amend, you can set visibility by signed in user to either show a rich text component or the notes component based on signed in user.

  5. 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.

1 Like
3

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
4

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

5

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.

6

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

7

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
8

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

2 Likes
9

@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!

10

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

Would need @Jeff_Hager for this one.

11

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
12

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
13

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
14

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
15

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

image
image622×667 26.4 KB

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
16

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

17

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
18

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

image
image1920×1080 255 KB

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.

image
image1920×1080 257 KB

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.

image
image1920×1080 228 KB

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
19

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
20

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