Dec 14, 2020 01:36 PM
Porting something from Google Sheets for the relational goodness, but having trouble wrapping my head around this… don’t even have a formula to troubleshoot, sorry.
I have a table called Legislators. Each record is a person.
I have a table called Legislation. Each record is a bill.
Each bill record has a linked field to Legislators, and contains the uniqueid of each Legislator who voted for the bill. Each bill also has a year, say ‘2020’. There are multiple bills per year in this table, and multiple years worth of bills.
Now, in the Legislators table, I wish to have a field that indicates how many times this Legislators voted for any bill in 2020. I just want a number. That number is the number of times this Legislator’s uniqueid shows up in the voted for field of every bill in 2020.
In Sheets, I use their SQL equivalent search. I started investigating wacky IF statements here, but I just don’t think that’s going to cut it.
Dec 14, 2020 02:18 PM
Hi Mainstream, and welcome to the community!
You’re right, it won’t.
You are describing the process of aggregating analytics which is typically the domain of query languages and especially where joins and other relational references are key requirements. You have a few choices though - this may not be an exhaustive list, but it is a start:
Dec 15, 2020 11:26 AM
Actually, one more thought.
You can use the Count field type to aggregate record counts in another table based on a filter via a lookup field. It’s a little complicated to explain but it’s likely another rock worth turning over.
Dec 16, 2020 11:45 AM
Many thanks for the shout out @Bill.French!
@MainStream_Coalition - I’m Eric one of the people behind Sync Inc. If you were using the SQL functions in Sheets, then our tool should be a nice addition to Airtable to help you pull this data.
I just wrote up a quick guide that you might find helpful here:
And If I can be of more assistance, please shoot me a message and I’d love to help more.