Thursday, November 19, 2015

I've found myself stuck a few times when I've created a validation rule in Salesforce but needed to update records that would trigger my rule criteria.

Example: I don't want to allow users to update a specific field after it's been populated upon record creation. But, occasionally as the system admin, I may need to update that field when a user makes a mistake.

In the past, I'd edit the validation rule that was preventing me from saving a change and deactivate it. But when you have multiple rules that you're 'breaking', this becomes rather cumbersome.

So, I've started adding an 'admin out' to my rules. There are several ways to do this (by profile id, user id, etc), but here's what's working for me:

I add in a criteria to not allow the change for anyone except a system admin, as defined by the profile name.

This has been working well for me.

Word of warning: I don't recommend doing this for all rules, especially those that prevent changes that could cause errors in reporting or data consistency. Be selective if you choose to allow yourself an 'admin out.'

Do you have an alternative method that works for you? Let me know in the post comments or share it with me on twitter - @rathergeeky.

Wednesday, November 18, 2015

I received a report request today that sounded something like this: I need to get a total of all won opportunities for any account in this account hierarchy, regardless of whether we worked directly for them or were subcontracted*.

Sounds simple, right?

In theory, yes. But, if you happen to have a complicated account hierarchy (eg: you have any federal government accounts), this is not an easy task. In fact, it's not natively possible to run a report like this in Salesforce.

In my case, I was looking at 80+ accounts organized in a hierarchy that was five levels deep.

Rather than tell the requestor that the report was impossible, I decided to get a little creative.

Step 1: Get a list of all the accounts to include

I clicked on the [View Hierarchy] link next to the account name. Because I had multiple parent/child relationships, I scrolled until I found the account name that I was looking for within the hierarchy. Then I highlighted all the accounts underneath that, copied and then pasted them into Excel.

This is what my spreadsheet looked like:

At this point, I needed to extract the record IDs from the account name url. I remembered blogging about this before and read through the old post to remind myself how to extract the record ID from a hyperlink.

Step 2: Generate a list of record IDs to use as a report filter

Step 3: Create a report and add the filter criteria

I created the report in Salesforce and pasted the concatenated IDs in my filter. Since I wanted to filter by two account fields on the opportunity, as well as by the parent relationship, I had to add those three fields to my filter. (Note: Several of these fields are custom formula fields based on the lookup to the Account object.)

Because the filter criteria can include only so much text, I split the record ID list into two filters for each field that I wanted to apply the filter to.

Here's how it looked:

Final Thoughts

I'd much prefer an approach that is more dynamic, rather than hard-coding the record IDs to generate a report. But for now, it does what I need it to do.

Have you ever received a request for a Salesforce report that stumped you? Or do you have an idea to improve my workflow? I'd love to hear your suggestions and stories in the comments below.

*We have a Secondary Account custom lookup field so that we can track not only the direct client the opportunity is for, but also the account that may have hired them (when we are operating as a subcontractor).

Hi, I'm Jenna.

I can't seem to squelch a desire to find better ways to do All The Things, utilize technology to boost personal productivity, and leverage digital tools to organize my everyday life. If you find yourself similarly inclined, welcome!

Disclaimer

The content I share on this blog reflects my own opinions and experiences, not that of my employer or any other company. Please note that software or techniques mentioned in past posts may no longer be applicable. I try to post updates when possible, but if you have any questions please reach out via the comment area in the sidebar.