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:
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.
|First Name||Member?||Kids under 18?|
Serving Opportunities Table
|Volunteer Position||Member qualifier?||Kids qualifier?|
|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?
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.
@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,
A person fills out a form, which populates a table and specifies the state that they need service in.
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:
[Companies]table (guessing the name) where the state matches that entered by the user.
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).
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.
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.