Comparing Cells from 2 entries

[Brett_Sizeland]

Hey after some help on the best way to calculate a total from 2 different entries in a table.

So I am trying to develop a scoring/judging system for a school project. One of the sections is they submit a deign and they have to submit 2 identical copies and we mark them as A and B and we check them both against a set of regulations.

I currently have a table that has the team listed twice one entry is for their A copy and the other entry is for their B copy. There are over 30 regulations they need to comply with, I have a column that totals the points scored for A and B separately but I need a way of combining them.

I can’t just add them or do an average or take the lowest score. The way we judge it is if they fail a regulation for both copies they only incur the penalty once. If they fail it with only one copy then its still just the penalty once.

What can happen is copy A fails regulation 5 and copy B fails regulation 7 for example I need a way of comparing them and finding if they failed 10 regulations between the 2 copies.

Example:
Copy A - Fail,Fail,Fail,Pass,Pass,Pass,Fail
Copy B - Fail,Pass,Pass,Fail,Fail,Fail,Pass

In this example their is 7 regulations but between the 2 copies they have failed all 7 regulations, this is where I need to be able to calculate this to ensure the team receives the 7 penalties currently I can only get it to do it based on copy A or B

I hope that makes sense.

Thanks in advance,
Brett

[TheTimeSavingCo]

Hmm, the only way I can think of doing this is by linking the entries and using a lookup per regulation

We then use a formula field to calculate the number of fails like so:

IF(
  OR({Reg 1} = "Fail", {Reg 1 (from Entries)} = "Fail"),
  1
) +
IF(
  OR({Reg 2} = "Fail", {Reg 2 (from Entries)} = "Fail"),
  1
)

Here’s the base

You can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

[ScottWorld]

Welcome to the community, @Brett_Sizeland.

It’s not a good idea to list each team twice, because each unique team should only be listed once in your system.

What you ideally want to do is create 2 tables: one table for teams, and one table for entries.

Then, on your entries table, you can link each entry to a particular team.

Back on the teams table, you can use lookup fields, rollup fields, and formulas to perform any sort of math or calculations that you would like to do on a particular team’s entries.

If you’re new to linking records, you might benefit from watching the “linked records” chapters of my free Airtable training course:

LinkedIn

Learning Airtable Online Class | LinkedIn Learning, formerly Lynda.com

Learn the basics of Airtable, a spreadsheet-database platform, including how to create tables, use different types of fields, leverage views, work with records, link tables, and more.

[Brett_Sizeland]

Hi Adam,

Thanks heaps that has worked perfectly. The formula is quite long but it works.

Do you know how I could automate the linking between the 2 entries?

I have automations setup now when a new team is created it automatically creates the entry A and B but would be awesome if I could automate the linking between the 2 of them.

Thanks again for your help.

[TheTimeSavingCo]

Hi Brett, hmm, I assume those are two separate Create Record actions within the automation? If so, would it be possible to modify the second Create Record action to place the record ID from the first Create Record action into the linked field for the second one?

---

Brett_Sizeland:

I have automations setup now when a new team is created it automatically creates the entry A and B but would be awesome if I could automate the linking between the 2 of them.

Hmm, this implies that you have a Teams table and an Entries table. If your entries are already linked to the correct Teams record, it might be worth considering doing the above setup in the Teams table instead? Slightly cleaner overall, but functionally equivalent I guess heh