Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 28, 2022 02:14 PM
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!
Solved! Go to Solution.
May 28, 2022 04:28 PM
Welcome back to the Airtable community!
Give that
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,
})
May 28, 2022 04:28 PM
Welcome back to the Airtable community!
Give that
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,
})
May 30, 2022 08:28 AM
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.
May 30, 2022 09:16 AM
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.
May 30, 2022 12:30 PM
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.
May 30, 2022 12:58 PM
Haha, I think this is a better analogy! Airtable is a teenager!! :stuck_out_tongue_winking_eye:
I hope you’re right!!
Mar 22, 2023 05:22 AM
@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.