Compare records from different tables, calculate and store the result

[Emin]

Hi there,

I’m new to the Airtable and absolutely love it so far!

However, there’s one task that I can’t seem to wrap my head around as it’s a bit difficult to understand the logic behind it, including the correct formulas/linking techniques needed.

I will explain it in simple terms:

Base: Baskets
Table 1: Basket 1
Table 2: Basket 2

Basket 1 contains:

• apple
• banana
• orange
• pear

Basket 2 contains:

• apple
• banana
• orange

Basket 2 to Basket 1 match is 75% (3 out of 4 fruit match).

I need to calculate and store the result in a record of Basket 2 table that says “75%” (percent field type) that basically means “Basket 2 matches 75% of Basket 1 contents”.

Please note that fields in each table are defined as text, not as numbers. So I will need to find the strings that are true for both tables and then calculate the match %.

I understand that some calculations, comparison and formulas have to be involved.

I just can’t seem to understand how to link Basket 1 to Basket 2 (or vice-versa), and how to calculate and further store the Match Result in the Basket 2 table. Like do I link the 1 to 2, or 2 to 1, etc.

Would you please guide me on how do I approach this problem and/or provide a somewhat appropriate solution?

This is the sample database with full creator rights. 75% was inserted manually.

Airtable

Airtable | Everyone's app platform

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

P.S. If you need some additional details, I will happily provide them.

Thank you!

[Justin_Barrett]

Welcome to the community, @Emin! :grinning_face_with_big_eyes: This wouldn’t be possible using linking and formulas. For this to work, you’d need the ability to iterate through both collections to compare their contents, and Airtable’s array functions currently don’t include iteration. This is doable using a script, though.

Would you need this comparison to be run automatically as the options are updated, or would this be only run sporadically?

[Emin]

Hey, Justin!

I’d like to use it automatically. So whenever a new item is added to the second table, for example, (or any particular item changes) then it updates automatically and shows the correct match %.

I’d appreciate it if you could guide me through the scripting process of that.

сб, 18 сент. 2021 г., 20:43 Justin_Barrett via Airtable Community Forum <airtable@discoursemail.com>:

[Justin_Barrett]

Sorry for the delayed reply.

Emin:

I’d like to use it automatically. So whenever a new item is added to the second table, for example, (or any particular item changes) then it updates automatically and shows the correct match %.

How are new items added? Are you adding them manually? Are they entered via a form? Some other method? This will determine how the automation is set up.

Emin:

I’d appreciate it if you could guide me through the scripting process of that.

If you’re willing to experiment on your own—perhaps starting by reviewing the scripting examples available in Airtable’s Scripting app, and also keeping a good JavaScript reference handy—and you come up with something that doesn’t quite work as you expect, feel free to share your script here (be sure to mark it as preformatted text with this editor button: </>). There are several of us who are willing to offer help with small issues directly in the forum.

However, guiding you through the process from scratch is a much greater investment of time. Please message me directly about options for hiring me as a direct consultant to mentor you through the scripting process.