Re: Formula to make a mult-select option disappear if tickbox is filled in?
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.
Thanks!
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:
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:
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.
Here's what it all looks like when you put it all together:
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!
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
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.
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.
To finish up, all you need to do is insert the value of the formula field into the automation.
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:
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.
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:
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
This is my code:
Not sure where I've messed up?
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?
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,
