Strategic impact and making multi-level relationships look nice
Last Updated on Wednesday, 5 May 2010 02:08 Written by Steve Wednesday, 5 May 2010 02:03
I’ve been doing a little prototyping in Salesforce.com as a volunteer project for a nonprofit in town. They’ve got an interesting need to track their strategic plans in Salesforce. I’ve seen this kind of thing built many times and have built it myself for another org or two. For this org they’ve got 3 levels of custom objects to hold their data. Driver is their high-level strategic focus. Goal is the next level down–there can be many goals for each Driver. And Metrics our measurable things related to each Goal.
Salesforce lets you create these objects and relationships between them very easily. I created the schema in just a few minutes. I created three objects and related them together–in this case I made them master-detail relationships. You can see in the diagram what my schema ended up looking like.
Now tracking your strategic goals is great, and most nonprofits do that. But where I think it gets more interesting is when we can tie the actual work we do directly to the metrics we’re trying to deliver on. So you’ll see in the diagram that there is a 4th object called project. Project is used to track all the work efforts of the org that are worth recording. I created a lookup relationship from Project to Metric so every project this groups takes on can be explicitly related to a Metric we need to deliver on. Which is in turn related to a Goal, which is related to a Driver. So, why are you updating that website for the 100th time this year? Because the website Project is meant to deliver on the Metric of increasing engagement of our supporters 25%, which is part of a Goal to grow the direct impact our supporters have in the world, which in turn feeds our Driver which calls for us to lead the world with a model of catalytic impact.
Those strategic objectives are made up for this example, but you get the idea how daily work can roll up to why your organization exists. Seeing that direct relationship can be very powerful. So when you look at the Project record in Salesforce, what do you see? Because there is only a relationship to the Metric object, you only see the Metric on the page layout.
Because the Project isn’t directly related to Goal or Driver, we can’t have lookup fields to those objects. Lookups are only for direct relationships, from child to parent, not up to grandparent and beyond. So we need another solution, and it turns out to be formula fields. On the Project I can create a Goal field and a Driver field that are formula fields of type Text. Here’s the formula for each of them:
for Goal: HYPERLINK("/" + Metric__r.Goal__r.Id , Metric__r.Goal__r.Name,"_self" )
for Driver: HYPERLINK( "/" + Metric__r.Goal__r.Driver__r.Id , Metric__r.Goal__r.Driver__r.Name,"_self" )
We’re creating a hyperlink to fake a real lookup. The URL of the hyperlink is relative, so it starts with / rather than http://. It then has the Id of the object, which we get by traversing up the relationships in Salesforce. From project we follow the Metric__r relationship to the Metric object, then up the Goal__r relationship to the Goal where we can grab any fields we want. With Driver we do the same thing, just go up one more level. If you think this looks hard, don’t worry, the formula field editor is really easy to use and does all the hard work for you–you just click the relationship you want to traverse.
The text of the link is the Name of the object in question. And then we target the hyperlink to “_self” so that it will load the record in question into the current window–just like a regular lookup would.
After adding these formula fields to the page layout, the end result is this:
Now we’re telling the full story about this project with a little help from simple formula fields.
This is a totally hot tip! Cross-object formula fields to fake lookups to distant objects. Very nice!
Yes – so much easier than trying to update actual relationship fields.
Another tricky part with tracking metrics is to connect the “25% increase” to real campaign or evaluation data, and to evaluate whether goals are met based on metrics being reached. Rich Saunders will be showing something like this at the User’s Group tomorrow if you can make it – a flexible metrics/indicators/outcomes evaluation “engine” based on Survey Shark.
I can’t wait to see it!
Steve, did you submit this as a Paper for dreamforce yet
Garry
Instead of using “_self”, use “_top”. It works the same way except that if you ever use these fields in a related list, they will not load properly and instead just pop into the hover.
I wouldn’t submit this as a paper for dreamforce. salesforce generally scoffs at url techniques like this as they are undocumented and unsupported. They won’t work on mobile (for example).
That all being said, I use this technique all the time. It works great.
Thanks for the ‘_top’ tip!
If we ever change the url for a detail page from /record id, well I think all heck would break loose…so, I give this suggestion a “Like”. It is when people start replying on specific query parameter values to drive behavior that I start to worry about their solution breaking sometime down the road. Thanks, Steve, for sharing this!
– Mary Scotton (salesforce.com)
Ah the blessing of the product group! Can’t ask for more than that!