Update Picklist Field Using Formula with SEARCH function

[Murphybp79]

I'm using the Modify module to try and update a new picklist field I've created with a formula.  I want to select a certain value based on if I can find the word "Cancelled" in the name of the record. I'm trying to use an IF with SEARCH, but I keep getting errors, and I can't find any documentation on how to use SEARCH in DemandTools.  I've created this formula in Excel and it worked, but it doesn't work in DemandTools.  Anyone know if this is possible, and where my formula may be off?

IF(SEARCH('Cancelled',${Name},1)>0,'Cancelled','Fulfilled')

[Pugs_Validity]

@Murphybp79 - I believe your formula just needs a slight adjustment:

IF(ISNUMBER(SEARCH('Cancelled',${Name},1)>0,'Cancelled','Fulfilled')

ISNUMBER is a true or false – I use it in front of search as search counts something in the string. SEARCH returns the number of times it is there. As long as it is a number, that means the searched item exists in the string and you get a TRUE from ISNUMBER.

[Murphybp79]

@Pugs_Validity I have the comparision of ">0".  That should also return a TRUE/FALSE.  So are you saying that DemandTools can't handle evaulating a greater than/less than comparison in formulas?

[Murphybp79]

I updated the formula to this, and this worked. 

IF(ISNUMBER(SEARCH("Cancelled",${Name},1)),"Cancelled","Fulfilled")

However, it does seem you can't do any number comparison's in an IF statement, which is dissapointing.  Is there another way to accomplish something like this?

[AnthonyValidity · Accepted solution]

Hi @Murphybp79 ,

Thank you for reaching out.  Love to see people using Modify's Formula action - one of my faves.  The reason why you're geting a syntax error is because SEARCH returns an error if there are no matches.  We do allow comparing a returned number, but in this case you will not always get a number.  The IF(ISNUMBER(SEARCH is my best approach for finding search terms in a string, but you could use this as well:

IF(
    IFERROR(SEARCH("Cancelled", ${Name})) > 0,
    "Cancelled",
    "Fulfilled"
)

"If find_text is not found, the #VALUE! error value is returned."  Article can be found here.  I tired the original formula in excel and received the #VALUE! error. 

I also noticed single quotes in the original formula - be sure to use double quotes in instances like these.  

Hope this helps!

[Murphybp79]

@AnthonyValidity appreicate the info. Can you explain why my second example, which would not return any error, doesn't work?

[Murphybp79]

Sorry, just saw the remark about double quotes.  I tried that and it does work.

So essentially, where Excel will allow the formula even if it returns #VALUE! error, DemandTools will not.

[PeteIntradiem]

I think my question is much simpler.  How do I update text values to a picklist field?  I used Import/Update, uploaded my file of Account ID and Industry, selected 'Always Update', Preview.  The result is 'Import 0 Account records.'  The word Import (not Update) is a little scary - but regardless, the result = 0.  Can anyone help please?

[PeteIntradiem]

I've solved it.  I just used Account Name as the Mapped field instead of Account ID.