Complex find and count across tables with a couple of variables, help?

[MainStream_Coal]

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.

Thank you.

[Bill_French]

Hi Mainstream, and welcome to the community!

MainStream_Coalition:

I started investigating wacky IF statements here, but I just don’t think that’s going to cut it.

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:

1. Script Block - use it to read and traverse the data tables while computing the aggregations as needed.
2. ZapQuery might work (no experience with it).
3. My money is on Sync Inc - they’re probably going to take the SQL integration market for no-code platforms.

[Bill_French]

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.

[Eric_Goldman1]

Bill.French:

My money is on Sync Inc - they’re probably going to take the SQL integration market for no-code platforms.

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:

DEV Community

Go further with Airtable using Postgres Arrays

Airtable is a bit of a tease. While it looks and feels like a database, you can't query it like a da...

And If I can be of more assistance, please shoot me a message and I’d love to help more.