When one option is chosen in Multiple Select, return a value of "1" in another field
Hey @Jessica_McCanse!
Welcome to the forums!
This is a fun one to explore!
Just to make sure I’m understanding your requirements, here’s what I’m understanding:
You have a multi-select field with a wide array of options.
Regardless of how many options are selected in the multi-select field, you only need to see if the Pads, Tampons, or Pregnancy Test values are selected.
If they are selected, you need to be able to quickly see as such to allow you to easily report how many are being given out.
If I’m misunderstanding one of the requirements, let me know, and I can adjust to meet them!
If they’re correct, then here’s how I would do it.
Method 1: The Simplest Way
I would say that this is the messiest way. It doesn’t scale very well, but it gets the job done.
You’ll want to create three different formula fields for each of the three items you’re looking for.
Since Airtable interprets multi-select fields as one continuous text string, all we have to do is search for the words we’re looking for and have it return true if it finds it.
Here are the formulas for each field.
Why is the {Items} field in a LOWER function?
Humans are one of the single biggest sources of bad data.
Few examples are more glaring than how we handle grammar and alphanumeric characters.
The LOWER function removes the chance that somewhere along the line, there is an inconsistency in the case lettering that ruins the formula’s query.
It’s a small thing that might not matter at all, but it’s a good habit nonetheless.
IF(
FIND(
"pregnancy test",
LOWER(
{Items}
)
),
1
)
IF(
FIND(
"tampons",
LOWER(
{Items}
)
),
1
)
IF(
FIND(
"pads",
LOWER(
{Items}
)
),
1
)
Method 2: Three-In-One
This method is pretty dope, depending on what you’re trying to accomplish.
You’re functionally building a summary field that lets you quickly glance over records and see a “summary” of whatever you define.
There are an almost infinite number of permutations that you can use, but this is just an example.
You can also quickly build filters around what is shown in the field since you can build filters to look for specific criteria easily.
Here’s the formula that I used in that screenshot.
I wrote it fast, so I apologize if it’s dense and not the most optimized.
"Pregnancy Test: " &
IF(
FIND(
"pregnancy test",
LOWER(
{Items}
)
),
1
)
& "\n" &
"Tampons: " &
IF(
FIND(
"tampons",
LOWER(
{Items}
)
),
1
)
& "\n" &
"Pads: " &
IF(
FIND(
"pads",
LOWER(
{Items}
)
),
1
)
Method Three: Flexing Airtable’s Muscles
This example is going to be very quick and simple, but if you have a table that tracks your inventory and a table that tracks your “outgoing” records, then by linking the two tables, you can build a dynamic, simple inventory management system that updates passively with minimal effort on your end.
Using a rollup field, I can have Airtable tell me what my estimated remaining inventory is.
You can then use that information to get metrics such as how much time passes between your restocking orders.
You can see the distribution of which inventory item is used the most.
etc.
There are a ton of really dope things you can do once you start building it out.
I’m happy to explore this a bit more if you’d like!
I’d also be happy to answer any questions you might have.
Quick Edit
I just saw @Vivid-Squid’s reply and had to take a moment to laugh. I realize that I might have completely misunderstood the counter requirement.
If that is what you’re looking to do, then this should do it.
IF(
{Items},
IF(
FIND(
'Pregnancy Test',
{Items}
),
1
)
+
IF(
FIND(
'Tampons',
{Items}
),
1
)
+
IF(
FIND(
'Pads',
{Items}
),
1
)
)
Again, there’s probably a more straightforward way, but this gets it done.
Major props to you for the thoroughness here! I am going to take a moment to look more closely at the examples you shared. Here is a quick shot of my table. Currently, what we’ve been doing is, once a week or so, just manually going through and entering a value of “1” for the corresponding field if we see that option was selected. But knowing there is likely a better way, and given that we’re a tiny nonprofit and I’m pretty much AT capacity, I wanted to work smarter, not harder.
