Hi Everyone,
I have an idea that I am really struggling with and would appreciate some help.
Goal
Create a script/Automation that loops through table 1 to find matching record based on multiple conditions and link those records with table 2.
Base structure:
Listing
Listing contains all the properties we have in our portfolio and has various field types. For this specific idea, here are the ones that matter:
- House_Listing_Price (number)
- House_Budget_Range (single select)
- House_ViewType (multiple select)
- House_bedroom (#)
- House_land_type (single select)
- House_property_type (single select)
- House_region (linked records -> third table containing list of region)
Opportunities
List of all active house searches we have for clients. For this specific idea, here are the ones that matter:
- Budget range (multiple select)
- View type (multiple select)
- Min. # Bedrooms (#)
- Land type (multiple select)
- Property type (multiple select)
- Region (linked records -> third table containing list of region)
Idea
- Opportunites record > when a button is pressed
- Look through the listing table to identify properties that could match the requirements of the client search
- Link those record to the current opportunity.
Example
Opportunity 1 has the following value:
- Budget range: 3 to 5 M / 5 to 10 M
- View type: Sea view / Countryside view
- Min. # Bedroom: 4
- Land type: Countryside
- Property type: Villa / Project
- Region: North
The script should run through the listing table and identify properties with dynamic conditions based on the opportunities matching fields:
- House_Listing_Price: Has a budget rang of 3 to 5 M or 5 to 10 M
- House_ViewType: Has a view type value of Sea view or Countryside view
- House_bedroom: has a min. value => 4
- House_land_type: Contains the value countryside
- House_property_type: Has a value of Villa or Project
- House_region: Has any of the following value: North
Then automatically link the identified properties with the Opportunity I am doing a search for (I would create a link record field in the Opportunity table to store them)
As you can see, I need to use
- Dynamic fields value for the conditions
- Partial match (has any of the following value)
- Min/Max value match
- Single value match
Any idea/ressources I could use to figure this one out? I was trying to solve it with airtable automation tool but couldn't find a way yet.
Thanks a lot for the help!