Compare two columns and find matches?

I might be mistaken but this doesn’t seem like the correct way to use columns.
If column 1 is a list of names, column 2 is also a list of names, then within a record does column 1 have any relation with column 2?

Column 1 | Column 2
Andy | Bob

Is “Andy” related to “Bob” in a meaningful way in the record?

I’m with @Y_K. I think you’re misunderstanding some basic things about Airtable, and perhaps databases in general. Even though the default grid view looks very similar to a spreadsheet, which is pretty free-form in terms of how you can arrange the data in its cells, databases have a very different structure.

For example, say you have a database that’s tracking different companies. The first field (column) contains the company name, and is used as the primary identifier for the entire record. Other fields (columns) contain other information about the company: physical address, phone number, web site, etc. Each subsequent row (record) is about a different company, but has similar information in the same order as all other rows because that’s how the fields (columns) were designed: the first field is always the name, the second field is always the physical address, etc.

That’s why you were asked about the relationship between Andy and Bob, because based on your example, they ended up together in adjacent columns (fields) the same row (record). Databases are all about records. Fields (columns) simply define the order and type of data stored across all records. They’re not an arbitrary collection of cells that can be processed at will, like in a spreadsheet.

Thanks for the comments here, much appreciated. I have worked with databases for a number of years, but am new to AirTable. The database we have is a bit more complex, but I am trying to understand some basic functionality of AirTable and have simplified this example. In a traditional database, I would just create a SQL query to extract what we are looking for, but obviously not an option here.

The Serving Opportunities table contains a list of different volunteer opportunities. The goal is to provide a list of individuals (from Individual table) that meet specific criteria (multiple criteria in most cases). The key here is that the list of individuals would be dynamically generated.

Below is another look at these two tables.

Individual Table

Serving Opportunities Table

For Kids volunteer, it should dynamically match individuals that are both members and have kids (Bob, Craig, Sandy), for Greeter it would pull everyone since both qualifiers are either, for Discussion Group Leader it would pull members (Andy, Bob, Chris, Craig, John, Joe, Sandy). The key here is to dynamically filter these results (we have 47 opportunities with multiple qualifiers and lots of different combinations). The Lookup function in AirTable will link these two tables together and filter by one area (that already works), but not by two or more. So, I can create two columns with a list of matches in one area, but don’t know if there is a way to compare them? Or, perhaps there is another solution to this. Make sense?

Thanks for the clarification. It does make more sense now. One way to achieve what you want is through creating custom views for the Individual Table. For example, to make a Kids Volunteer view, start by making a new Grid view (click on the view name, then choose “Grid” next to “Add View” at the bottom of the popup). Double-click the view name to change it. In that view, you would apply filters that only show those records where Members and Kids are both “Yes.” Because the Greeter qualifiers include everyone, perhaps rename the default Grid view to “All/Greeters”. Make other views that apply other filters, groupings, etc. Each view retains whatever you’ve applied for filters, groups, and sorting, so switching views takes you to those custom lists, and it’s all on the same base table.

While the Lookup feature does have its uses, I feel like custom views might be an easier approach in your situation.

Hmm… I also think creating 47 custom views is the easier approach (unless you suddenly get like 200 different new position types), the Airtable UI is easy enough to let people play with filters if necessary – and I don’t expect the filters to change dramatically, because a “Kids volunteer” position almost always requires a Member AND someone with Kids, right?

Think of each custom view as a SQL query. A Kids Volunteer view is

SELECT * FROM Individuals WHERE Member=true AND KidsUnder18=true

Then once you have created all the views, let the users select which view they want, they will see the dynamically updated results.

I would eliminate the Serving Opportunities Table (or use it as a reference document only.) You can still change qualifiers, but through the Filters menu in the view.

Hi Y_K, is there a way to query the data from 2 different tables in 2 different bases besides custom views?

@SnickersDaBear Views can only control the display of records within a single table. To access data across tables in different bases, you’ll need to use the Airtable REST API. If you want the results to be used within Airtable itself, this could be done within a Scripting app or an automation, but most uses of the REST API involve scripts that run on other servers.

@Justin_Barrett I am working on the same problem but for another use case with ~100 new “matches” that need to be performed daily.

I created a spreadsheet that can solve this matching problem but it takes more time to train people to use and I don’t like relying on spreadsheets in general to create applications that are integral to daily operations.

How would you recommend going about creating a script for this in Airtable?

More context: my use case has different definitions but is fundamentally the same as the one above. I need to match people with companies that are located in their state and provide them a list of all companies that service their state.

The process should go as follows,

1. A person fills out a form, which populates a table and specifies the state that they need service in.

2. A list is provided of all companies that service their state.

I will need to match along an additional ~10 features but I figure that if I can match based on one feature, I should be able to add additional features and then include an if statement to display only the companies that are a match for each criteria.

Welcome to the community, @Joseph! :grinning_face_with_big_eyes: While a script would work, you can also do this without a script. The basic process that I’m picturing is this:

• An automation is triggered when the user submits the form.
• A “Find records” action is used to look at records in your [Companies] table (guessing the name) where the state matches that entered by the user.
• A “Send email” action emails the user with the results

The “Find records” action could be designed to match records based on as many criteria as you wish—not just a state match—but those criteria would be applied to all form submissions. The only way to use different match criteria in specific situations (if that’s useful for you) would be to use a script, which also makes email formatting a little trickier (records from “Find records” can be easily inserted as either a grid or list, but scripts can only output JSON-serializable data, so the formatting would need to be done by the script prior to output).

@Justin_Barrett Thank you for the warm welcome and thoughtful answer. I will give it a go!

Ok, followup question! When I go to create an automation to “Find records” everything goes smoothly until I get to the point when I need to define the conditions. I go to create a dynamic condition. I am trying to set the condition as state in the [Companies] table equals the state indicated in the form submission. Am I going about this the wrong way?

You’re doing it the correct way. To complete the setup for that condition, click the gear to the right of the empty field, then choose “Dynamic”. This will let you pick data from a previous step instead of entering something manually.

In you other post, you gave an example of matching foods based on two features. Is this post the same basic question? Depending on the complexity of those 10 additional features, adding in new features to the search may be more complex than a Find Records action can handle.

For example, in your food based example, matching people with foods based on allergies is a situation where any single common allergy rules out a match. This is much more complex than doing an exact match on two different fields.

When I did this it asked me to select a property. I thought that I would be selecting the “state” feature from the customer request table (which is generated from the form). But when I go to do this there are no properties available to select. Do I need to create a property?

Yes! That is the same basic problem. However, I only need to match along a couple of features to start.

Properties come from prior actions (or the trigger step) in the automation. If you haven’t tested the trigger step or other actions leading up to the “Find records” action, you’ll need to do that first before any properties become visible there. For example, you mentioned that you’re matching against a state chosen by the user who submitted the form. You’ll need to submit the form once yourself with each field containing something to test the trigger. With that done, all of the fields from that test submission—including the field where the state is selected—should become available as dynamic options when setting up the “Find records” action.