Summarizing by date in another table

Welcome to the community, @Kelsey_Brookes! :grinning_face_with_big_eyes: The easiest way I can think of to do this is to change how the form user picks the date.

Create a [Dates] table with records pre-created using a date as the primary field. This could be simplified by adding an Autonumber field to the table, and having that drive a formula in the primary field using the DATEADD() function. Something like this:

DATEADD(DATETIME_PARSE("11/01/2020", "MM/DD/YYYY"), {Autonumber Field Name}, "days")

Change “11/01/2020” to whatever you want the table’s starting date to be. That way you wouldn’t need to manually enter new dates. Just add new records, and the increasing autonumber values will end up driving increasing dates. You could probably even create an automation to add a new record for you daily, so it would be completely hands-off. (If you want help with the automation setup, just holler.)

Back in the table where you have your form (I’ll call this [Data Entry] based on your comments), change the date field into a field that links to this one-day-per-record table. The form user will pick a record from that table, and because the primary field in that table is a date, they’re effectively picking that date.

From there, you can add lookup/rollup fields to the [Dates] table to collect specific info from the linked records in the [Data Entry] table and process them as you wish.

Amazing! Thanks Justin!

I’m looking at the automator now, and concerned that I might trigger a runaway date-addition.

I’m not sure of how the logic works - if I based it on the [Dates] table, and set it to trigger when the date field with the formula is before today, does it look only at the last record?

Then with the action, I can’t choose it by field, because the Date field in the [Dates] table is auto-calculated. If I choose to update it based on the action ID, I can’t see how I can tell it to add one record…

I’ll tackle those questions in reverse, because the last one is the easiest to answer.

The action step of the automation would be “Create record.” Because the date in the primary field is automatically calculated with the help of the autonumber field, and the autonumber field automatically increases its number with each new record added, adding the record is all you need to do. No need to set/modify any fields.

In terms of triggering the automation, you want to only trigger it once each day, so I suggest triggering when the date matches today. Assuming the primary field is named {Date}, create a formula field named something like {New Record Trigger} with the following formula:

Date = TODAY()

That will output a 1 when the dates match, and a 0 at all other times. The trigger for the automation would then be “When record matches conditions”, with the condition being that {New Record Trigger} equals 1. That will effectively create the date records one day ahead of when you need them. So on the 27th, it would trigger and create the record for the 28th. When the date rolls over to the 28th, it’ll fire based on that new record and make the record for the 29th, and so on. Because Airtable only fires an automation once until the trigger “resets”, you’ll only get one new record. And because the dates will only match once per record, you won’t get any re-firing from the same record.

On a side note, be aware that TODAY() calculates based on the date change at GMT, not your local timezone, but having a date record fire a few hours ahead of when it’s needed doesn’t sound like it’ll be an issue.