There are some interesting things you can do with formula fields which admins and developers I have met often don’t realise or have forgotten some of them. I’ll draw attention to a few of my favourites here.
Here is our scenario. We want to pay our sales team different levels of commission based on their experience and time served with us. We store this percentage in a custom field on the user object. We want our sales agents to be able to see what commission they can expect from the deal on the opportunity record. However, we want our sales teams to be able to see each other’s opportunities but not the commission that will be paid. There are multiple ways we can do this, but I’ll look at how we can use some of the lesser known user formula features to achieve it.
Here we have an opportunity by our Star Seller who gets paid an impressive 5% commission on deals. When viewing the page as Star Seller we see this:
In the left hand column we can see that nice chunk of commission we can expect on this deal. Viewing the same deal as another member of the Sales team we see this:
Notice that the commission value is £0. This is the same record, no editing in between, just viewed as a different user. We now change the owner of the Opportunity to Under Performer and we can now see a new commission value. Under Performer hasn’t had regular career progression so is only on 1% commission. Now if Star Seller looks at the record, commission will show 0 again.
So how is this done? The commission field is a formula field containing this:
This does a few things, one of which I haven’t mentioned yet.
The interesting things happen in the OR on lines 2 – 6. We’re using a few things that I think are often underused in formulas here. Firstly, line 3 checks if the current user is the same as the opportunity owner. Secondly, on line 5 we have a custom label called roles that we compare to the current user’s role to see if the role name is contained in the label text. The label is set up as below with the names of the roles I want to always be able to see the true value as they are senior management.
Being inside an OR this means we are returning TRUE if the person viewing the record is its owner, or they have a role name contained in our label.
We could just list those role values out in the formula itself but we have a lot of them so this keeps the formula tidier and if we are running the same formula in multiple places this makes maintenance much easier.
The rest is a relatively simple IF which calculates the commission if the viewing user is one of the people we want to be able to see the commission, or just displays zero if not.
This is a very simplified example for demonstration purposes of just two of the formula features that are often under used. We are able to display different information based on the user looking at it, and able to bring in custom labels to compare two sets of information very simply. Each of these things can be done in other ways but depending on the detail of your use case so simple formulas might be just what you need!
Find out more about our offerings here.