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.
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:
Script Block - use it to read and traverse the data tables while computing the aggregations as needed.