topic Re: Update Picklist Field Using Formula with SEARCH function in DemandTools

Update Picklist Field Using Formula with SEARCH function

Murphybp79 — Mon, 28 Oct 2024 22:23:37 GMT

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')

Re: Update Picklist Field Using Formula with SEARCH function

Pugs_Validity — Tue, 29 Oct 2024 15:42:34 GMT

@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.

Re: Update Picklist Field Using Formula with SEARCH function

Murphybp79 — Tue, 29 Oct 2024 15:47:32 GMT

@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?

Re: Update Picklist Field Using Formula with SEARCH function

Murphybp79 — Tue, 29 Oct 2024 16:01:03 GMT

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?

Re: Update Picklist Field Using Formula with SEARCH function

AnthonyValidity — Tue, 29 Oct 2024 19:01:20 GMT

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!

Re: Update Picklist Field Using Formula with SEARCH function

Murphybp79 — Tue, 29 Oct 2024 19:18:24 GMT

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

Re: Update Picklist Field Using Formula with SEARCH function

Murphybp79 — Mon, 04 Nov 2024 23:17:25 GMT

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.

Re: Update Picklist Field Using Formula with SEARCH function

PeteIntradiem — Wed, 30 Oct 2024 17:19:59 GMT

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?

Re: Update Picklist Field Using Formula with SEARCH function

PeteIntradiem — Wed, 30 Oct 2024 17:44:48 GMT

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