Registering runners:
The scrRegister Screen has
- a text entry box for a runner name
- a text entry box for a unique runner ID number, which can be optionally filled by
- a Scan button
- a Register button, to add the runner to the Runners sheet.
There is no Screen Initialization code for this screen.
A Scan button is provided for people who have pre-printed bar codes they want to register with runner names.
When the Register button is Clicked,
- Check for blank runner names and ID
- Ask Sheets for any Runners rows that already have this runner ID, to avoid duplicates, using a Filter Query for just that ID.
We want the filter result to be empty. If it is not empty, complain. Otherwise start building the row that will hold this runner:
-
runner ID
-
runner name
-
lookup formulae for each of the checkpoint names (Staten Island, Brooklyn, etc.)
-
Pop up a Progress Dialog to warn that a sheets Add is in progress
-
issue the Add Row request
Here is how each lookup formula is built:
To verify I got the right ranges:
I had to add a function to bypass a bug in the Spreadsheet component that makes it choke on a text JOIN result:
This concludes the tutorial.
This is only one way to handle the problem of keeping milestones in a row.
It could also have been handled by looking up the row number of a runner when he passes a checkpoint, and updating that cell in the runners sheet.
The detail logging done here would facilitate adding more data to the checkpoint rows, like the identity of the person who logged that checkpoint for that runner.
Feel free to comment on this tutorial here.
P.S. @TimAI2 pointed out silently that the Sheets Pivot facility can be used to generate those rows of Checkpoint times by runner ID.
I have no data yet on whether or not this operation needs to be done after all data collection is complete, or if it can be done before data starts to arrive and will automatically update from the Checkpoint details sheet during the race.
