Suggestions on how to accomplish this reporting system

  • Profile Image
    AgilityAssoc.Canada
    Asked on March 10, 2019 at 10:22 AM

    Hi,

    Every year our association has a national event. There can be almost 800 dogs competng in different classes and size range. The people running the event keep track of each competors scores and enter the data into computers. Then at some point printed out and copies sent to administration.

    So here is my thought. We create a form to submit the data a particular class, example.

    10 " Class event Gamblers. Data is collected for all the dogs running this event.

    Below is an example of data collected, highlighted, for 3 dogs running a Gamblers 1 course. The sheet also shows the scores for all 6 events the dog would over the three days.

    My form would be for 1 event at a time.

    1552226765score-card.png

    If I used a sheet-2-form widget for each class to populate the dogs name, ID number, breed and handlers name. then as the results for each run com in they would enter the data into the form. At the end of the event it  would be submitted. The form is integrated with g-sheets. The g-sheet has a webpage, so now the webpage is displayed on the Nationals website along with a sheet that is compiling all data for each dogs 6 runs showing total points accumlicated for the 3 day event. This also shown on the website. That is what the image above shows.

    Dose this sound feasable to you guys? The only glitch in the ointment is if the event has access to the web. If not this is all for nothing.

    I would appreciate any suggestions.

    Thanks, Robert

  • Profile Image
    jherwin
    Answered on March 10, 2019 at 11:58 AM

    Could you please clarify and see my answer below if I understood your query correctly.

    1.) You have an online form and it is integrated with google spreadsheet.
    2.) Now, the form will be submitted and the data entered will go to your spreadsheet.
    3.) You need to show the records or all entered data from your spreadsheet on your webpage.

    By the way, is the [1, 2, 3] has different forms? Please explain the workflow of your form so we can check if there is an option for your requirement.

    Looking forward to your response.

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 10, 2019 at 09:05 PM

    Hi,

    You have a good start of understanding the process. I am adding a simple flow sheet to help.

    1552266643nationals1.png

    1. Each ring has a form to fill for the amout of dogs for that event

    2. The forms are prepopulated (sheet-2-form) with the dogs entered in the event, one for each ring.

    3. When the event in a ring is completed the form is submitted / integrated with g-sheets.

    4. The dats for the complleted event is posted on the website.

    5. A master sheets complies all the data for each class and each dog and displays it on the webpage.

    6. At the end of the competion after all events have been completed the master sheet shows Aggregate Scores with highest to lowest scores for each size division.

    I think I have it... If I think of something else I will add it here.

    Regards, Robert

  • Profile Image
    Kiran
    Answered on March 11, 2019 at 02:59 AM

    Please note that if there are different forms for the events, the data will be sent to different integrated Google sheets. So, it is required for you to create a new Sheet to merge or compile to display the final scores. Since you wanted to display the scores on a separate master sheet, you may pull the data from the integrated spreadsheets by using IMPORTRANGE function and display the compiled data on your webpage.

    Also, could you confirm if you want to read the Google sheets data to the form again by using Spreadsheet to form widget?

    155228745511032019_120314.png

    If so, unfortunately, it is not possible to read the data automatically from the integrated Google sheets. The Spreadsheet to Form widget is required to upload an Excel file offline and the data from that sheet can only be read from the form.

    If I am missing something, please let me know. We will be happy to help. 

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 11, 2019 at 07:58 AM

    Hi,

    You are understanding very well. 

    could you confirm if you want to read the Google sheets data to the form again Answer is No.

    There are 4 different height classes and 4 different disaplins, each disaplin is run twice but on different days.. That is 64 score sheets.

    Heights: 6", 10", 16", 20",

    Disaplins: Standard, Jumpers, Gamblers, Snooker.

    (This part is for me to manage) There is a Master Total score sheet for each height class, so 4 masters.

    Just an FYI, If there are 200 dogs in each class. The same 200 run in every disaplin. So they have 4 runs a day. A run lasts between 12 to 16 sec, depending on height and breed. 

    Reason I mention this is, only 4 forms need to be created, one for each class as they are prepopulated with the same dogs every time.

    What can change is a dog being pilled because of injury or for disiplinary reasons. The handler can also be pulled for the same reasons. So we need to be able to indicate what is called a DNR (did not run). There is also a DNF,( did not finish)

    Again 4 sheets, for each height class.with ability to change or select DisaplinsStandard 1, Jumpers 1, Gamblers 1, Snooker 1. Standard 2, Jumpers 2, Gamblers 2, Snooker 2. Requires 1 Sheet-2-Form for each height class, so 4 widgets.

    I'm good with spread sheets so that part of the process I can manage very well at moving and massaging data for the website.

    Just a reminder, I am not sure wifi is available so this may be a muite excerise.

    Best Regards, Robert

  • Profile Image
    Kiran
    Answered on March 11, 2019 at 11:16 AM

    Thank you for the additional information. Did you already start working on the form? I believe all the 4 forms shall be similar except for the height class. If so, please share the form ID so that we can check it and take it forward. Also, it would be helpful if you can share the Excel spreadsheet with sample data that shall be populated to the form that might help.


    We will wait for your response. Thank you! 

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 11, 2019 at 04:37 PM

    Hi,

    I have not started working on a form at this time.

    Normally these are not on spread sheets, the data is loaded into software programs, so I could not find one..

    Here is a link to a G-Sheet I made to show the fields. It may be difficult to follow because a number of fields use more the one column, but I will let you be the judge.

    https://docs.google.com/spreadsheets/d/1jaHIQ4-5Zykuy2mQhR41wMhvr_87gX2vXgKWnkkj9CA/edit?usp=sharing 

    Also you will se additional classes for vetrans and specials. Not to worry about that just now, also the heights are different then I indicated as they have been changed since this event.

    Tank you, Regards, Robert

  • Profile Image
    jherwin
    Answered on March 11, 2019 at 06:04 PM

    I think it's better if we have the form now so we can run some tests. And by the way, you can check the video here on how to combine multiple spreadsheets into one: https://bit.ly/2SUlbmJ

    So it will be like this, 3 forms for 3 spreadsheets then you will make three spreadsheets into one. You can then do the calculation and condition on that one spreadsheet.

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 11, 2019 at 07:50 PM

    Hi...

    Thanks to all of you... As i fist inicated this might not happen because there was no wifi, I have been informed that they attempted to have it installed but were unable to do so without some difficulty and expense. So unfortunatly there is no Wifi and no need for us to continue. I'm very disappointed. I should have waited a bit longer before asking for your support but in the begining there was some urgency. 

    Again thank you for the great support as usual. I had a look at the video and itis informitive, Thank you!

    This may happen in a year when the event moves to a new location.

    Best Regards,
    Robert

  • Profile Image
    jherwin
    Answered on March 11, 2019 at 09:06 PM

    I see. By the way, the whole process and workflow are nice. Let us know if you will implement this in the future and we will be happy to help.