Comparing records from different tables and finding matches

[Medschool_Renta]

I’m currently creating a web app where I’m pulling data from Webflow and Memberstack using Zapier. I have 2 separate tables.
Table 1: On this table, I have a list of provinces each as a checkbox field. So if a real estate agent is licensed in a province, that province gets a checkmark. So if they’re licensed in Ontario, the “ON” checkbox field gets checked.
Table 2: On the second table, I have a list of requests where there’s a province single select field, so if a request came from Ontario, “ON” gets selected.

Right now, I need to have a reference field that assigns these requests from table 2 to the real estate agents that are licensed in the matching province. So if Request 1 came from Ontario, all real estate agents from Ontario should get assigned that request.

Can anyone help me with this please? Totally stuck right now

[JudoHacker]

It’s really hard to offer a solution without seeing your tables and knowing more about how the data is being pulled. DM me if you want me to take a look.

I built PowerImporter for easily syncing your Airtable base to your Webflow CMS. So, I know a thing or two about integrating Webflow and Airtable. :laughing:

[Databaser]

Hi @Medschool_Rentals and welcome to the community!

Just out of curiosity, but why don’t u use a “multiple select” field instead of a checkbox field per province?

[kuovonne]

Welcome to the Airtable community!

It sounds like you want to join real estate agents with requests based on a the province. Normally in Airtable relationships like this use linked record fields. However, all of the links between the records need to be created manually, with code, or with a 3rd party integration. It cannot be done directly with only field values.

The data in your base also is not normalized, which is a fancy way of saying that I recommend a different structure for your data. You might not be able to change your data structure due to how your Webflow/Memberstack integrations work. However, I suggest you have a [Provinces] table in addition to your [agents] table and [requests] table. Agents would link to provinces and requests would also link to provinces. Then you can use a series of lookups/rollups to see which agents and requests match with each other.

[Databaser]

kuovonne:

However, I suggest you have a [Provinces] table in addition to your [agents] table and [requests] table. Agents would link to provinces and requests would also link to provinces. Then you can use a series of lookups/rollups to see which agents and requests match with each other.

Maybe this is a good place to start if you are looking for more information on this.