Compare two columns and find matches?

#1

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 :smile:

0 Likes

#2

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?

0 Likes

#3

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.

1 Like

#4

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?

0 Likes

#5

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.

0 Likes

#6

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.

1 Like