0

I am looking to return the values to an Expected Revenue column, based on the WEB FORM CAMPAIGN field having specific words in. The campaign names are set up like the following, so I am looking for only a string of words within that campaign name to return the corresponding value as per the formula.

example of campaign name: Generic - Fitted Wardrobes - New Should return:951 as per my formula. But returns NO VALUE. Please help!

IF(
    AND(
        CONTAINS("*Brand*",  Web_Form_Campaign__c ),
        NOT(CONTAINS("*LW*:*Generic*", Web_Form_Campaign__c))
    ),
    "1370",
    IF(
        AND(
            CONTAINS("*LW*",  Web_Form_Campaign__c), 
            NOT(CONTAINS("*Home*Office*", Web_Form_Campaign__c))
        ),
        "716",
        IF(
            CONTAINS("*Home*Office*",  Web_Form_Campaign__c),
            "937",
            IF(
                AND(
                    CONTAINS("*Fitted*Wardrobes*",  Web_Form_Campaign__c ),
                    NOT(CONTAINS("*Non*", Web_Form_Campaign__c))
                ),
                "951",
                IF(
                    AND(
                        CONTAINS("*Bedrooms*:*Fitted*Bedroom*:*Bedroom*Furniture*", Web_Form_Campaign__c),
                        NOT(CONTAINS("*Competitor*:*Discovery*:*DSA*", Web_Form_Campaign__c))
                    ),
                    "1120",
                    IF(
                        AND(
                            CONTAINS("*Wardrobes*:*Cat*:*Non*",Web_Form_Campaign__c),
                            NOT(CONTAINS("*Fitted*:*Design*:*Sliding*:*Walk*In*:*Location*",Web_Form_Campaign__c))
                        ),
                        "702",
                        IF(
                            AND(
                                CONTAINS("*Competitors*", Web_Form_Campaign__c),
                                NOT(CONTAINS("*Hammonds*",Web_Form_Campaign__c))
                            ),
                            "708",
                            IF(
                                CONTAINS("*Sliding*",Web_Form_Campaign__c),
                                "620",
                                IF(
                                   CONTAINS("*Walk*In*",Web_Form_Campaign__c),
                                    "830",
                                    IF(
                                        CONTAINS("*Overbed*",Web_Form_Campaign__c),
                                        "655",
                                        IF(
                                            CONTAINS("*Loft*",Web_Form_Campaign__c),
                                            "571",
                                            IF(
                                                CONTAINS("",Web_Form_Campaign__c),
                                                "No Campaign assigned",
                                                "No Value"
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

They all seem to be pulling NO VALUE if they have a campaign in, no matter what words are in the campaign. Where am I going wrong?

1 Answer 1

1

The CONTAINS() function does not take wildcards.
It is also case-sensitive (which may or may not be an issue, but it is notable since many parts of Salesforce are case-insensitive).

The documentation calls the parameters text and compare_text, but I think it's more helpful to label them like so CONTAINS(haystack, needle)

The CONTAINS("A:B:C", My_Field__c) construction here looks to be correct (i.e. this would return true when the "needle", My_Field__c, is either "A", "B", or "C", which are all individually part of the "haystack").

The issue is that Web_Form_Campaign__c isn't just a single word. The "match one of any of these" trick with CONTAINS() doesn't work if My_Field__c has more stuff in it than the options you're trying to match against (i.e. if My_Field__c from the example above is "Ad", "A d", "A B", or even something like "A ", the result will be false).

I don't think what you're doing here is well-sutied to be a formula. It should be possible in theory, but it's quite possible that you'd run into the formula "compiled size" limit. I'd probably look towards Apex to accomplish this (though it may also be possible with a Flow).

If you're dead-set on using a formula for this, then you'll need to use Web_Form_Campaign__c as your haystack and search for each possible needle separately (you can't group them together).

e.g. CONTAINS("*Bedrooms*:*Fitted*Bedroom*:*Bedroom*Furniture*", Web_Form_Campaign__c),
would become

OR(
    CONTAINS(Web_Form_Campaign__c, "Bedrooms"),
    CONTAINS(Web_Form_Campaign__c, "Fitted Bedroom"),
    CONTAINS(Web_Form_Campaign__c, "Bedroom Furniture")
)
1
  • Thanks Derek. That makes a lot sense. Appreciate the help a lot.
    – Rob Boyton
    Jul 31, 2023 at 15:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .