Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Help with Variables

Solved
Jump to Solution
2272 4
cancel
Showing results for 
Search instead for 
Did you mean: 
mrorstad
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, I'm just learning so I apologize for the ignorance.

I'm trying to collect game statistics for my son's hs lacrosse team.

I have a data table with fields of Statistics (like shots made, shots missed, etc.) and Player.

I have a form that lets me enter statistic (e.g. forced a turnover), Player (6 Jimmy) and submit.

From that, I have a pivot table that collects the stats for each player.

I want to make a field for Quarter that doesn't have to be entered each time I create a new record.  I tried creating a table with game information and doing a lookup but I don't think that works.

Is there a way to have a record keep the Quarter the same until I have it changed at the end of each quarter?  The goal is to be able to have a summary of stats for each quarter and the overall game.

Thank you!

Mike

1 Solution

Accepted Solutions
Jan_Cape
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Mike,

If I understand correctly you're using a form to create all your new records.  In this case you can autofill the quarter in your form by changing your form's URL slightly.  Create a "Quarter" field with single select option for each quarter (First, Second etc. in this example).  Click on Share or open your form in the browser to find your URL.  To the end of the URL add ?prefill_Quarter=First where Quarter is the name of your field and First is the value you want prefilled.  Use this form to enter your stats until the next quarter when you'll use the ?prefill_Quarter=Second form, etc.  

Here's some documentation that will explain in greater detail:  https://support.airtable.com/docs/prefilling-a-form

Go team!

See Solution in Thread

4 Replies 4
Jan_Cape
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Mike,

If I understand correctly you're using a form to create all your new records.  In this case you can autofill the quarter in your form by changing your form's URL slightly.  Create a "Quarter" field with single select option for each quarter (First, Second etc. in this example).  Click on Share or open your form in the browser to find your URL.  To the end of the URL add ?prefill_Quarter=First where Quarter is the name of your field and First is the value you want prefilled.  Use this form to enter your stats until the next quarter when you'll use the ?prefill_Quarter=Second form, etc.  

Here's some documentation that will explain in greater detail:  https://support.airtable.com/docs/prefilling-a-form

Go team!

Jan_Cape's solution would work great for your purposes

Assuming the "Quarter" value will always be based on the created time of the record, i.e. when it was submitted, yo could perhaps use a formula field with DATETIME_FORMAT() for this purpose as well:

Screenshot 2023-04-16 at 1.22.52 PM.png

And the formula used is:

DATETIME_FORMAT(Date, "Qo") & 
" Quarter " & 
DATETIME_FORMAT(Date, "YYYY")

 

mrorstad
5 - Automation Enthusiast
5 - Automation Enthusiast

Jan, Thank you!  That worked very well.  It took me a bit to find how to add the string to the URL and then followed the video that you suggested to redirect to the URL with the prefill info added.

From that, I discovered that I can share the new form each quarter with the parents that are helping to record the stats for the boys.  I can actually have one person recording shots, one penalties, etc., etc.!!

This really helps, thank you!!

Mike

Thanks Adam, that will be very useful for another project I'm working on.

Best, Mike