a week ago - last edited a week ago
I'm building out a modify to my Accounts object. We have record types for People (Households) and Businesses/Churches (Organizations) - I'm trying to change some businesses with INC in their name but Vince also contains those 3 letters. I really want " INC" - with a blank space before the inc letters. When I add the quotes in my record selection criteria I use the quotes but they go away. How can I achieve my space/INC query?
Thanks,
Roger C.
Solved! Go to Solution.
a week ago
Roger,
you should be able to use % INC% which will match to anything with INC in it but has a space before the INC.
Chris
a week ago
Hi @Roger_Coville, I ran some testing on this and found a solution that might work better for you. Rather than filtering within the Record Selection Criteria for "INC", it might be best to utilize a RegExReplace formula in the Set New Values section (step 2). You can still set a Record Selection Criteria stating "Name contains INC" if you'd like to narrow down your search.
By using the RegExReplace formula in Modify, you can find a specific string value and replace it with a value of your choosing - you can copy the formula below and test it out within your DemandTools instance to see if this works for you:
RegExReplace(${Name}, "(?i)\s+inc$", " Inc.")
What this formula is doing is finding any variation of " inc" (case insensitive) with a preceding space, and replacing it with " Inc.". I have tested this within my org and it ignores any situation where there is already a following period, and also ignores account names that include values such as Vince and Incorporated; of course, we always recommend you test this internally before processing any records.
I would also love to direct you to our Modify Formula Dictionary! I keep this on hand at all times in case there's something I am trying to do accomplish but can't quite figure out on my own.
I hope this helps!
a week ago
@JonG_Validity Thanks for the insight on my challenge. I'm still learning the tool, but wouldn't every record selected run through the second step? Meaning, I may be able to change just my desired records name to be something like was " Inc." to. " INC." - my goal is to change each record from a Household to an Organization record type. This would include my Vinces' changing too? The long term goal is to automate ths process to not require human review. I could change all the " inc" to " zzzyyy" and then take a second pass at the data looking for " zzzyyy". This would skip all my Vinces'.
a week ago
Roger,
you should be able to use % INC% which will match to anything with INC in it but has a space before the INC.
Chris
a week ago
@Hydey_Validity Thanks for this idea - I will pursue this option today!!
a week ago - last edited a week ago
Just to clarify further @Roger_Coville , you'll need to use the 'Like' operator, not 'Contains'.
a week ago
Chris - the Like and Contains both produced the same results. Great solve! Thanks again.
7 hours ago - last edited 7 hours ago
@Hydey_Validity I'm onto another Contact change where I want to find "Bob And Mary Smith". I find these using your % And %. I also find "Steve and Lisa Jones"(lower case "a"nd). When I later grab all the characters before the "And" and after the "And" I get good results. AllTextAfterChar(${FirstName}, "And ")). The issue I'm having is it does not "see" the lower case a and so my forula does not grab the second name. I have been searching for documentation on %, but no luck. Is there another way to find only the Large A And? Thanks
7 hours ago
thanks Roger, fun topic for me to look into tomorrow and definitely one for the wider community.
I'll have a play tomorrow am and let you know what I find.
Get industry news, expert insights, strategies, and hot tips, served up fresh every week.
Visit the Validity blog