Skip to main content

When one option is chosen in Multiple Select, return a value of "1" in another field

  • July 25, 2022
  • 8 replies
  • 252 views
J
Forum|alt.badge.img+7

Hi! I have looked pretty extensively in other questions and feel like I just must be missing something.

We have a diaper bank and have one field that is a Multiple Select. That field has many selection options; we don’t care about counting most of the individual items in that field but when either Pads, Tampons, or Pregnancy test is selected, I’d like the value of “1” to be entered into the Pads, Tampons, or Preg test field (so we can correctly count how many of each item we are giving out.)

Can someone please help me with the formula? Thank you!

8 replies

V
Forum|alt.badge.img+16
  • Vivid-Squid
  • Inspiring
  • 529 replies
  • July 25, 2022

Since this is a multi select, do you want the number to increment by 3 if all three are slected?

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • 520 replies
  • July 25, 2022

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.

J
Forum|alt.badge.img+7
  • Jessica_McCanse
  • Author
  • Participating Frequently
  • 6 replies
  • July 26, 2022

Since this is a multi select, do you want the number to increment by 3 if all three are slected?


Thank you for the clarifying question! I was so overwhelmed/busy yesterday I wish I’d taken more time to write my process out. We separately track the numbers of supplies we give; for instance, the “Additional Support” field has maybe 20 different items listed: deodorant, condoms, body wash, as well as pads, tampons, and pregnancy tests. The only numbers I want to keep a record of is the total # of pads, total # of tampons, and total # of pregnancy tests given.

J
Forum|alt.badge.img+7
  • Jessica_McCanse
  • Author
  • Participating Frequently
  • 6 replies
  • July 26, 2022

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.

J
Forum|alt.badge.img+7
  • Jessica_McCanse
  • Author
  • Participating Frequently
  • 6 replies
  • July 26, 2022

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.


I just took a spin through your first example and it works beautifully! Oh my heck, now it feels so silly that I was ever doing this manually. Thank you, thank you!!

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • 520 replies
  • July 26, 2022

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.


I understand the feeling of being at capacity.
Honestly, if you’d like more help or assistance on anything else for your use case, I’d be happy to help for free.

I’m willing to sign an NDA if you/your org. would like.

J
Forum|alt.badge.img+7
  • Jessica_McCanse
  • Author
  • Participating Frequently
  • 6 replies
  • July 26, 2022

I understand the feeling of being at capacity.
Honestly, if you’d like more help or assistance on anything else for your use case, I’d be happy to help for free.

I’m willing to sign an NDA if you/your org. would like.


Holy cats, that would be amazing! I am tech-oriented, but also solo managing a statewide diaper program (currently distributing about 14k diapers, among many other things every month to local families in need). I designed our Airtable a few years ago when things were a lot simpler (we had 4 full-time staff and were seeing fewer clients; now it’s just me and we have 2x the number of clients) so I know there are plenty of things we could/should be doing differently/better. Please feel free to email me! jess@all-options.org

M
Forum|alt.badge.img+2
  • Matthew_Pena
  • New Participant
  • 1 reply
  • June 13, 2023

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.


I adopted this to add up different quantities based on the selection of a subset or all of the available multi-select options. Is the first If statement necessary? If so, what is its role? (My code was fine without it.) Thanks and Kudos.


Terms & ConditionsAccessibility statement