Help

Formula to make a mult-select option disappear if tickbox is filled in?

Topic Labels: Formulas
1786 9
cancel
Showing results for 
Search instead for 
Did you mean: 
Peach_cat
5 - Automation Enthusiast
5 - Automation Enthusiast

Hiya, 

Basically want to know if there is a formula where if the tickbox "marketing brief" column is filled in it will automatically then remove it will remove the marketing brief option from the multi-select "outstanding" column? I've been looking on the forum and can't quite find anything similar. 

Peach_cat_1-1676567521921.png

Thanks!

 

 

9 Replies 9
Ben_Young1
11 - Venus
11 - Venus

Hey @Peach_cat

Alone, a formula is not able to create, update, or destroy any information stored on a given record.
The solution you're looking for is an automation.

Normally, the automation would be incredibly straightforward and simple.
To be honest with you, multi-select fields suck. They suck even more when you're trying to perform automated actions on them.

If you're using the Update record automation action that is built natively into the automations, then there's not a way to perform a selective, conditional removal of a multi-select option.

Personally, I would lean on scripting to handle the update, but presuming that scripting is out of the question for you, there is another workaround.

Here's the example we're working with:

Ben_Young1_0-1676569635678.png

In my example, I'm trying to update my multi-select field so that Option 1 is no longer included, but all the other remaining options remain.
I've created a formula field with the following formula:

 

IF(
    {Tags},
    REGEX_REPLACE(
        {Tags},
        "(,\s)?Option 1(,\s)?", ""
    )
)

 

This formula simply removes any instance of Option 1 from the values stored in the actual Tags multi-select field.
Your version of the formula would probably look like this:

 

IF(
    {Outstanding},
    REGEX_REPLACE(
        {Outstanding},
        "(,\s)?Marketing Brief(,\s)?", ""
    )
)

 

With that, you can just hide that formula field and start building your automation.
I set up my trigger to run when one of the records matches the following conditions:

Ben_Young1_1-1676570015264.png

From there, you'll only need a single Update record action.
You'll want to insert the value of the formula field as the value to write to your multi-select field.

Ben_Young1_2-1676570083381.png

Here's what it all looks like when you put it all together:

Ben_Young1_3-1676570118676.pngBen_Young1_4-1676570133721.png

Once it's all together, it's quite simple.
I think it's important to state that I really discourage these types of formulas and fields. While it will solve for your requirements, you shouldn't make it a habit to create fields in your schema that don't exist to directly serve you or your users.
Every field within your schema should only store relevant information about each instantiation of a given record type, but I digress.

Let me know if you run into any trouble or have any additional questions about this particular problem.
We're always happy to help!

Peach_cat
5 - Automation Enthusiast
5 - Automation Enthusiast

Hiya @Ben_Young1

Thanks so much for getting back to me. I've managed to add everything in but I'm now stuck on the automation at the action. The formula option can't be clicked

Peach_cat_0-1676632748961.png

It says it can't be selected because it has been computed? Could you help? Also, not sure if Record ID is right either. 

 

Thank you so much for your help so far!

Ah!
So, when you're configuring an Update record automation action, you need two things.
Firstly, we need to tell the automation which record we want to update. We do this by passing the record id of the record we want to update.

As things happen in automations, pieces of information about what's been happening along each step of the process are temporarily stored for us to use in other steps if we'd like.

When the automation is triggered, the trigger stores the information about the record. This includes the record id that we need.

Looking at your screenshot, you did already insert the correct record id, but hopefully that will provide you some background on why it's the correct piece of information to input.

Ben_Young1_0-1676658138346.png

The next piece that we need to complete our Update record action is the specification of what information we actually want to update.

This action allows you to pick and choose which fields you want to update.
In the process of asking you to specify the field(s) you want to update, it correctly prevents you from picking a field that cannot be manually updated. This makes sense since you cannot update computed fields.

You actually want to select the multi-select field. In your case, that's your Outstanding field.
Now that you have your field specified, we need to tell the automation what information we want to write to this field.

Since we're going to use variables from the record, we need to change the input configuration from "Static" to "Dynamic." This can be done by clicking the gear icon to the right of the field.

Ben_Young1_1-1676672441004.png

To finish up, all you need to do is insert the value of the formula field into the automation.

 

Peach_cat
5 - Automation Enthusiast
5 - Automation Enthusiast

Hiya @Ben_Young1

Sorry for being slow in getting back to you - I've just had a go now and i've done it! Thank you so much, really appreciate your help with this 🙂 

 

All the best!

Cat

Peach_cat
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi @Ben_Young1

I have another qq about this and wondering if you could help? I want to now replicate this for other multiselect options in outstanding. My question is can I put all the different formulas in the  one formula column or does each multi select option need its own formula column? Hope that makes sense!

 

Thanks!

Cat

Creating a new formula field dedicated to removing each option would cause my brain to melt!
I'm already a pretty uncomfortable with having a formula field that doesn't do anything from a user's perspective.

Luckily, there's a couple of changes to the regex pattern we can make that will allow us to eliminate other options.

Suppose you have the following configuration:

Snag_54dd0e4.png

 

 

IF(
    {Tags},
    REGEX_REPLACE(
        {Tags},
        "(,\s)?Marketing Brief(,\s)?", ""
    )
)

 

 

For the sake of our example, we'll say that we want to remove the following options: General Social, Do Fun Stuff, and Random Marketing Activity.

We'll also say that the original word we wanted to remove was just the Marketing Brief word. So, we'll be adding three new options to our extraction. This brings us up to a total of four options.

Here's what the original formula looks like:

 

 

IF(
    {Tags},
    REGEX_REPLACE(
        {Tags},
        "(,\s)?Marketing Brief(,\s)?", ""
    )
)

 

 

The RE2 regular expression (regex) library allows us to define multiple possible values to match.
To do this, we have to utilize the following syntax:

a|b => This translates to "match 'a' OR 'b', whichever is found first."
...
a|b|c|d|e => This translates to "match 'a' OR 'b' OR 'c' OR 'd' OR 'e', whichever is found first."

 Each OR value is pipe-separated.
Okay, so what does this actually look like in the formula?

Here's a formula that will remove all options that we define:

 

IF(
    {Tags},
    REGEX_REPLACE(
        {Tags},
        "(,\s)?(Marketing Brief|General Social|Do Fun Stuff|Random Marketing Activity)(,\s)?", ""
    )
)

 

For your purposes, this is the important part:

(Marketing Brief|General Social|Do Fun Stuff|Random Marketing Activity)

You can use this as a template to add the options you want to remove.
Something important to note: be sure to confirm that you do not include spaces between the pipe characters and the individual options. Here's an example of what you shouldn't do:

 (Marketing Brief | General Social | Do Fun Stuff | Random Marketing Activity)

 Regex will read those spaces literally and it won't behave as expected.

Now, there's also another change we need to make to the formula. Since the original formula also removes any commas before or after the option we're removing, there are certain multiselect configurations that might cause strange formatting which will go on to wreck havoc on your automation.
For example, in this screenshot we are removing the desired options, but because we're removing the comma that came along with that option, it has left the Blog and Social options flattened into a single value with no comma separator.
Additionally, there are scenarios where leading commas can be left behind.

 

Snag_5679c39.png

Here's the (final) formula that will address this behavior:

 

IF(
    {Tags},
    REGEX_REPLACE(
        REGEX_REPLACE(
            REGEX_REPLACE(
                {Tags},
                "(,\\s)?(Marketing Brief|General Social|Do Fun Stuff|Random Marketing Activity)", ""
            ),
            ",\s+?$", ""
        ),
        "^,\s+", ""
    )
)

 

Here are some additional examples of it behaving like a good formula:

Snag_56e16da.png

  

Snag_56f181e.png

Peach_cat
5 - Automation Enthusiast
5 - Automation Enthusiast

Hiya, 

 

Sorry for the delay in getting back to you. I've had a go with this formula and I'm getting this error message 

Peach_cat_0-1678378087066.png

This is my code: 

IF(
{Tags},
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
{Tags},
"(,\\s)?(Marketing Brief||Social|Demo Video|Press Release|Deck|One Page", ""
),
",\s+?$", ""
),
"^,\s+", ""
)
)

Not sure where I've messed up? 
Thank you for all your help so far, really appreciate it. 

Hey @Peach_cat,

You'll need to switch out your field names. There's also two small syntax errors in the formula you just posted that will also throw you an error.
If you're using your Outstanding field, the complete formula would be:

IF(
    {Outstanding},
    REGEX_REPLACE(
        REGEX_REPLACE(
            REGEX_REPLACE(
                {Outstanding},
                "(,\\s)?(Marketing Brief|Social|Demo Video|Press Release|Deck|One Page)", ""
            ),
            ",\s+?$", ""
        ),
        "^,\s+", ""
    )
)

Here's the formula that you just posted with a callout to where the syntax error is:

IF(
    {Tags},
    REGEX_REPLACE(
        REGEX_REPLACE(
            REGEX_REPLACE(
                {Tags},
        ---->   "(,\\s)?(Marketing Brief||Social|Demo Video|Press Release|Deck|One Page", ""
            ),
            ",\s+?$", ""
        ),
        "^,\s+", ""
    )
)

You're missing a parentheses at the end of the regex pattern and you also have an extra separator in there. Here's the isolated line:

"(,\\s)?(Marketing Brief||Social|Demo Video|Press Release|Deck|One Page"

Here's the adjusted line:

"(,\\s)?(Marketing Brief|Social|Demo Video|Press Release|Deck|One Page)"

 

Hi @Ben_Young1,

Thanks again for your help - you are honestly a life saviour. I don't know what I'm now doing wrong but I've gone into automation and duplicated the marketing brief automation for the blog checkbox and it won't remove the multi-select once the tickbox is clicked? 

I've changed the configuration to 'exactly is' and thats working for marketing brief but won't replicate it for blog? 

Peach_cat_0-1679048415364.png 

Peach_cat_1-1679048443169.png

Do I need to put this into one mass automation as opposed to separate ones for each tickbox?

Sorry to be so annoying with this - I really appreciate your help. 

Thanks,