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:

[Joseph]

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

[Joseph]

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?

[Justin_Barrett]

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.

[kuovonne]

Joseph:

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

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.

[Joseph]

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?

[Joseph]

[Joseph]

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

[Justin_Barrett]

Joseph:

But when I go to do this there are no properties available to select. Do I need to create a property?

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.