Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 19, 2020 03:53 AM
Hey there,
In order to better manage my emailing campaigns and some of the auto replies I’m receiving I integrated my sending box with Airtable. To ease the cleaning I would like to add within a column a specific code based on the corpus of the auto reply. Here is an example of the formula I came up with:
IF(SEARCH(“yyyy”,Corpus),“Click to validate”,IF(SEARCH(“XXXX”,Corpus),“Nothing”,IF(SEARCH(“aaaa”,Corpus),“Nothing”,IF(SEARCH(“bbb”,Corpus),“Nothing”,IF(SEARCH(“ccc”,Corpus),“Nothing”,IF(SEARCH(“ddd”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“eeee”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“fff”,Corpus),“Unsubscribe”,IF(SEARCH(“gggg”,Corpus),“Unsubscribe”,IF(SEARCH(“hhhh”,Corpus),“Unsubscribe”,IF(SEARCH(“iii”,Corpus),“Unsubscribe”,IF(SEARCH(“jjj”,Corpus),“Unsubscribe”,IF(SEARCH(“kkkk”,Corpus),“Click to validate”,)))))))))))))
but it doesn’t seem to be working. Do you guys have any clue why?
I may have to add new criteria moving forward.
Thanks a lot in advance for your kind help!
Solved! Go to Solution.
Mar 19, 2020 08:06 AM
Hi @Robin_POLGUER,
Looks like there are two minor mistakes in your formula:
ELSE
condition (“Click to validate”,). Since it’s the last conditional statement in your formula, you need to end with only a closed parenthesis rather than with a comma.Here’s your formula with the adjustments for the two items above. And, big +1 for @Zollie’s suggestion to break your formula into chunks to troubleshoot and indent when helpful.
IF(SEARCH("yyyy",Corpus),"Click to validate",
IF(SEARCH("XXXX",Corpus),"Nothing",
IF(SEARCH("aaaa",Corpus),"Nothing",
IF(SEARCH("bbb",Corpus),"Nothing",
IF(SEARCH("ccc",Corpus),"Nothing",
IF(SEARCH("ddd",Corpus),"Unsubscribe & Check for address",
IF(SEARCH("eeee",Corpus),"Unsubscribe & Check for address",
IF(SEARCH("fff",Corpus),"Unsubscribe",
IF(SEARCH("gggg",Corpus),"Unsubscribe",
IF(SEARCH("hhhh",Corpus),"Unsubscribe",
IF(SEARCH("iii",Corpus),"Unsubscribe",
IF(SEARCH("jjj",Corpus),"Unsubscribe",
IF(SEARCH("kkkk",Corpus),"Click to validate"
)))))))))))))
Mar 19, 2020 07:53 AM
That’s quite the formula. I’d recommend breaking it up into chunks so that you can see if parts of it are working in isolation (particularly useful for type related errors, such as using a string when an integer is required). You also might want to do some code indentation while working on it in a separate text editor so it’s easier to figure out what’s going on (where typos are, such as a missing comma). For example:
IF(
Logic,
"if true",
IF(
Logic,
"if true",
"if false"
)
)
Mar 19, 2020 08:06 AM
Hi @Robin_POLGUER,
Looks like there are two minor mistakes in your formula:
ELSE
condition (“Click to validate”,). Since it’s the last conditional statement in your formula, you need to end with only a closed parenthesis rather than with a comma.Here’s your formula with the adjustments for the two items above. And, big +1 for @Zollie’s suggestion to break your formula into chunks to troubleshoot and indent when helpful.
IF(SEARCH("yyyy",Corpus),"Click to validate",
IF(SEARCH("XXXX",Corpus),"Nothing",
IF(SEARCH("aaaa",Corpus),"Nothing",
IF(SEARCH("bbb",Corpus),"Nothing",
IF(SEARCH("ccc",Corpus),"Nothing",
IF(SEARCH("ddd",Corpus),"Unsubscribe & Check for address",
IF(SEARCH("eeee",Corpus),"Unsubscribe & Check for address",
IF(SEARCH("fff",Corpus),"Unsubscribe",
IF(SEARCH("gggg",Corpus),"Unsubscribe",
IF(SEARCH("hhhh",Corpus),"Unsubscribe",
IF(SEARCH("iii",Corpus),"Unsubscribe",
IF(SEARCH("jjj",Corpus),"Unsubscribe",
IF(SEARCH("kkkk",Corpus),"Click to validate"
)))))))))))))
Mar 19, 2020 08:38 AM
Hello Jason,
Thanks a million! It’s working perfectly! I’m definitely going to take you guys advice about breaking my formulas into chunk next time!
Once again thank you both for your help!!
Mar 19, 2020 08:39 AM
Hello Zollie,
Thanks for the tips! Will not miss to use it next time :slightly_smiling_face:
Mar 19, 2020 08:48 AM
Happy to help! For future reference you can also see our growing list of support articles around formulas: https://support.airtable.com/hc/en-us/categories/360003075854