Parsing Stuff with Flows (Part 1)

Salesforce is really good at a lot of things, but digesting or looking for bits of data within large chunks of text is not a particular strong suite, though to be fair, not may systems can brag about how awesome it’s parsing ability is. I was thinking about this subject since there seems to be two camps, the configuration camp backing workflows and formulas and the code camp backing apex. I am going to propose a bit different way…an adminveloper way.

To start with, we are going to use flows for this. The reason I am going to use a flow is that I want this as object agnostic as possible and I don’t want to be constrained by any of the formulaic limitations (Looking at you char limit of related formulas!). Using a flow will also let me do some fun stuff later on (cough, next blog post, cough), but the biggest reason I want to use flows is the variables.

But, before we get into the nuts and bolts, I need to first talk about how I changed my thinking about this. Let’s say I have some questions in a text field and I want to parse out the answers to these questions. In the past, I might have tried to hard code in some right / left functions, but that is rigid and leads to stuff like “Answer this question in less than 255 char…”, ugh. Instead of trying to build out left / right stuff that ignores questions, you need to think of questions as keys in your document. They are a unique set of text / numbers that should only occur once in your doc, and because we are thinking like this, we can use the FIND function to locate their specific points within the text.

For testing purposes, I am just building out a basic flow with a long text input field that will be parsed.

The flow will look for my “keys” (AKA, questions) and determine how much distance (in char) between the LAST CHAR of a question and the FIRST CHAR of the next question. For the last question, we are going to use a footer as the marker (“Thanks for your time!”).

I am going to do this with a series of formulas, one for each question:

Find out how long the whole piece of text is (This is just a LEN(your text here), not going to expand it out.)
Find out where each question ends
Find out how long each answer is
Build out the answer

Formula number two is finding out where the questions ends. To do this, you use the “find” function, looking for the variable within your text. Now, you need to ADD to this the length of the question since you are looking for the END of the question and FIND returns the char location. Here is what the formula looks like:

find({!varQuestion1},{!Enter_Sample_Text_Here})+len({!varQuestion1})

Now that we know where the first question ends, we need to find out how long the answer is. We do this by subtracting the value from the first formula from where the value of where the next question (or text) starts. I did it this way, though you could have also just used a new find function. In a nutshell, at this point, we have determined that the end of question 1 is X and the start of question 2 is Y and that the answer is Y-X long.

({!FindQuestion2}-len({!varQuestion2}))-{!FindQuestion1}

Knowing this, we are going to go ahead and build out the formula that strips out the answer from the text. It is going to use a combination of left and right functions. We are essentially grabbing all text to the RIGHT of the end of question1 and then taking just LEFT text of that in the length of the Answer

left(
right({!Enter_Sample_Text_Here},{!GetLen}-{!FindQuestion1}),{!Question1Length}-1)

For the last question, you would just use the footer location:

{!FindQuestion4}-{!FindFooter}

FindFooter = find(“Thanks for your time!”,{!Enter_Sample_Text_Here})

This is how this function would look just running it from a flow:

Entered Data

Parsed Out

So yeah, with just a few formulas inside of a flow, parsing can be easy – peasy if you adjust your thinking. Now, before I leave you all hanging for part 2 of this blog post, let’s also take a look at something fun. What happens if someone starts to monkey with the questions? Well, through a little bippity boppity boo we can do something about it, without any more formulas. See we are using the FIND function already in the first formula that finds the end point of the question…and if the find function doesn’t get a match it returns a zero, thus, we know that if the formula that finds the end point of a question returns the length of the key variable something is off. This can be done with just a decision!

Alrighty, that is it for now! Part 2 is going to expand on this FUNctionalitye even more, so stay tuned!
As always, let me know if there are any questions / comments / or you just want to buy me coffee and pick my brain.

Cleaning the data that matters…and not all data matters!

In my previous post, I alluded to a list of 5 concepts that make data cleansing a bit easier (Not fun, not easy – peasy but easier). In this post, I am going to expand on the concepts of “Knowing your data” and “Classify your data”

GI Joe talks about knowing

.But, before we get into the methodology and the doing, let’s talk about tools used. We are actually only using two tool to build out the functionality found with in this post, reports and formulas. However, because the methodologies discussed below is different than most organizations approach to cleaning data (Ocean…Boiling) there will be work on you to get folks bought into the ideas of not just trying to clean everything. So, I guess if you want to get technical, a third tool is the soft grey matter inside your noggin!

First things first. To help me “Know” and “Classify” my data, I am going to write a report that has two bucket fields, “Customer” and “Pipeline”. The bucket fields are looking at two custom field that are rollups counting the number of booked opportunities and the number of open opportunities. These are my two primary classifications because I am going to use a combination of these two classifications to score the value of an account to my company.

1) Non Customer, No Pipeline (Least Valuable)

2) Non Customer, Pipeline

3) Customer, No Pipeline

4) Customer, Pipeline (Most Valuable)

My fictional org for “Kramerica” wants all 481k of their accounts cleaned. Before jumping in and just starting to cleanse, I set up a report that breaks down an account based on past purchases and pipeline. Just by using two bucket fields, I can see that 14,000 accounts (About 3%) that are high value (Customer with Pipeline), 13,000 (3%) are medium value (Non Customer with Pipeline) and 54,000 accounts (11%) that are medium value (Customer No Pipe or Non Customer Pipe). I have just reduced the pool of accounts that should be cleansed by nearly 83%.

Dry those eyes, it is not as bad as it seems

Unfortunately, there is still a number that is not very friendly standing between us and Maragriatville.

Margaritaville is real, google maps told me!

Which is just outside of Dallas apparently.

So, we are going to take things up a notch and write a set of formulas that will score the data that is entered on our account records. The folks in charge of data management (and that might be you), decided that Address, Phone and Website were most important. Yeah, I didn’t put state / country, but that is because of the change making it a picklist field, and we will just assume Kramerica is using the picklists. I am going to end up creating four formula fields. Three formulas will look at the data contained in the three fields. The fourth field will sum the scores of the three fields and then based on the totals, grade the data “Good”, “Acceptable” and “Poor”. The formulas don’t have to be complex, even something basic like if(len(FIELD=0,1,0), which will check for the presence of any data in those fields.

In this case, red is good because red = less work!

That was a fun diversion, now, go back to the original reports with primary / secondary classifications. We add in the data grading field. Now, you can see how many of your most valuable accounts actually need the most help. In the case of Kramerica, we want to distil down that 14% (68k accounts) even further so we can focus on valuable accounts that have a data score of zero (no values in any of the fields) or one (at least one field has some data in it). Applying the formulas and the buckets to my data set reduces the amount of accounts I need to look at from 54,000 to 18,000.

I think this deserves a quick, bullet pointed recap:

– Initial data set, 480k accounts

– Valuable Accounts:

o Customer / Pipeline (Most) 14,000

o Pipeline / Non Customer 13,000

o Customer / No Pipeline 54,000

– Data scoring of valuable accounts:

o Zero data score = 5,000

o One data score = 13,000

– Reduced my “need to clean” by nearly 90+%

I get this way whenever I shake loose a bit more time in the day.

Yeah, that is pretty awesome. However, there is the question of what do to with all those “other” accounts. Here is where it goes from awesome to AWESOME (in a monster truck voice). Since you have already established what makes an account valuable, once an account meets a certain threshold (gets pipeline), you know that it then needs to be cleaned up…and of course, you know what needs to be cleaned up because you are already scoring it.

PS – For bonus points, create a nice email alert telling the reps their data is bad, and make it so it sends them that notice every time they edit the account OR opportunity…just put on a timer so it only sends once per day!

Add followers with Flows

Chatter (in my opinion) is one of the really neat features of salesforce, allowing users to follow records that might be important to them is just one of the “Killer Apps” of this functionality. Ironically enough, being that this is a social network, is the request for that one can just add followers automagically if “X” condition is met. There are even a couple apps on the appexchange for that.

Automated Networking, Jumbo Shrimp, Stop / Keep Moving, OxyMoron

To me, this takes a bit of the social out of “social networking”, so I am going to sketch out a solution that is a bit more personable but still makes it easy to add a follower

In a nutshell, it goes a little something like this:

1) Within a record, the current user feels that another user (or themselves) should be on the follow list

2) The current user clicks a button. In this case, I have called it “Pied Piper”

3) A flow is launched that does the following:

1. Looks up a user based information given

2.Attaches that user to the current record as a follower

As usual, I am just sketching going to provide the foundation for this functionality. The magic here is that you can flows to do some neat stuff like attaching multiple users, adding comments, tags, etc. You don’t have to add this stuff in, but I would encourage playing around with it to see what you can / can’t do. Also, a quick Caveat. I found that a user will have to have “Modify All” permissions on the object you are pushing this out to.

That aside, now we get to the Nuts & Bolts of the matter. I have got a couple of components that I am going to be using:

A visual flow
A custom button driven by a URL
A nice cup of Coffee (Optional, or not)

This flow function sounds exciting and awesome (Follower Updates, Magic!), but is really basic. There are four components to this flow, Entry screen, Record lookup, Record Create and the confirmation screen (optional). There is however a critical variable, varAccountID. This is the variable that houses the ID that is passed over via URL. Just be aware when you are building this out!

Salesforce Flows, How awesome

On the entry screen, there is a text box for entry of a person’s name. The text box value is passed to the Record Lookup. One of the easiest improvements would be making this multi search (name, ID, email), just something to keep in mind.

The flow does a look up on name, and returns the userID.

Next up is the Record Create on the object Entity Subscription. In this case, the parentID is the accountID, but it can be anything. SubscriberID is just the UserId pulled above.

Once the record is created, there is a confirmation screen. This is an optional step, but something I like to do.

Now that I have a flow in place, I create a button (Pied Piper) on the account layout.

1 story point was spent on the clever name

This button is nothing fancy, just running a hyperlink that will open up the flow AND push over the accountID of the record the user is currently in. I did add the retURL on the end so that instead of a loop, the user is returned to the accounts page.

Just to reiterate, this is the foundational work. You could implement this into production and have something that works but it isn’t going to be awesome. Here are the required proof shots!

As always, questions or comments, hit me up!

andrew

What (approximately) 48 hours with Apex has taught me

Friday afternoon, around 2:00 I got a message telling me to check out SFDC99.com. At first, I was skeptical, here is yet another blog that is going to teach me Apex / java/ html etc…I had read it all before and have failed just about every time. Part of it was how the knowledge was presented, part of it was me saying “It is 20XX, why am I still having to write lines of code” and part of it was that I can do a BUNCH of cool stuff already with the goodies in Salesforce, what will learning Apex do for me.

Where we are going, we don’t need code

This blog was different though, the tutorials were written in a conversational and logical manner that made sense and also didn’t come across as a developer talking to a kindergarten class.It also dawned on me…I was doing this stuff already. Sure, it might be through flows or crazy clever formulas and workflows, but the logic was there. My bias was pretty much erased completely once I saw this question:

1. If you can solve a business need using either a workflow or a trigger, which should you use?

Always use a point-and-click solution (workflow) when possible!

It’s easier for your team to create and maintain workflows

Workflows are easier to find when debugging unexpected behavior in your org

Workflows never break!

You don’t have to write test classes

Pretty much every developer I have ever worked with

In internet time, this meme is so old it farts dust

The reason this question really hit me was because I have ran into MANY developers that are code first, ask questions (and write documentation, test, get feedback on, deploy correctly) later.

Having seen and supported the aftermath of this type of developement, I really took a to the whole #ClicksNotCode and #ButtonClickAdmin philosophy to heart. Seeing a developer actually answer this question like I would was like a golden ticket.

First time your class runs with 100% coverage (even though trigger is 5 lines)

It told me I could still believe in all the good stuff that makes a system like Salesforce.com great WHILE learning the dark side.

So, it has been approximately 48 hours since I started doing this thing, and here is what I have learned:

1) Just because you know code doesn’t mean you know salesforce.

2) APEX is way more intuitive than whatever code you might have tried to stumble through last time.

3) I am really thankful for my time supporting traditional CRM systems (Cough, Siebel, Cough) and having to muddle through gnarly SQL statements.

4) Having to have 75% code coverage is tough, but if you think about what you are trying to do with your trigger and then build out from there, you will do good.

5) Try and think how you would write a trigger to handle every transactions like getting coffee.

Next steps? Well, I just got my Head First Java book and have been working through that. Honestly, so far installing the JAVA SDK seems to be the hardest part (and I have installed Linux!), but that really isn’t a surprise since it is an Oracle Product.

The new westcoast beef

I am also going to keep plugging away on the SFDC99.com tutorials. I have went through Chapter4 and am going to spend some time here getting a better grip on the basics before I move along. I am also going to keep “doing what I do” in terms of new functionality in SFDC. I love all the fun stuff like flows and workflows, but much like a padawan with a new lightsaber, I am going to try and use it as much as I can.

Nuts! My lightsaber test class only ran 45% of the lines!

I missed class picture day

Andrew

Delegation Made Awesome (Salesforce1Selfie Number2)

With my first salesforce1 selfie I took on user setup and reduced my “Click Load” when it comes to setting up users.

For this salesforcesforce1selfie, I am taking a swipe at the delegate process.

The org I support has quite a few approval processes and within these processes are a mix of related user (manager, for example) and named user approvals. Supporting these processes can be quite a challenge, especially around summer time since we are not using the delegate functionality. We have tried to roll out delegates, but never quite get off the ground with adoption. After taking a close look at the delegate function, reviewing notes from our last attempt and talking to the SFDC_IT team member I believe the lack of adoption is due to:

1) No obvious click path to delegated approver screen (Click on Name, Click on Drop Down, Select “My Setttings”, Click on “Approver Settings”, enter delegate )

2) Unable to start / stop time delegates

3) Can’t turn on via mobile

4) Too much mail if the Delegate is left “on” (See #2)

I believe that if I can add functionality that will help with the above, I can increase adoption of delegates. At the very least, like my clone user app, I can use it myself during the summer holiday season!

Hold on tight, here we go!

We got a long way to go and a short time to get there!

First things first, I needed to create an input method for my users that could be both easy to use and upfront. Therefore, I built out a custom object / tab combination, which I named “Out of Office”.

Can’t miss that!

I have as few entry fields as possible, both to speed the time to release but to also make it as easy as possible for the user:

1) Start Time – Defaulted to current date

2) End Time – User selectable date field Three Fields, one defaults

3) Delegated Approver – Lookup field to user table

*Now, you might be reading this and shouting / typing furiously/ muttering about missed opportunities. I agree with you, and I feel certain that future revisions will get new / better functionality (Filtered Lookups, Approvals, On behalf of…etc), but this is about a good foundation to build on.

A quick look at workflows and I can see that I will have to do this with a combination of workflows and flows since I cannot update the Delegated Approver field with workflows, and

You know what I am talking about

In the interest of time, I am going to give an outline of the methods used to get this functionality up and running rather than the step by step.

Step 1, FUNctions used to update the delegate:

Workflow “OOTO Created” is triggered up on record creation. This workflow has two immediate actions, an email alert to both the delegate and the record owner and a flow trigger that runs a flow in the background that handles the actual update to the delegate. The flow really is basic, just a record update. I threw in a lookup out of habit.

The workflow also has a timed based action, which is the secret sauce. This time based action is based on “return date” – 0 and ticks the box “Back in Office”. Do not overlook the re-evaluate workflow rules after field change check box!

2014-04-30 08_30_30-Field Update_ Mark as returned ~ salesforce.com - Unlimited Edition

Step 2, FUNctions used to remove the delegate

Workflow “Remove Delegate” is ran when the field “Back in Office” is set to TRUE. This worklow has two elements (as I wrote it), but could get away with one. The main element is the flow trigger. This trigger launches another basic trigger that just deletes out the delegate from the record owner. The only thing special I did on this was to put in an extra field update in the flow so I can tell if it ran. I have a similar field update in my workflow. I found these to be very helpful when testing because I could visually observe how my updates were working.

2014-04-30 08_42_16-Workflow Rule_ Remove Delegate ~ salesforce.com - Unlimited Edition

Move along, nothing to see here

Now, for the brass tacks, how does it work? Well, it works pretty dang good. I have been monkeying around with this for a couple weeks now and I can attest to the basic functionality. The only real weak link in the process is that I cannot directly influence the field “Receive Approval Request Emails” setting via the flows. That, however, can be handled with business process.

Curse You!

Now, for the 10,000 dollar question. How does it look on Mobile? Well my friends, it looks pretty darn good. Salesforce1 does such a nice job of packaging up the objects created that you get a very nice experience out of the box. This is such a nice contrast to previous mobile versions. I can focus now on making the user experience that much better.

2014-04-29 07_56_56-Presentation1.pptx - PowerPoint

Yeah, that is awesome

For example, I built out a field that shows a message based on a couple parameters: “if(Back_in_Office__c =FALSE,”Out of Office, returning “& text(Return_Date__c) ,”In Office”)”

I added this field to a new compact layout, and viola, the user can now, at a glance, see if they are in office or out of office, and if they are out of office, when they are coming back (NEVER!)

Awwww

Woo Hoo!

So there you have it, another piece of adminsitrivia solved with Salesforce, Visual Flows, and Salesforce1

Andrew

	atjohnson9 on Fuzzy Searching in Salesforce…
	Matt on Fuzzy Searching in Salesforce…
	atjohnson9 on Calculating number of months b…
	Sarah D on Calculating number of months b…
	Parsing Stuff with F… on Parsing Stuff with Flows (Part…

SFDCinSeatown

Blogging about Salesforce from the land of Craft Beer and Fine Coffee!

Administrivia