Get your smarts on over Advanced Salesforce formulae

Something I have blogged and tweeted about a couple of times in the past is writing quality, advanced formula fields. Having a good grasp of Salesforce formulae reaches beyond just formula fields though, as we encounter this mark up in Workflows, Approvals, Validations rules and numerous other areas of the declarative side of the platform.

Badly written formulae can quickly start to stack up in your org (and I mean that literally - if you know how Formula dependencies are inter-compiled) so why don't you stop the rot before it begins with a new exciting Trailhead module... Introducing:

 


Trailhead reckons this module will take about 2 hrs 30 mins and we know from past experience those estimates are pretty accurate, but this module is nicely broken up into 7 chunks, none more than 30 minutes, so again, as we know from before we can easily fit these bitesize chunks into our regular, busy days. I'm actually on holiday today, and so I am going to secretly work through these modules as Laura fusses about the house doing Christmassy things.

So, First

Laura: Shopping on "notonthehighstreet.com" for a novelty cushion as a Christmas present

Simon: Using Basic Logic in Checkbox Formulas

The first thing I had to overcome is that as a "Latin English" advocate, the plural of formula to me should be formulae, but I will let this go, that is not the challenge here.. the challenge here is getting the basics down with formula fields, and understanding the core functions and operators. As I came into this module, I was honestly expecting the use of some things like ISBLANK() to be the climax of advanced formula fields, so even I am now a little nervous about how advanced this might actually get! There is even a little bonus Back to the Future reference in this module. Which you can't miss.



As a personal aside to this module, I would have to say that I have always found it useful/better to actually use the "Insert Field" button to drop fields into formulae... I also quite often use the Function picker on the right to build up my code. Even though typing these things isn't hard. I think it makes it more methodical (and does reduce typos) than just freehanding it all in. The logical flow which you might type "this and that if that or this" might not be the most efficient way to actually evaluate something, and using the function selector makes me evaluate that a little harder.

 

Anyway, no distractions by personal techniques. We are here to earn all the badges. And besides, Laura has stopped shopping... it appears someone in our family is getting one of these next week:

(I'm lucky none of our relations read my blog...)

Module 2

Laura: Making posh coffees with the machine in the kitchen

Simon:  Using numbers, currencies and percentages in formulas

This is a really good topic, and probably one where I fall foul of the rules more often than not. There is a sub-section in here on the solid use cases for these three different, specific formula types. I'm afraid that I am a bit of a dinosaur myself, and I still don't "believe" in these rich variable types. I just make everything a number, and then deal with the formatting, processing and collateral damage of doing so myself. I could definitely learn something here.

I have also have trouble in the past with "divide by zero" errors in formulae, so it is worth noting the final paragraphs on "treating blanks as zero" - but you should also get into the habit of thinking about zeros being valid amounts. How often do you create a number field with a default of zero for initial values? Consider using an "IF( field__x == 0, 1, field__x)" to instead "divide by 1" - which of course has "no effect" when there is a nill value. (obviously check this makes sense to the business logic first).

Laura rattled through those coffees, so no time for screen shots. A quick espresso and onwards!

Module 3

Laura: Gone upstairs, I think she's on the phone to her mum

Simon: Using Date and Date/Time formula

Ironic this because if I keep up Salesforcing on holiday, I might not get many more dates..

Once again there was some interesting learning in here even for an old hand like myself. DATE functions in formula fields are incredibly powerful. Coming from other programming language libraries being able to call out instances of TODAY and LASTWEEK in reports and rules has always been quite simply dreamy.

The challenge on this module is a little odd, in terms of "business value".. I would probably have one of our BA's go back to this client to establish quite why they wanted the current day of the week on their contact page, but it's still a good example that uses a clever trick outlined in the module ;) - as an extension to this, you could consider taking a Date of Birth field on the contact page, and get Salesforce to output the day of week this contact was born.. You could then even cross reference it with that famous poem about "babies born on a monday" to augment a Description field...

Either way, stack up the points, and prepare to move on. I have to go out now, I have a feeling the conversation is going to be a distant background noise to me now as my head buzzes about the possibilities of the next module.

Module 4

Laura: Back home scrolling instagram for wedding ideas

Simon: Using Picklists in Formulas

I know who's having more fun on the sofa right now (I hope she doesn't look over at my screen). There is one resounding rule regarding Picklists in formulae that they address right at the head of this module: ISPICKVAL() - never forget this.

 

As much as Picklists can LOOK like text, ACT like text and in Apex be TREATED like text - in formulae, you need to address them with the ISPICKVAL function to compare them. I have lost count of the number of times this has caught me out and you eventually conceed "ooohhh, it's a picklist, thats why it doesn't work.". This module makes a good highlight of this, and explains the options you have in the form of CASE and TEXT as well. Most comparisons I make though tend to need ISPICKVAL - as you are plucking out one possible status value for an sObject and then asserting a bunch of rules around it.

Module 5 - the game is up

Laura: Discovered I was on Salesforce, has gone into the other room

Simon: Using text in Formulas

This must be one of the biggest uses of simple formula fields in Salesforce - text manipulation. The examples in this module are exactly on point - concatenating names, turning numerical values into words (like "Hot" and "Cold" leads) - but did you know you can do so much more with text in formula fields? Just last week I tasked one of my colleagues (a fairly new start) with the challenge of replacing a "dirty product" flag with a "red light image" - he stared at me in horror, until I told him to quickly Google it... if I'd know this module existed, I would have sent him straight over here instead.

That is exactly what you are about to embark upon in the challenge. Generating a five-star-review icon in place of a "1-5" numerical grade for Lead quality. This kind of formula field should be kept somewhere safe, as once you know how to do it - you are going to want to put images ALL OVER your page layouts. (with a bit of tinkering, you can get a whole raft of standard salesforce icons, such as traffic lights and 5-star rating, but also Tab icons and other standard imagery, so go have a poke around...!)



I now have to go and make lunch I think, to re-establish communication lines here at home, especially as the next module starts with a video, and I don't have headphones to hand.

Two modules to go

Laura: Has put her shoes on, I think I'm in luck - she's off for a walk

Simon: Levelling up with Advanced Formulas

It seems we have now learnt everything we need to know about advanced formula fields, much like the prodigy in every kung foo movie - it is now time to use what we have learnt. There are some really good tips on best practice formula practises, especially for longer formulae, with a nice 12 month example (which as I read I was sure, this can be simplified) - that is then smashed down to a super-simple 4 liner.

There is a really interesting article linked out from this module "Tips for reducing formula size" - and I am genuinely going to print this out and keep it in my desk drawer at work. My comment in the second paragraph about inter-compiling formulae is addressed in this document, with really good examples of how re-using another formula field repeatedly inside other formula fields will really, really quickly blow you out of the water for maximum formula compile size. Give this a good read, I have found myself applying the "when all else fails use a workflow rule" chapter at least once.

The challenge on this module involves building two formula fields, so read through all the points before starting on it.. you actually want to develop from the bottom up on the challenge bullet points as you will reference your second formula field in your first (which you can't do until you have it).

The final module

So there has been a little beer drinking between the advanced formula module and now, but I have always maintained I do my best coding when I'm more "relaxed".. (I'm not sure how my employer would feel about that mind)

Laura: Wrapping presents on the floor

Simon: Troubleshooting Formula Errors

As I read down these common formula errors, I couldn't help but notice that I was just slowly nodding my head along in agreement with everything they said. Anyone who has worked with formula fields for a few years would have to admit or agree to these errors at some point or other. My suggestion of using the "Insert Field" and double clicking method names really does mitigate errors relating to missing parentheses or parameters because the wizard will fill them in for you as you go.

The second half of this module reiterates the details from the "Tips" document I mentioned earlier in this post - which does just go to indicate it's importance (I knew it!). Being aware of the compilation process and limits is half the battle to not having a problem with it.

This final challenge is a bit of a "spot the bug" one - which if you've ever paid attention to the Bristol Developer User Group that I run - you will know is something of a personal favourite hobby of mine - so I was delighted to recreate this problem and fix it up. Not a problem in the end, just pay attention to your training and you will have this module complete in a heartbeat.




Now I haven't technically earnt this badge yet, as I didn't complete all the challenges (I know, I know, shocked gasps!). But if you would like to find out more about this, or other trailhead modules - get in touch with me and join us on January 7th at 6pm GMT for the Bristol Salesforce Developer User group who will be having a Trailhead meetup that night. Attendance can be physical or digital - we can skype-project you if you want, and I will be earning ALL these new badges that night, for sure!


Whilst I am writing a blog post about Trailhead - whether you need the practise or not - I have to point out that there is now a module for building a Space Battle Station now.... no more boring warehouses for us!

Go earn all the badges!