Creating checkbox in rich text field via scripting

[kofal]

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!

[BenjaminM]

Hi Kofal!

I was wondering if you had managed to achieve this project. I have a similar use case and I am trying to understand how the script would work as well as what's the best way to display the results. I am pretty new on Airtable so any recommendations are welcome!

Thanks!

[kofal]

Hi @BenjaminM Welcome to Airtable.

Yes I was able to achieve something very close to what I had in mind. I would advise looking for a GPTs airtable script assistant (if you are low code). I literally built my script with it entirely.

Here is a script layout you can use for inspiration:

1. Function Definitions: The script begins with several function definitions, each calculating a score for a different matching criterion between an opportunity (buyer's preferences) and a listing (property details). These functions take in relevant parameters, compare them according to predefined rules, and return a score reflecting how well the listing meets the opportunity's criteria

2. Data Retrieval: The script prepares to fetch data from the "Opportunities" and "Listing" tables, defining the fields it will need from the "Listing" table.

3. Opportunity Record Retrieval: It retrieves values from the current opportunity record, such as minimum bedrooms, desired view types, budget range, etc.

4. Filtering step: You might want to add some filters before you loop through all your properties in order to optimize API usage (could be check the property status, filter by budgets, things like that)

5. Looping Through Listings: The script then loops through each property in the "Listing" table, filtering out those that do not meet basic criteria such as status, type, price, and region.

6. Scoring Each Listing: For each listing that passes the initial filters, the script calculates scores for each criterion using a scoring function (you need to define your scoring function before looping through properties. There are many ways to score this type of data (1 to 10, to 100, weighted scores, etc)

7. Calculating Total Score: It combines these individual scores into a total score for each listing (it needs to reflect your scoring method)

8. Sorting and Selecting Top Matches: The script sorts the properties by their total scores in descending order and selects the top matches.

9. Outputting Top Matches: Finally, it prepares the top matches for output, including their scores and other details.

10. Updating the Opportunity Record: The script concludes by updating the opportunity record with the top matching listings, linking them via a linked record field

What took time is understanding how to prepare the data for the different field types in airtable (single select, multiple select, numbers, text, etc).  He has great tutorials on scripting: https://www.youtube.com/@AutomateAlltheThings

Hope that helps.

[BenjaminM]

Thank you so much, I appreciate your prompt and detailed reply!