It was about a year ago that Mark Slayton wrote about Salesforce Global variables, it’s a great post and if you haven’t read it already I want you to add it to your reading list. Last week I was doing some work on the org that Jared and I use to manage the podcast- yes we use a Salesforce org, so maybe I’ll have to write about that in the future. Anyway, I found myself trying to think of the last time I created a custom field on the user record that I then used to make data more relevant for my users. Go ahead, try to think of the last time you added a field to the user record that was then referenced outside of a user report. Don’t worry, I’ll wait. I’ve got all day.
So if you were like me I couldn’t think of one. Then it dawned on me that Admins could be creating all these silly look up relationships- and don’t talk to me about maintaining them- just for the sake of making reporting easier. How about I start with a real-life example?
Tell me if this sounds familiar?
Let’s say your Accounts are divided into the following categories- Small, Medium, Large. And when your Account set up team creates an Account in Salesforce they choose from a picklist those values in a custom field called “Category”. Now based on each Account category chosen- Small, Medium, Large- that account needs to be assigned to one of three managers. So at this point most Admins would create a lookup relationship that needs to be populated. That way Bob who is in charge of small accounts can see his Accounts, and so on. Good Admins at this point will create a workflow so that the Account setup team doesn’t have to populate that lookup field. And at this point you would be pretty happy with yourself.
But Bob has to create his own list views and what happens when Bob leaves or starts managing different size accounts? (Hint: the Admin has to update a workflow and a bunch of records.) So really that lookup relationship is only important to Bob- kind of seems like a lot of work to make Bob happy?
There is an easier way.
Obviously the picklist on the Account is important- I’m sure many departments would use that for reports. And maybe a few people care that Bob manages small accounts- but I bet the majority don’t find it relevant to their jobs. And Bob really only cares about seeing his Accounts, so as an Admin you don’t want to keep maintaining the relationship- you have better things to do and build!
So let’s create a field on the User Record to manage this for us. We will create a picklist field (because that way we know the text will be the same) and call it “Category Manager” and put the same values in it as we did on the Account picklist field. Now when you set Bob up as a User you can populate the field and let Salesforce do the work for you.
After that, let’s create a formula field on the Account to manage this ‘relationship’. Similar to the field that Mark created in his post we will use a formula field that renders text. Then using the following formula we will either render “TRUE” if the two fields match or “FALSE” if they don’t. Don’t worry, I’ll put the instructions for all this below.
Now we can create one list view called “My Accounts” and it works for Bob who manages small accounts, Jim who manages medium accounts, and Bill who manages large accounts. Why? Because the formula field will only render to True when the user and the record match. Make sense?
Let’s build it!
Ok so here is what we need:
- A picklist field on the User Object called “Category Manager” with the following values- Small, Medium, Large
- A picklist field on the Account Object called “Category” with the following values- Small, Medium, Large
- A formula field on the Account Object called “IsThisMyCategory” with the following formula for each value:
IF(ISPICKVAL( $User.Category_Manager__c , “Small”)
ISPICKVAL( Category__c ,”Small”), “TRUE”, “FALSE”)
- The formula field can be hidden from the page layout to give that magic effect to users.
- Create 1 list view Called “My Categories” or something like that where “IsThisMyCategory” equals true, and make it available to all users. This list view will magically display the correct accounts for each user.
The Good, and the bad of this solution
The Good. Like the formula in Mark’s post it works automatically for the user you need it to, and in my opinion it’s really low maintenance. No workflow to deal with, and if a user gets reassigned you don’t need to mass update a lookup field. You can create one list view for the user or the user can reference the formula field in a report.
The Bad. You don’t have that lookup field on the record, so if you wanted a report of Accounts by Category with category owners you are out of luck. Did I mention it only works for the user? I know that you have the categories on the Account so you can always report from that, but the magic ends for the other users- but hey, maybe they don’t care anyway.
How can you use this?
Ok, my plan was to plant an idea seed. So with all the information in this post- how can you use an idea like this to make your job easier? What would you change? What am I forgetting?