Hi I wish to build an App to produce times at the Start and Finish of a Rally stage and put it into my spreadsheet to generate the automatic results, to Post on Google sheets.
The spreadsheet sets out what I wish to achieve. Can anyone help me build the App ?
John
SO519AL UK
@Stephen_Mattison
Thank you for doing the Initial design of an App.
To give you some background, the App is to be used at the Start / Finish of a Rally Stage i the middle of winter, using gloved hands.
I would like the controls to be chunky and robust.
It can be cold, wet and windy.
The App is being designed for my next event on 9 February 2019. At the Start of each Stage, I would like to set the Stage number, and the Stage Post, [ and preferably Lock it for the duration of that stage ]. At the end of that Stage, it would be unlocked and changed to the next Stage details.
[ I don’t want to have to enter those details every time ]. The cars normally arrive in sequential order, so Car # 4 will be followed car # 5. On that basis,
It would probably best to have a displayed number and a
Plus / Minus to toggle to the next number, again with large buttons. Likewise, the cars normally depart at minute intervals, so the hour will normally set and may increment up by one in the duration of the running of one Stage. The minutes will increment by one minute for each car, so a plus / minus format would be suitable. The cars will start on the whole minute [ 00 ], which will not change. However, they do do 30 second starts, so the final
seconds could be 00 or 30. On my Validation List, I had a 30 and 00 at the top and bottom of that List so that the Operator could toggle between them. When the Enter button is pressed, , the data string [ Stage # / Post / Car # / Time is passed to the next available line on the spreadsheet. The spreadsheet is designed to have 3000 lines of data in the blue columns, [ that is 99 cars for 12 stages with a Start and Finish, plus some headroom ].
The Blue columns form a vlookup table, which feeds the following sheets.
The blue cells are for inputting, the rest are derived and could be Protected.
Some arrangement required to allow the Moderator / Scorer to amend incorrect data.
@John_Broughall This should be a pretty easy App to build.
But I don’t think you’ll be able to do your side by side layout you show above.
Each Row in your App/on the screen will be in a Vertical stack.
You should still be able to set up something that works very fast & efficient.
I would set up the Stage & Post with Buttons.
You can manually enter time, or set that up with Buttons as well.
The way it looks like you’re going, (adding your own Time from an official outside source, instead of using Start & Stop times in the App to compute your own Time) I’d go all Buttons.
Set it all up as Data Validation in your Sheet, must click “Reject other input” to make it Buttons in the App.
Data Validation in your Sheet will turn into Enum Columns in your App.
Go into the Column edit and change Input to Buttons.
@John_Broughall WOW, that’s one helluva spreadsheet!
Very busy!
Hard for me to really see everything that’s going on.
This could be harder than I thought you needed from your original explanation.
If you just want to keep it all working in the Spreadsheet, I suppose that you could build a simple way to just use an App to just input data, but you’re going to have to change things a little, or a LOT depending on how you want to go.
Will take me a bit to figure out where data will need to go.
As I see it, the required data input string is Stage / Post / Car # and time [ HH:MM:SS ]. This information goes from the App into the Blue columns on the Input sheet, [ one per row ]. The first three items create a unique Code for each time. In the next three sheets a unique code pulls the time from the input sheet, [ which is a VLookup table ]. Row 3 is where the functions are all created, and then simply copied to the rest of the sheet. The Elapse times are taken from the detail sheets to the Results sheet.
The single
blue cell defines which Stages are put into the Total. The totals are then Ranked for the Positions. The App is the data collecting front end and the rest is derived. That is what I planned, but I may have missed the point.
I was hoping that you could build the App to capture the data and send it to the spreadsheet. Thank you for your location - I now have an idea of the time zone difference.
@John_Broughall
In the spreadsheet, all the grey font columns will be ’ hidden '. to reduce confusion and avoid the cells being changed.
There needs to be somewhere for the Rally Results Manager [ what is known as the Scorer in the US ] to correct any errors. I would expect the data to be entered at the Start Line and the Flying Finish by reliable operators.
They would also have a handheld radio link to pass any amendments / corrections from the Posts to the Results Team…
As this will be a ’ Real Time’ Results system it needs to be corrected quickly.
To give you an idea of what I am aiming to achieve, I attach a plan of a Rally Stage and a schematic of the Data flow. I hope that you can make some sense of it.
@Stephen_Mattison
Hi
I think that I may have pushed the wrong buttons ! !
I have Posted some details onto this Thread, but they may not have been directed at you - Stephen… Is there any chance that you could review by misguided Posts or should I re send them to you ?
@John_Broughall @Stephen_Mattison
@John_Broughall Just seeing your posts now. Looking into it. App will likely be very different from your spreadsheet.
@John_Broughall You need a profile image so people can find you easier. Other John Broughalls out there.
@Stephen_Mattison
I do have a photo in my Profile but it doesn’t seem to have registered it.@Stephen_Mattison
@John_Broughall Give me Access to that Sheet & I’ll help you if you still need it.
Might not be able to look at it until tonight, but this should be easy. Do you have an outside timing source you must transcribe, or could you use Start/Stop buttons in the App to automatically compute your Time?
@John_Broughall Weird. When I search for you, I do see a different color “J” than the other JBs, but here in your Post, you only have that creepy blue guy icon.
No biggy.
@Stephen_Mattison
This Appsheet system is all new to me. I am not sure what can be achieved. I could try my hand at understanding how to do the simple things, but the matter of better presentation and an intuitive interface would be completely out of my reach.
[ I need help ]
I hope that the schematic gives you some idea of what I am trying to achieve.
In essence, I would appreciate your advice and help on how I can capture the Stage / Post / Car # with a time and place them into a Google sheets spreadsheet, where the Results page can be opened to public view to give real time results. The first question should be - is this possible ?
@John_Broughall I understand what you’re needing to do.
Explaining your abbreviations would be helpful, I’ve figured out “SS” & “SF” = Stage Start & Stage Finish, but what’s “TC” stand for? I see under “Rally Race” at Wikipedia that there can be multiple Drivers & Co-Drivers.
Is there a maximum number of Drivers, Could there be 3 or 4 Drivers & 3 or 4 Co-Drivers, or just 2 Drivers & 2 Co-Drivers maximum? Exactly what is your Code (1SS1) for?
Is this Rally Race Standard code, or are you just using it to help compute elapsed & total time?
@John_Broughall
Yes, this is all very possible and will be sweet when finished.
I see BIG problems when Spreadsheet Jockeys try and hold on tho their Spreadsheet format and try and make an app out of that.
It’s usually much better to build an App that just happens to use a Sheet as a Backend.
Not critical of you at all, you gotta start somewhere, but for your Apps sake (and your/our sanity) the sooner you forget about all of those pre-half-populated Tables (1-4, 5-8, 9-12, Results) the better.
You will end up putting all SS times & SF times into one Table [Stages], then the App will split things up and display Results how you want it.
@Stephen_Mattison
SS = Stage Start;
SF = Stage Finish;
TC= Time Control, which is currently not being used within this spreadsheet, but may be required in the future. For the Rallies in the UK, the Crew comprise of a Driver and a Co-Driver only.
Each crew and their Car is given a Competition number for that event [ in the range of 1 - 99 ]
The information inputted to the App will be the Stage number, [ in the range 1 - 12 ]; the Post [ either SS / SF / TC ], the Car # [ in the range 1 - 99 ] and the time [ HH:MM:SS ]. The App doesn’t need to create the Code. When the Stage / Post / car # are entered into the spreadsheet Input page, it is Concatenate’d
[ made into a combined string ] into a unique code. The subsequent three sheets [ each with 4 stages ] is waiting to receive a time and it given a matching unique code for each Cell.
For Car # 1 on stage 1, it will have a start time and an finish time [ 1SS1 and 1SF1 ].
The spreadsheet takes the start time away from the finish time to give the Elapsed time for that Stage. The elapse time is then brought forward to the Results Page, [
being a time taken for the first stage for Car # 1 [ from the SS to the SF ]. The only function of the Appsheet App is to capture the Stage number, the Post, the Car # and the time.
The spreadsheet will do the rest. In the spreadsheet, the raw data is added to the blue columns and everything else is computed from it [ the data is derived. ] to produce the Results, including the times of each stage for each car to give a total. the car # with the lowest Total is the winner and is Ranked automatically. Apart from the data inputting at the Start and Finish the rest of the system should be automatic.
However , errors do occur, so someone needs to be able to tweak the numbers on the spreadsheet and over-ride the automation. The Results Page in the Google sheet is given a unique Link which is distributed to the competitors using a QR Code.
@John_Broughall Check out this screen cast video I made of a little Rally Input App I made for you.
This is super simple, will something like this work?
You could probably add something like this to your Excel Sheet and use it just as input, but it would be MUCH better if you built the entire thing into an App that would do everything and more than your spreadsheet does, and much more organized and easy to use!
The way your Tables are set up, none of that will work in Appsheet world, only one record per Row allowed.
Your Values must be in it’s own Table. To speed things up a bunch in this sample Input App, I setup Show_If of =ISBLANK() so as soon as you make your choice it’s locked in and jumps to the next row ready for input.
Without this, since there are so many choices, it would be easy to bump into & enter the wrong data while scrolling down to the next row.
If you make a wrong entry, you’d just hit Cancel and start over, would take 1 second. App now CONCATENATES the HH, MM, SS into [Time], it would be very easy to make the App compute Elapsed time, and everything else.
Notice how I have the Table View Grouped by Car #, could also make a View Sorted on Stage, Driver, etc.
There might be a way to use a Time column to enter HH:MM:SS, but I don’t know how to omit the AM/PM.