Hi there.
I am trying to create a voting system in airtable. Basically, I am hosting an awards show kind of thing (think oscars kinda structure) where people are nominated for awards, and judges vote on the nominees. There are 2 rounds: round 1, each judge in each award catagory individually rates each nominee by a set of 5 criteria; round 2, all the judges from every award get together and vote which of the top 3 from each award should win the award.
I am currently finishing up round 1, and am running into a few issues. The issue that I would like to address here is about a "Power Point" or "Je Ne Sais Quoi" point that each judge is able to give 1 nominee per award (its basically there so that if a judge thinks one nominee deserves an extra point they can give it to them). The issue is that as my base is set up right now, a judge can add this Power Point to multiple nominees. Ideally I would like to make it so that if a judge decides to add the power point to another nominee, it removes it from the first one. I figured there would be a way to do this with automations, but I am struggling.
How I have constructed my base:
Nominees Table- Primary field is the name of each nominee. the 1st field is a link to a 'Voting Submissions' table where all the voting records are stored. the 2nd field is a link to an 'Awards' table that assigns each nominee to one of the awards. the 3rd field is a rollup field that collects the averages the final scores for each nominee assigned by their respective judge.
Voting Submissions Table- Primary field is an amalgamation of the Nominee field, Award field, and Judges field. the 1st field is that linked field to the 'Nominees' table. the 2nd field is a look up field to the Awards field in the Nominees Table. The 3rd field is a linked field to the Judges table, and assigns each Judge to a record. the 4th - 9th fields are rating fields for each criteria, followed by the 6th field which holds the Je Ne Sais Quoi field.
Judges Table- the Primary field lists each judges name. the 1st field is a linked field to the Judge's corresponding Award. the 2nd field is a user field, linking an airtable account to each judge. the 3rd field is the linked field to the voting submissions table.
Awards Table- the Primary field lists each award. the 1st field is a description of what each award is for. the 2nd field is the linked field to the corresponding Nominees. the 3rd field is a linked field to the corresponding judges. the 4th and 5th fields are look up fields to the airtable account field and voting submission fields, for the sake of the construction of my interface.
Ok. I've tried to color coordinate the linked fields and tables to make things easier for you, but in retrospect i may have just made it unbearable to read. if you would like access to the base, let me know.
Appreciate all your help community!
Ben