Help Creating Conditional Links to Records Between Table: i.e. only link relevant records [New U

[sicahjes]

I have two tables that I have linked:

• Case Studies
• Events and Policy

Each of these tables has a 'Region' field in the accompanying records (set to single selection). I am trying to set it up so that when the location fields match between the two tables, these matching record are linked automatically. 

For example: 

1. There is a record in the Case Studies table named 'European Union'.
2. There is a record in the Events and Policy table named 'Brexit'
3. The 'European Union' record has a single selection field called Region, which is set to 'Europe'.
4. The 'Brexit' record has a single selection field called Region, which is set to 'Europe'.

Ideally, the 'Brexit' record would automatically add to a linked record field set up in the 'European Union' record (I know this will also be vice versa, which is fine). I would like to repeat this in these tables with different regions (i.e. Africa, Asia, North America etc.). For example, a record titled 'US Election' in Events and Policy will link to a 'North America' record in Case Studies, and a record titled 'AUKUS' in Events and Policy will link to an 'Asia' record in Case Studies etc.  

I have read other posts in this in this community, but I am new to Airtable. So whilst the answer may be out there, I didn't quite understand some of the solutions (they seemed to assume knowledge I don't have). I have tried playing around with automations, but I keep getting errors. I also tried placing conditions on a look up, but it will only allow me to place conditions on one table (meaning I could only do this for one region).

Lastly, I know that my approach may be way off. So, if you have a suggestion on how I could add Events and Policy to records in Case Studies in a way that only links relevant records, I would be grateful!

[kuovonne]

Keep playing around with automations. 

Each link between two tables has two linked record fields: one in each table. You can make the link from either side of the link. Decide which side will trigger your automation. It will probably be [Case Studies]. Test the trigger. 

Use a “Find records” action to find records in the other table. Use the gear icon to say you want to find records based on condition. Set the conditions based on the two fields that will have the same values. Make sure that options have exactly the same spelling, capitalization, white space, and punctuation. Test the action. 

Have an “update record” action that updates the original triggering record with the found record. 

If you still have trouble, I recommend posting screenshots. 

[Stephen_Orr1 · Accepted solution]

Hi @sicahjes,!

This is very easy to do in Airtable. You'll need to use a Find Records step to lookup matching records in the other table from the one used in your automation trigger. Your automation would look something like this:

Trigger: When record matches conditions
     - Pick either table to start from and add the condition where Region is not empty
      ("When record is created" could run with missing Region values and "When record is updated" runs on every keystroke, so "When record matches conditions" works best)

Action: Find Records
     - Choose the opposite table that you chose in the trigger step above
     - Add a condition for the Region field and turn this to dynamic
     

     - Click the blue plus icon and choose the Region field of your trigger record

Action: Update Record
     - Select the table used in the trigger step
     - For Record ID, click the blue plus icon and select the record ID of the trigger step record (When record matches conditions -> Record ID)
     - For fields, select the linked record field between your two tables and insert Find records -> Make a new list of Airtable record ID
     

Test all of your steps and turn the automation on 🙂

Hope that helps!
-Stephen

[sicahjes]

Thank you so much, this worked! I'm encouraged by the fact that I was part of the way there, but I had some things mixed up. I'm, sure I'll get the hang of it the more I use it. I do have a follow up question, if that's ok:

Is there a way to do this if the information in the one of the tables is a multiple select field? For example, some records in the Events/Policy table may relate to more than one region. It would be great if I could have it set so that if a record in the Events/Policy table has more than one region selected, this record will then link to each relevant record in the Case Studies table. 

I tried playing with this after I followed your instructions, but I get stuck a the 'Action: Find Record' step.

1. First, it wouldn't allow me to select the Region property once is was changed to multiple select (see image below). 
2. So, then I changed both 'Region' fields to multiple select. The automation tested fine, but it didn't populate any fields. 
3. Once I updated the test record to only have one option selected in the Region field, the automation worked... so I'm guessing that I missed something. 

This is what happens in step 1 above.