Count records meeting criteria at a point in time

[David_Ayer]

I’ve read many Count Records threads, haven’t found my answer.

Table A lists organizations (schools) I’m tracking. Schools have attributes such as public/private, updated/not updated.

Once a month or so (automated if possible) I want to count how many schools meet various criteria and record in Table B

So Table B will be:

Fields:
Date, Public Updated, Private Updated, Public not Updated, Private not Updated, etc.

Records:
1/1/2022, 200, 400, 100, 200
2/1/2022, 250, 450, 50, 150
3/1/2022, 300, 500, 0, 100

Automation? Script? Rollup?

Also—even if I could find the calculation that would give me those #s, I can’t use a calculated field because it will be current, obviously. I need to the calculation on the 1st of the month and then paste # into those fields.

Thanks in advance!

[kuovonne · Accepted solution]

Welcome back to the Airtable community!

Give that

• you need to calculate the values at a moment in time and freeze those values
• the values are in the hundreds
• you want to perform the calculation about once a month (but not exactly)

I recommend that you use a script. You can either run the script as a scheduled automation, or you can run it from a button on demand.

I do not recommend using rollups. Using rollups would require linking all of your records to a single control record. Maintaining the links to the control record is a pain and it can also slow down your base. You also cannot use the “Find records” action to determine the numbers because you have over a hundred records in several cases.

Note that you can decide to calculate the numbers in the script itself, or in a view. If you are unfamiliar with scripting and want to make defining the logic for calculating the numbers easier, you can create a filtered view for each group of records. Then have the script simply take the number of records in each view and populate them in the proper field in [Table B].

const tableA = base.getTable("Table A")
const tableB = base.getTable("Table B")

const publicUpdated_queryResult = await tableA.getView("Public Updated").selectRecordsAsync({fields: []})
const privateUpdated_queryResult = await tableA.getView("Private Updated").selectRecordsAsync({fields: []})
const publicNotUpdated_queryResult = await tableA.getView("Public not Updated").selectRecordsAsync({fields: []})
const privateNotUpdated_queryResult = await tableA.getView("Private not Updated").selectRecordsAsync({fields: []})

await tableB.createRecordAsync({
  "Public Updated": publicUpdated_queryResult.records.length,
  "Private Updated": privateUpdated_queryResult.records.length,
  "Public not Updated": publicNotUpdated_queryResult.records.length,
  "Private not Updated": privateNotUpdated_queryResult.records.length,
})

[David_Ayer]

This is the exact solution to the problem I was having, thanks!

Anyone looking to count records in a table and record the results in another table, this is your solution.

[ScottWorld]

kuovonne:

Using rollups would require linking all of your records to a single control record. Maintaining the links to the control record is a pain and it can also slow down your base.

I can’t wait for the day (and I have no idea if this day will ever arrive) that we don’t need to use single control records in Airtable anymore. I hope that Airtable introduces the concept of global fields & global variables, which are a staple of most database languages. Airtable is really in its infancy… it reminds me of the early days of FileMaker Pro in the 1980’s and 1990’s, which ALSO required single control records for almost 2 decades before they finally matured the platform.

You also cannot use the “Find records” action to determine the numbers because you have over a hundred records in several cases.

This is such a great reminder that in addition to its other problems, the “Find Records” action can only return a maximum of 100 records.

[kuovonne]

ScottWorld:

Airtable is really in its infancy… it reminds me of the early days of FileMaker Pro … for almost 2 decades before they finally matured the platform.

I see Airtable as a little more mature than a infant–more like a tween or teenager. In some ways the growing pains related to a teen are even more stressful than dealing with an infant. I think it won’t take two decades for the platform to mature.

[ScottWorld]

kuovonne:

In some ways the growing pains related to a teen are even more stressful than dealing with an infant.

Haha, I think this is a better analogy! Airtable is a teenager!! :stuck_out_tongue_winking_eye:

kuovonne:

I think it won’t take two decades for the platform to mature.

I hope you’re right!!

[JamesKenny]

---

@ScottWorld wrote:

 I hope that Airtable introduces the concept of global fields & global variables, which are a staple of most database languages. 

---

That sounds like the mentality of one of these high school students that want to program everything in node. Airtable doesn't market itself as a database because its NOT a data base, its a formatted csv. Their are countless DB platforms usually designed to meet specific needs or to be a free version of the paid version. So far, Airtable fits somewhere in between Mongo DB and Mysql, it isnt trying to be one or the other. Different tasks almost always require different languages or platforms to run efficiently.