Hi I wish to build an App to produce times at...

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

0 34 1,748
34 REPLIES 34

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

@Stephen_Mattison Life is never simple. This is a new idea and I know that I shall meet resistance from the traditionalists who like the โ€™ Old Ways '.

I need to convince the existing Results Team Manager [ Scorer ] who is set in his ways, and the national Motor Sports Association [ MSA ] who control our sport. At this stage, to build an App that does all the work, would be a step too far. They need to see transparency of the system. That is why I need to go down the route of Data gathering to a visible spreadsheet where they can track the flow of the data from the Input sheet to the Results sheet. At some time in the future, it may be possible to do all the functions in the App, but not yet. This month, the MSA has set up a Technology Working Group, who are already considering another project that I am working on timing Classic Car events using GPS, timed to the second with no roadside marshals. When I have a working prototype of the Appsheet Results system, I wish to submit it to them for the MSAโ€™s consideration in the near future. At this stage, it is about widening their ideas of what can be done with technology.

I hope that it will reduce the current data gathering with paper time cards and couriers taking data to someone to key in, with significant risks of errors and mistakes and a lot of manpower. It should also be almost โ€™ real time โ€™ results.

If the finish time is keyed into the App at the Stop Line, the carโ€™s Elapsed time will have already been posted onto the Internet by the time that he drives to the Service Area.

That would be real progress. I am also a firm believer in the KISS principle [ Keep it Simple Stupid ]. Letโ€™s just take it one step at a time, as shown on my schematic diagram [ which has been produced in anticipation of a submission to the Working Group ].

I hope that you understand my position.

@Stephen_Mattison

I would like the display of the App to be bold and simple. with a limited number of โ€™ multiple choices '. .

It may be a bit too much to ask to make it Idiot Proof .

@Stephen_Mattison The Validation List had a 00 / 30 second option to enable the Operator to toggle between them.

Thank you for helping me.

Just tell me how to give you access to the spreadsheet in Google Drive and I shall do it.

This is all new to me.

I am the Official Chief Timekeeper for the South Downs Stages at the Goodwood Race Circuit, UK

next February. I need to work with the Results Manager, who normally collects the times and inputs them in to his Google Sheet manually [ very time consuming and prone to error ]. I would love to use the App to time the event at the Start and at the Flying Finish of the Stage, but that is not possible at the moment.

We need to use MSA Certified accurate Timers. I was proposing to input the times of the Start [ at minute and 30 second intervals, so it is either 00 or 30 ]. At the finish, the cars are timed by a IR Beam to HH:MM:SS. Those times would need to be inserted manually.

The minutes would normally be incrementing by a minute [ so a + / - would be fine ], but the seconds could be 1 - 59 . When I can prove that it works, I would like to consider a timing front end like the Nasa Rally Check Point Timer App.

play.google.com - Rally Checkpoint Clock - Apps on Google Play

which does Arrival, Start and Finish functions.

One day perhaps. Rally Checkpoint Clock - Apps on Google Play play.google.com

@Stephen_Mattison

In the Validation List, T1 / T2 and T3 are for Test data submissions from the App to the spreadsheet prior to the running of the event. 000 / 00 and 0

are for the safety Course Cars running ahead of the rally. They are to check the safety of the Stage, with the โ€™ 0

โ€™ Car running about 5 minutes before the first car. These data strings will be shown in the Blue Columns. The computed Code will be created, but it will have nowhere to be posted to.

I would expect the Scorer to monitor these times to check progress and connectivity.

@John_Broughall thanks for all of the updated info, very helpful. You will not be able to make your UI look like your mock-up data entry screen. I believe that you could set up plus and minus buttons to adjust your data entry but in appsheet I believe that will end up being very clunky and hard to use in cold wet with gloved hands as you mentioned. To get all of the functioning that you have requested, like advancing car number automatically will take lots of work and I do not have time to figure all of that out. I set it up with many Emum choices because I thought that would be faster simpler more error-proof data entry.

@Stephen_Mattison Hi, I have taken on board everything that you have said. Just to confirm that I have reviewed the layout of the App a little so that all the variables are stacked on the Left hand side, with a Minus / Plus keys in the middle and right. Due to a misunderstanding, I donโ€™t want to select / enter the Stage number every time, just set it and keep it for the cars going through that Sage. Likewise, The App will be used at the Start with the Post setting of SS by one Operator. There will be another Operator at the Finish using the Post Setting of SF.

Both App locations will be submitting time data to the Spreadsheet regularly every minute approximately., [ and every 30 seconds for the 30 second starts ]. To avoid these setting to be changed in error during that stage, it would be good if they could be LOCKED.

The Car numbers will normally be sequential, but it may also have the odd change. By using the + / -, the car number can be stepped up manually. I donโ€™t want the App to seek the next available number. I donโ€™t want to seek a Car # from a drop down list of 1 - 99

or scan through a table of numbers as you have suggested.

I would anticipate that when one car leaves the Start Line, the plus button can be pressed once to register the next number.

For simplicity, I could use the Minimum of โ€™ 1

โ€™ and the Maximum of, say โ€™ 105 ', where the numbers 100 - 105 are used for the Course Cars, etc.

It would appear that the number range may be simpler than a Validation List approach.

For the Time, I want to set the Hour from a range of 0 - 23 and normally leave it, unless the hour changes during the

running of the stage, when it will only step up one.

I donโ€™t want to use a drop down List or table, as youโ€™ve suggested. For the Minutes, the Start times will normally be at minute intervals, so when one car departs, I want to manually step up by one minute and not use a drop down list or table for the range of

0 to 59. The seconds are a little more difficult. Generally, the same comments apply, as for the Minutes.

However, I would like to find a fast track way to switch from 00 to 30 for the 30 second starts.

Lets get it working overall first and puzzle that problem out later. I note that some builds of the App refer to a separate reference file held on Google Sheets. Could that hold the Validation Lists and be drawn on to allow the extended range that I am looking for. I would anticipate that the HH:MM:SS would be concatenate the three elements into the full time function for transmission to the spreadsheet.

I hope that the detailed task is starting to fall into place.

I do appreciate your contribution and I know that I can be a little pedantic sometimes. John B

I only need Stage / Post / Car # and time. The spreadsheet computes the unique code to place it into the correct cell in the spreadsheet. It should produce a Results Page, which can be Posted onto Google Sheet for Public access / viewing.

The aim is to have one extra person at the Start and Finish and the rest is just automatic.

@John_Broughall All totally doable and will be sweet!

Should all but eliminate errors and all data will be in your backend Sheet within seconds of the actual finish times.

You can even set up Reports, (with photos if you want) to be Emailed and/or SMS out automatically.

Cool App use.

Iโ€™m kind of glad youโ€™re using external Official Time.

Youโ€™d get accurate time using App, but I guess it wouldnโ€™t be Sanctioned, as you need.

For maximum UI speed, Iโ€™d set everything up using Buttons.

I built a Birth Date/Age calculator and have 100 years to choose from and itโ€™s still way faster than manually inputting 1,9,8,5โ€ฆ so all of your data will be super fast to enter!

I also like using Enum/Buttons because it forces data entry the way you want it, reducing errors. You could totally build an Appsheet App that will do all of your Arrival, Start & Finish functions.

It would be far easier to use than what youโ€™re setting up above.

More complicated to build, but youโ€™d essentially end up with very simple Buttons for Arrival, Start, Finish for each Stage or Post, when clicked, they would automatically enter a Timestamp, then

simple formulas will calculate Start & Finish Timestamps into your Decimal Times, like 02:33:54.

Appsheet Timestamps will use the GPS Clock system, so it would be extremely accurate.

You could even set up automatic GPS Stamps to Log exactly where the person is when they entered the Start & Finish Times.

This would be a very slick way to Network a number of timekeepers in different locations.

LOTS of possibilities!

@John_Broughall When I click on that Sheet you posted, it says I must Request Permission to view the Sheet, I did that, you must go into your Sheet, top right, I believe, & find where you have a Request and give it to me.

Or, if you just โ€œGet Sharable linkโ€ it will make the sheet viewable to the Public, then when I get back in and click it here, I should have access, but everyone else will too, which should be fine cause youโ€™re looking for help.

drive.google.com - Appsheet D4.xlsx

I hope that this works.

Where do you live ? Appsheet D4.xlsx drive.google.com

Later version - drive.google.com - Appsheet D6.xlsx

Appsheet D6.xlsx drive.google.com

Hello John! Did you ever get your Rally Car timing App working the way you wanted? Iโ€™d love to see it if you have! Stephen

@John_Broughall OK, this Later version Spreadsheet opened up just fine.

Lemme check it out.

Just so you know, I do everything in Google Sheets.

I live in Missouri, USA.

Top Labels in this Space