Compare two columns and find matches?

[Craig_Bottolfso]

I am helping my church with a project and have what seems like a simple problem to solve.

I have a table with a list of Serving Opportunities (greeter, discussion group leader, children’s leader, etc). Column 1 is a list of Individuals (linked to Individuals table) that meet certain criteria…as an example it might be that they are a member of our church. This column contains the names Andy, Bob, Chris, Craig, John, Joe and Sandy (all members). Column 2 is a list of Individuals (also linked to Individuals table) that meet a different criteria, perhaps they have children under age 18. Column 2 contains the names Bob, Fred, Craig, Jason, Sandy and Sarah.

What I’d like to have is a third column that returns matches in Column 1 and Column 2 (names that appear in both). In this case, it would be Bob, Craig and Sandy.

I’m sure this has been asked before, but I can’t seem to find a solid answer. Would love any guidance! I’ll also note that there are instances based on the Serving Opportunity we might have three or even more criteria to filter. Thanks much everyone :grinning_face_with_smiling_eyes:

[Y_K]

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?

[Justin_Barrett]

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.

[Craig_Bottolfso]

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

First Name	Member?	Kids under 18?
Andy	Yes	No
Bob	Yes	Yes
Chris	Yes	No
Craig	Yes	Yes
Fred	No	Yes
Jason	No	Yes
John	Yes	No
Joe	Yes	No
Sandy	Yes	Yes
Sarah	No	Yes

Serving Opportunities Table

Volunteer Position	Member qualifier?	Kids qualifier?
Kids volunteer	Yes	Yes
Greeter	Yes or no	Yes or no
Discussion Group Leader	Yes	Yes or no

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?

[Justin_Barrett]

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.

[Y_K]

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.

[SnickersDaBear]

Y_K:

SQL query

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

[Justin_Barrett]

@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.

[Joseph]

@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.

[Justin_Barrett]

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).