Count records meeting criteria at a point in time

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,

})

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,

})

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.

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.

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.

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.

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.