Summarizing by date in another table

[Kelsey_Brookes]

Hi all, new to AirTable, and this one has me stumped!

I’ve built a simple table that a client will submit to via form. He’ll make multiple records per day which will include several fields with numerical data.

What I’d like to do is to be able to collate each day’s data and present it in a separate table, performing some calculations on that data.

A simple example: the data-entry table has a date field and a won field (checkbox).

If I have 5 records on 17/11/2020, 3 of which have the ‘won’ field checked, I’d like the summary table to have one row for the date 17/11/2020 with a formula that gives me the percentage of ‘wins’ for that day.

It’s got a bit more going on than that, but that’s the general idea!

K

[Justin_Barrett]

Welcome to the community, @Kelsey_Brookes! 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.

[Kelsey_Brookes]

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…

[Justin_Barrett]

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.