How to create a matching system? i.e. create a list of records from one table filtered based on values from another table

[Joseph]

Hi Everyone!

Can you help point me in the right direction to help solve for my use case below?

Any examples of scripts are much appreciated!

Use case:

Create a simple matching script that will take inputs from a form and then return a list of matches based on the inputs

Below is a (slightly) simplified version of my use case.

Goal: to match people with specific types of food based on their allergies and taste preferences for a wedding.

Data:
Table 1. Food List. This includes Information about certain types of food. Each record includes a food choice with the feature “allergies” and the feature “food preference”.

Feature: “Allergies”
Allowed Values: “peanut”, “gluten”, “dairy”
Many to Many relationship
One person may have multiple allergies and one allergy may exist for multiple people

Feature: “Preference”
Allowed Values: “kosher”, “vegetarian”, “vegan”
1 to Many relationship
Each person can only select one preference. One preference may be true for many people.

Table 2. Customer request. This includes the food preferences for each customer. The food preferences are displayed as an array of values that are linked to the tables below.

Table 3. "food preferences"

Table 4. "allergies"

Table links: the features in tables 3 and 4 are linked to tables 1. and 2. and appear in tables 1 and 2 as an array.

Desired Workflow:

1. A user fills out a form listing their allergies (up to 3 in total) and their preference (only 1 allowed).
2. These values populate as a row in the table “customer request”
3. A list is provided of all the food options that satisfy both their allergy criteria and food preference criteria. There may be up to ~20 choices.
4. The food choice options are stored in the table “customer request”
5. An email is sent to the person who submitted their information in the form with a list of their food matches.

As I understand it the script needs to,

1. Provide a list of all records in the table “Food List”
2. Then, for each record in table “Customer Request”, find the value located in the preference column and filter the original list of foods to show only the foods that match this preference.
3. Then, for each record in table “Customer Request”, find the value located in the allergy column and filter the original list of foods to show only the foods that match this allergy preference.
4. Display this list

There are multiple ways to accomplish this. I would definitely encourage people to let me know if there is a better way to approach this problem in order to make the most eloquent script possible. This will eventually be used to process ~100 match requests a day and thus the number of records will grow quickly.

Thank you!

[kuovonne]

I have written many matching scripts for clients. (I am not able to share any of them.) Here are some things to think about.

• Do you want an automation script to process the form submissions one at a time as they are received? Or do you want to process form submissions in batches to reduce the number of automation runs? Or will you be running the script manually?

• I’m guessing that the output of the script would be linking the new form submissions to all the matching foods. I’m also guessing that you are using Airtable automations to send the email. However, if you want to combine the script linking the records and the email action in the same automation run, it might be harder than you think. It would be easier to use two different automations, although this would double the number of runs you need. It would still be only 6,000 runs per month (out of 50,000).

• Some of the functionality you want could be accomplished with a Find Records automation without a script. In fact, depending on the number of allergies and the number of foods, it might be possible to create a system that does not use a script at all.

• You are very correct that there are many ways to write a script that accomplishes your goal. The best script is the one that you understand and can maintain, not necessarily the most efficient.

• This particular script is best written by someone with some experience coding. Do you have that experience or are you looking to hire someone to write the script?

[Joseph]

Hi Kuovonne!

Thank you very much! This is helpful. Below are my answers.

• I want this to run automatically
• I want to break down the problem to first, product a list filtering along 2 criteria, second, create a list filtering along 5 criteria, third develop the email script to automate the last step.
  *I am looking into the Find Records automation now to see how much I can utilize this functionality
  *Good point
  *I am going try as much as I can to understand the problem and then will likely hire someone to create a better version. If you have any other advice for how to go about that feel free to message me.

[kuovonne]

Joseph:

I want to break down the problem to first, product a list filtering along 2 criteria, second, create a list filtering along 5 criteria,
…
*I am looking into the Find Records automation now to see how much I can utilize this functionality

Since you eventually want to add more and more criteria, the Find Records action probably will not work for you in the long run.

When it comes to writing a script, you need to both understand how to use the scripting api, and also developing a matching algorithm. You do not need to have your matching algorithm done before starting to work on understanding the scripting api. In fact, actually implementing a prototype of your initial algorithm is sometimes very useful in evaluating it.

Joseph:

third develop the email script to automate the last step.

For sending the email, you will not use a script. You might use a script to create an email record, but you will probably use an email action to actually send the email.

Joseph:

*I am going try as much as I can to understand the problem and then will likely hire someone to create a better version. If you have any other advice for how to go about that feel free to message me.

If you do not know how to code and your goal is simply having working code, you might be better off hiring someone from the start. When I get a client who has tried and failed to adapt a complex script from the internet, fixing/revising the script is often as much work as starting from scratch. (There may be some bias here–when people find and successfully adapt scripts on their own, they don’t end up needing to hire me.)

On the other hand, if one of your goals is to learn how to write scripts, starting with matching on only one feature, and then gradually expanding the matching algorithm makes sense.