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

Advertisements

using a formula field to generate random numbers in Salesforce

Standard

In the course of getting “Project X” on the road to AMAZING I had to figure out how to get a random number out of salesforce. Since I am not a coder (Clicks not Code FTW!) I went through the formula documentation on success.salesforce and could not find anything like the excel function “random”. The google machine had suggestions, but they were all code driven. This is an interesting post as well. (http://blogs.developerforce.com/developer-relations/2013/07/selecting-random-numbers-and-records-on-the-force-com-platform-part-1.html)

So, what’s a poor admin to do? Well, if option A doesn’t work and option B is not going to work, this admin gets out his thinking cap (Seattle Seahawks throwback, btw) and gets to work.

First, I had to think about what I was trying to do. Am I really trying to make a random number? Do random numbers exist at all? The truth is, I can give you random numbers all day long and more than likely, you will find (or think you have found) a pattern. Humans are exceptional at doing this, and it has been theorized this helps with the whole Fight or Flight instinct (Is that the bushes or is there really a tiger there?).The lesson here is that as long as the user thinks it is “random” it can be called random.

Thus, properly aligned on this concept, I go off on my merry and random way and cooked up a solution.

Now, before I go into pure SFDC formula awesomeness mode, I want to call out that this is probably my 50th iteration (maybe slight exaggeration) of this functionality. BUT, I want to also call out that Salesforce is probably the best platform I know of for this type of work. Quite literally, I would have the tab view up on the screen and in a new chrome tab the formula field for constant tweek / refresh. So, back to the awesomeness.

I decided that I will need two things to get my random number:

1)      A pool of numbers

2)      A “dice” function

To generate the pool of numbers, I decided to use the NOW() function along with the square root function. The NOW() function gives me an ever changing value and the square root function gives me a way to expand on that changing value. At first, I tried using just the minute function to get my pool, but ran into issues because the numbers were quite large enough. That is not to say it wouldn’t have worked, but it just didn’t make me happy. Then, around PI day (3/14) I had an inspiration! I remembered that there were certain sequential dates that only occur once in a millennia like “11/12/13”. I already had a really REALLY good seed, the date itself. This is because NOW() returns Month/ Day/ Year Hour / Minutes / AM PM. That might be a bunch of text, but if you break it down a bit more, you will see where I am headed:

NOW() = 3/19/2014 9:25 AM = 3192014925 = 31920141625

That last value might look a little funny and that is because it is. I noticed that when I ran a formula to derive hour from now <(left(right(text(now()),9),2)))> the return result was actually not matching with what I saw. Turns out, the hour value was being formatted into 24 hour UTC.

Deep Breath! With all this said, what I have is this great formula that returns a number that WILL NEVER HAPPEN AGAIN (Kerplew, Mind Blown and it is only Thursday) and can now work my random magic on it, and by magic, I mean the square root function.

The reason I use this function is because, well, it generates a gnarly set of digits. According to the Wikipedia page, “In mathematics, a square root of a number a is a number y such that y2 = a, in other words, a number y whose square (the result of multiplying the number by itself, or y × y) is a.[1] For example, 4 and −4 are square roots of 16 because42 = (−4)2 = 16.”, in other words, for every number, this is another number that when multiplied against itself will return the first number.

Thus, I get the following number formula with 9 digits after the decimal:

sqrt(

(value(

      (left(right(text(now()),6),2))&     

      text(DAY(DATEVALUE(now())))&

      text(Month(DATEVALUE(now())))&

      text(Year(DATEVALUE(now())))&

      (left(right(text(now()),9),2)))

)/100

)

There are a couple things I need to call out:

1)      I put the “/100” in there because the returned number was so large and the differences (minutes) so minute (ha) that the square root function was not returning really good results. Once I threw that “/100” in there, I started to get much more amazing data.

2)      The order of operation starts with the minute function. This is to keep things more random. Basically, if you started with the Date values, the leading digits would be the same for 24 hours. This way, they are refreshed every minute.

Now that you have a pool of numbers, you would need to generate your “dice” criteria.

The dice is what selects from the pool the actual number. In excel, this is similar to the “RANDBETWEEN” function. I put together a quick dice criteria in excel so that I could demonstrate the randomness. It uses a combination of the length of the user’s first name & the numerical position of the first letter of the first name. What this means is that ANDREW = 6 digits and A = 1, so my “dice” number is 6-1. IF my first name was BART, then the “dice” formula would be 3 (BART = 5 digits and B = 2). I used this “dice” formula on my pool results, and for it being a thrown together criteria, I did see some very nice and random results. Unfortunately, chatter posts are not very nice with formatting that data, so you would need to look at the attached docs!

And thus, with this knowledge, you will be on your way to generating your own take on this, which is what will truly make your number random! Now, was this all just for fun? Nope! One possible application for this could be for the assignment of incoming records like leads or cases where you might not want a round robin or first in / first out rule setup.

 

Thanks for playing along! If you like this, be sure and share. If you have questions, well, let me know!

Using a Salesforce formula to determine if a date is current or previous fiscal year

Standard

I wrote out this formula because there was a need for our customers to be able to quickly see if a date was in or out of a current fiscal year. The key component of this formula is to have a way of thinking that doesn’t think of a year as January 1 to December 31st but rather from “First day of Fiscal” to “Last day of Fiscal”.

 This formula could be used as a base if your company had a narrow criteria. The particular requirement I had was for plain text results for easier reporting.

CASE(
(if(Month(today())>9,Year(today())+1,Year(today())))-(if(
Month( <<YOURDATEHERE>> )>9,Year(<<YOURDATEHERE>>)+1,Year(<<YOURDATEHERE>>)
)),
1,”Previous FY”,
0,”Current FY”,
“Out of Scope”)

 

The nice thing with the plain text is that you get a data set that makes reporting SUPER easy because you can then group on that field and have subtotals running.

 

Enjoy the weekend!