Fuzzy Searching in Salesforce Flows

Standard

Salesforce flows are a magical thing, like good coffee at work and unicorns.

coffee unicorn.jpg

This is how I want my coffee delivered.

 

I have come to realize that there are limits to even the most magical things (BOOO!).

The limitation I have found in flows is around how you search for records…but, let me be clear, it really isn’t a big deal. For most people, finding records using the typical “equals”, “contains”, “starts with” and “ends with” functions will work just fine. I however, found myself going down a path where that wasn’t going to quite cut it. The challenge is how to search on a text input in a manner that is more google like. I don’t know what the kids now a days call it, but back in my day, we might call if “Fuzzy Matching”.

Fozzie_bear.png

Fuzzy, Not Fozzie!

For example, let’s revisit my Jaeger Dispatching System (Special Note, Pacific Rim 2 is scheduled). This system uses dynamic drop downs for the Jaegers, but, what happens when you have a bunch of Jaegers’?  You can build out a record search based on a text box, but, as mentioned before, you are limited to “equals”, “contains”, “starts with” and “ends with” functions. At this point, you might be saying, fine, just use a “contains” operator…and that would work, except what happens when the user spells something wrong? Contains is just two wild cards, so if you entered in “Dangur” instead of “Danger” you would not get a hit.

 

What happened next is pretty funny. I started thinking about how there are patterns in words…and then started thinking about how the pairs of letters could work. Spoiler alert, it totally worked. I proved it in Excel by just using a vlookup and some formulas, so I got pretty excited. It was then that I googled this matching pairs thing and found out that yep, some one wrote about all the fancy math behind it.

http://www.catalysoft.com/articles/StrikeAMatch.html

So, after building out the proof in Excel that this would work, I set about trying to get this to work in Salesforce…specifically a flow. I want to be able to enter in some characters into a text box and see returned results based on how well they matched my search term.

The first thing I did is create a field that removes the spaces from the Jaegers’ names and made them lower case. I don’t want it being “Cherno Alpha”, I wanted “chernoalpha”. This is done via formula field. If doing this in real life, I would build out my formula to also remove punctuation, but this is just for my Jaeger dispatch and my blog, so I am just removing spaces.

Thus, concludes the non flow part of this blog…what follows next is like 100% awesome, you have been warned.

TAKETHISCAT.jpg

Woot! FINALLY got to use this meme!

The first couple things are pretty basic, I have a start screen that has a text box. Next, I have a fast lookup on my Jaeger object. This gets all the Jaegers and puts them all in a collection. Next up is my loop, where I go through each Jaeger record. The only “gotcha” is that when you do the fastlookup, you need to bring over your formatted text from the record. Screen shots of this would be pretty boring, so here is a picture of all the giant robot toys.Jaeger Toys.jpg

Within the loop, I start with two assignments that copy the formatted Jaeger Name (no spaces) and the search string to variables.
I am going to use these variables for the rest of the functions, including the formulas. I take the search string that was entered and format it up.  I remove the spaces, count how many pairs I have and if the value has a remainder, I also remove the last character. I do this because otherwise the matched pair logic would be searching on a single digit, which would skew the results. The formula also checks if the search string is just 3 char, and if it is, it will treat these three char as one “pair”. Pictures and Text!

Formatted Search String

if(len(substitute({!Search_String},” “,””))<>3,
if(
mod(len(substitute({!Search_String},” “,””)),2)<>0,
left(substitute({!Search_String},” “,””),len(substitute({!Search_String},” “,””))-1),
substitute({!Search_String},” “,””)),substitute({!Search_String},” “,””))

Now I have to compare my first matched pair to the formatted name. of the search string that was formatted. I use a formula to get my matched pair:assignment - formatted search string.png

if(len(substitute({!Search_String},” “,””))<>3,
lower(left({!varSearchStringUseCopy},2)),lower(left({!varSearchStringUseCopy},3)))

 

The flow will next do a name check via the decision function. If the Formatted Jaeger Name contains the current matched pair, the flow adds a value of 1 to the counter variable and adds the current pair to a variable that will show the matches and the ID of the current record to another variable. Ugh, that was hard to read, here is a picture:assignment - match found

If the pair is NOT a match, well, I don’t really do anything with it but you might want to shove it to a debug variable. In fact, if you are starting this from scratch, I would HIGHLY suggest you do this!

Next up is yet another assignment, this time though, we are removing the pair was just searched on. This is done with, you guessed it, a formula:

if(len(substitute({!Search_String},” “,””))<>3,
substitute({!varSearchStringUseCopy},left({!varSearchStringUseCopy},2),””),substitute({!varSearchStringUseCopy},left({!varSearchStringUseCopy},3),””))

remove search string

Once that is completed, we check to see if there are more pairs to check. This is done by looking at the number of pairs left after the current pair is removed. This is in a formula I like to call “DisappearingSearchString”:

len({!varSearchStringUseCopy})

The whole process looks like this:

match process

If all the pairs have been used, the flow then checks to see if there were any matches with a decision point on the counter variable. If yes, then we add the matching data into a variable and then start the loop all over again. Because we will want to display some results in a table like format, be sure to append a text template that has a line break at the end of the string. To do this, create a text template with <br> in it.

build out result line.png

One of the things I really wanted was a way to see how many of the pairs were found in a given record. I do this with this formula:

({!varTripCount}/{!frmSearchStringPairs})*100

 

Before the loop starts all over again, any used variables are reset:

clear counters.png

So, enough talk! Let’s see how this works! For comparison purposes, I put in an alternate lookup that will use the “Contains” search function of what was entered in the input.

I also added more Jaegers, 8 in total!

Jaeger List.png

boom

 

First search string is going to be “Eureka”:

Eureka.png

and here are the results:Search Results - Eureka.png

The “Contains” function did what I expected and returned two records, “Striker Eureka” and “Eureka Smack”. However, it did not find “Striker Eurek”, but the matched pairs function did!

Let’s try this on another scenario. Suppose there is someone new in the Jaeger Dispatching Center and they forgot that it is “Cherno Alpha” and they enter in “Alpha Cherno”.

Search String.png

The “contains” search function would literally return ZERO results, whereas the matched pairs function would show an 80% match with Cherno Alpha.Results - Cherno Alpha.png

So, there you have it. With a bit of work, you can do fozzie…err…fuzzy search results with in a flow with Zero coding!

As always, if you have any questions, comments, or suggestions, please let me know!

Andrew

Advertisements

Attaching a copy of an email alert in Salesforce

Standard

How long has it been? A long freaking time!
Long timeBetween summer vacations, coaching soccer and cub scouts, there has not been much idle time for this boy to blog, which stinks because there has been so much fun stuff figured out!

But, here I am, eating a turkey sammich, listing to some EDM on Slacker Radio and trying to get a blog post cranked out for you all.

What I am going to blog about is an annoyance that has been going on for quite some time… the issue of workflow email alerts. In fact, this idea is over 5 years old!

See, when you build out an email alert and it fires…well…it just fires. You can do a work around that involves carbon copys to an email to salesforce box or creating activities, but those workarounds are either not scaleable or they don’t give you much more information than “this email was sent!”.

But, I did find a work around that is scalable using my favoritest thing ever, flows*. Now, did you see that little thing there? That Asterix? Yep, that is there for a reason. It is there because I wrote this out using flow triggers. I am sure you can do this with process builder, but I am just documenting the flow trigger function here. I like process builder, but in this case, I just need to crank something out quick, so I am using triggers.

The “Too Long; didn’t read” version is this. I created a flow that runs via the workflow (remember, I am using flow triggers!). When that flow trigger runs, it will do two things. It will send off the email alert AND create an emailmessage record. Yeppers, I said create an emailmessage. What I have found is that within salesforce you can create an email record through a flow and it will happily sit there. The trick is that you will need to pass the record ID from where you are starting and use that ID for the “parent ID” field on the emailmessage record.

This is a good jumping off point if you want to venture forth and tinker around yourself. For those of you that want more details, here ya go!

My assumptions at this point are that you are comfortable creating your own method of running this flow. As long as you can pass variables over from the record, you should be good to go. This flow only has two elements. The email alert (which is already built) and the record create for the email message. There are a bunch of things you can build out when you create the email message, but I am just going to cover the basics.

  • Parent ID – this should be the record you want this emailmessage living under. In my case, it was, well, case.
  • Status – Status is a funny one. It is displayed in plain English but the value is actually numeric. In my case, I am recording a copy of a sent email, so I used a status of 3, which is “sent”.
  • EmailMessageBody – An interesting thing about this method is how the text is displayed. You will actually be creating the emailmessage.body by using the field “HTMLbody” and to make it so the text looks nice, you will need to do the following:
  • Create a text template called “LineBreak”. This text template will just have the HTML value for break “<BR>”

Line break

  • Create a text template called “Email body”. Enter your data and insert the text template “linebreak” wherever there should be a line break. If you need to get values from the parent record, insert them here. I choose to pass them all over via the flow trigger, but you could just as easily do it with a requrery.

Linebreak being used

The end result is that the emailmessage you created via the flow will have a similar look / feel to the email that was sent out via the alert. Actually, it will look way more like the Text version, but really the point is to have this copy not so much to look pretty.

As I said before, I will leave the method of running this flow up to you, so let’s talk about the results.

By doing this, I was able to get a copy of the alert email attached to the parent. I was then able to remove the activity creation on the workflow since that was how we were noting that an email was sent out. The email copy is an actual copy, so we know what was sent out, which you couldn’t do through the activity. As an added benefit, running this via the flow allowed me to do some extra shenanigans that you really cannot do through a workflow, like running queries on other objects and adding them to the email.

The last benefit is crazy obvious, this is a LOW maintenance function. If a new value is added to the email alert, I (or another admin) just needs to add it to the record creation function…no dev time needed!

As always, let me know if there are questions / comments

 

andrew

 

 

2 cents on Salesforce Process Builder

Standard
Quarter

OK, maybe .25 cents worth!

I started playing around with Salesforce Process builder and I figured I would give the world my 2 cents on this new functionality!

The process builder is a HUGE step forward. It is really cool, especially considering this functionality is less than a year old. If you were amazed by what you can do with #ClicksNotCode before, this will be mind blowing.

3 things I like:

  • Up to 5 decision points. Raise your hands if you have ever had to dissect a HUGE workflow with multiple gnarly logic steps…OK, this one is for you all. Stop weeping, process builder is going to help tremendously. What I like is that you can have up to 5 conditions grouped under a process that run really as separate statements.
  • Run LOADS of Actions. There is NOTHING quite a fun has building out a super awesome workflow and then rebuilding the logic so you can run an approval process #sarcasm. One place for all this stuff now!

Only one not present Make me coffee

  • Great UI. I love Visio. Seriously, love it. The UI of process builder makes documentation a snap since you can just grab a screen shot and see in pictures what the heck is going on. You know what would make this better though? A Nice “Click here to Print” button that would print out the process along with all the “stuff” with it. Yes, I used “stuff” as a technical term.

3 things I don’t like:

  • Versions are a PAIN. To be fair, this is a beef I have with Flows as well. I should be able to deactivate, make changes and then reactivate. One of the MAGIC things with workflows is that you could make a change, save it, test it and be done. With Process Builder, you have to clone, enter a new name (WHY!), save then activate. If you find something goofy, guess what, same dang process. Much like flows, you very quickly generate a TON of versions.
pain

Speak it Grumpy Cat!

  • Can’t edit an inactive version. Yep, this is another versioning thing. One of the things I particularly like about flows is that I can step into a previous version, make edits and save it. Of course, there is a warning that I cannot over write the previous version, I have to save it as a new flow or new version of the flow. Sometimes, if your versions are different enough, you have to dig in and see what you could have done differently.
  • Replacing Precision Tools. You could create a flow trigger stupid fast. You could do it from multiple screens and you could edit it after you have associated it with a workflow. Here is a corny analogy. Let’s say that you noticed you have a screw loose on a piece of furniture. To tighten the screw, you would go and grab the cool screw driver with multiple bits out of your tool box and tighten the screw. This is like using a flow trigger. Just the minimum to get the job done! If you were to do this same action the process builder way, you would grab the WHOLE toolbox which includes tape measures, hammers, pliers, pencils etc…and bring it back to the furniture. Sure, the toolbox contains the screw driver and you are accomplishing the goal, but you just don’t have to lug that toolbox around.

So, that is that. Let me reiterate again…I love process builder and I think it is a  fantastic piece of tech, but don’t take away the precision tools because the tool box is getting fancier!

Fun with Salesforce Flows – Parsing Multi Select Picklist fields

Standard

Fun with Flows – Parsing Data

I recently ran into a challenge while making / scripting / conjuring a fairly complex flow. I needed to get data out of a Multi Select Picklist (MSP) field for use later on in my flow. The google hive brain did not help at all…literally, I couldn’t find anything when googling on terms such as “salesforce visual flow parse”. Once I started googling ““salesforce parse” I got lots of results…for apex solutions.

So once again, I find myself faced with the following:

1)      Check my “Clicks Not Code” membership at the door and start down the apex path?

2)      Write some crazy formula field to do this work and then pass data back and forth?

3)      Put on my thinking cap and figure out a way to get this done with in a flow?

And as usual, I opt for #3.

First things first, I had to figure out what I had to work with. To do this, I extracted some data from my MSP fields and found that MSP’s store their data in a very logical way, ((“Value””SemiColon”)). What this means to me is that when I run the query on the object and push MSP field data to a variable, it is going to ONLY bring over the data that has been selected and it was going to show up as a text string. I had a hunch that the MSP would act this, but you know what they say, “Trust but verify”. This behavior also reinforced why I wanted to parse the data. Carrying around a full string like “Value 1; Value 2; Value 3” really reduces what you can do within flows.

Now that I know what I am working with, I can go about getting the data into the format I want. The quick synopsis is that I am going to:

1)      Create variable for the MSP Values

2)      Create decisions for each MSP Value

3)      String the decisions together

For this example, I have the MSP field “McDuck” that contains the values Huey, Louie, Dewey, Donald, Daisey and Scrooge. I want to separate out any selection into their own variable for use in other parts of my flow. Here are the steps I am going to run through (Visual in the PDF).

1)      Query “GetData” passes the values in the field “McDuck” to “varGotMcDuckData”

Image

2)      The data in “varGotMcDuckData” is then ran through a number of decision setps.

     ParseHuey takes a look at “varGotMcDuckData” and if the data in that variable contains “Huey”, then populates “VarHuey” with “Huey”

Image

      ParseDewey takes a look at “varGotMcDuckData” and if the data in that variable contains “Dewey”, then populates “VarDewey” with “Dewey”

Image

3) Rinse, wash, repeat as needed!

There are a couple things important to keep in mind when doing this. The first is that these can get really big really quick. Don’t be afraid to use a subflow to do the dirty work (Future Topic!). Also keep in mind that once data gets passed into a variable, data is retained there until the flow stops. If you are going to have a multi-step process you need to build in a clean step where the variables are scrubbed of data.

Even though this is a fairly elaborate process, it really does go by quickly. The added benefit is that flows are recyclable, so if I ever had a need to parse out the McDucks in a flow anywhere, I could reference my parsing flows over and over and over again.

 

Andrew