Custom reports based on the current record in Salesforce

There are many things that you can show with related lists and roll-up summaries under a record but sometimes you would need an extra layer of depth or a more personalized report on activities or other records related to the one you are looking at currently. While power users have no problems creating a report they need it would still be useful to have a link or a button to create commonly used reports related to that record with a click of a button, right?

Unfortunately there is no standard functionality to do this in Salesforce but luckily there is a way around it. We need to create a report and a custom link to that report that includes the Account id, Campaign name or basically any other piece of information that we can use to filter the results of that report. It's not very complicated, you just need to master the ways of ?pv0=.

Creating the report

Let's say that your users would need to have a report of all the opportunities under an account. We might include product information or various summaries or maybe a pipeline of all open opportunities with full comments. Whatever the report is, you should make it so that it will show the desired information when you manually enter an Account ID (or name) as the first filter.

When we have the report that works like we want to just by changing the Account ID, we can save the report and copy the URL of the saved report and go forward with the custom link. Actually we just need the part after salesforce.com, but you'll see the formatting below.

Oh, and I'm using the Account ID here because it is the best way to single out a specific account and if you do it just by the account name there might be problems if you have duplicate data. With the ID you can be sure that the report is showing the right account and only the right account.

Creating the custom link

If you want to have a link in the Account page layout, you need to create the custom link in the Buttons and Links under the Accounts. And same goes naturally for campaigns, activities or custom objects.

After naming your link you can select whether you want it to be a button or a link. The instructions and examples are pretty clear on that part. You will also need to make sure that the Content Source is URL. Then we can move on to the more interesting stuff.

The trick here is to paste the URL of the destination report in to the field and add ?pv0= right after the address. This tells the report to fill the first filter with whatever is behind the wonderfully obfuscated series of characters. And since we want to have the Account ID there, we can choose it from the Insert Field -menu.

The end result should look like this:

/00OD0000003tr8s?pv0={!Account.Id}

That link will direct you to the report specified in the URL and insert the Account ID as the first filter. If you want to define the other filter rows you can also use ?pv1= for the second filter row, ?pv2= for the third and so on.

These can also be chained together so if you wanted to show the opportunities of other users in this account you would need to add a second filter row "Owner ID not equal to" in to the report and then make the link as follows:

/00OD0000003tr8s?pv0={!Account.Id}?pv1={!User.id}

Add to page layout

Maybe this goes without saying but you will also need to add the custom link/button to the page layout to make it visible for the users.

Other uses

You could also use this for creating reports of all the opportunities of accounts that have the current account as a parent {!Account.ParentId}. Or basically any other field that you can find in the Insert Field menu. It is definitely worth checking out because this can be a powerful tool if used in a clever way.

We use it amongst other things to show a campaign report that gives just the relevant fields when we want to export the names, email addresses and member ID:s to our external emailing service. There really is a million uses for this and if you have done something that you are proud of by using this method I'd love to hear about it in the comments.

How to launch a MDM solution

We are using Salesforce.com as our master data system and transfer the data in to our billing system (Sonet) and our proprietary service production system we call Finder (sorry Apple). Here's a small clip around the theme of the launch:



In case you hadn't guessed it by now, I had a lot of fun launching the service. :)

Spring is almost here

One of the best things about Salesforce.com is the regular updates to the system. It is pretty much an automated process and rarely requires any adjustments to the administrators end. The biggest thing to do is to download and read the release notes so that you won't be caught off-guard when the changes eventually come to your instance. If you are an admin then they have sent you an email titled "Spring'13 Release Notification" well in advance so you can have your training organization trained before the new release. And it really is important to have everyone up to speed about the coming changes because otherwise you can be sure that you will have a lot of calls to answer when things are a little bit different. :)

Reading the release notes

Don't let the 100+ pages that the release notes have scare you. Odds are you won't be using most of the things in there and they might not have that much to do with the features you are currently using. But it's good to read it through and evaluate which automatic changes affect your organization and what features could or should be activated and how they would benefit you. One good example is right on the second page of the Spring '13 release notes under Chatter. The tasks will now appear on Chatter feed automatically so if you are following an account, opportunity or lead in chatter you will be notified every time someone creates a new task under it. This is a really awesome improvement that helps keep everyone on the same page but more importantly there is a little gem hidden right below it. A small checkbox called "Allow Task Entry in Feeds" in Setup -> Customize -> Chatter -> Settings that will not be active without your input.

You can already try this option in your sandbox by activating it. It is still in beta as you can see in the release notes so you can't fill all the information that your organization might require from the Chatter feed. But if you are already using Chatter actively and need a quick way to add simple tasks then you might find this a really nice feature. The worst limitation at the moment is the fact that if you create the task in the Opportunity, you can't automatically attach it to a contact. So the task will be related to the opportunity but you won't see the contact details under that task. Of course you can fill these in later but even now it should be an improvement. Especially with leads where all the activities are related to just the individual lead and there is no need to specify the account/opportunity/contact to each task.

There are a lot of other improvements to Chatter as well like the ability to search within groups easily and being able to post to public groups without joining them. Detailed information is available on the release notes or the Chatter release training video.

Other noteworthy features

First of all I really feel bad for people who are still using email thinking it is a good way to communicate. But for many businesses it is still a valuable tool and a lot of customer communication is still done via email. There is hope of a better future and with our closest client we already have customer chatter groups that helps the communication a lot. I think the true jackpot of customer chatter groups is still yet to come - but apparently it on the roadmap if my talks with the Chatter developers from Salesforce are anything to go by. The breakthrough that we need is the ability to share chatter groups between different salesforce instances so that you can use your normal login credentials and see the shared customer groups in both organizations as normal shared chatter groups. Not so that the other one has to use special guest credentials. I talked with the Chatter development team in Dreamforce last year and apparently this is definitely on the roadmap but no schedule was given. But it is a very logical and awaited step that would really bring the whole Salesforce.com in to a whole new level. 

Sorry. Back to the features. It's easy for me to get carried away when talking about Chatter and the oppression that email has laid on most of us. That aside, if you are using Salesforce for Outlook in your organization and haven't seen the Salesforce Side panel then you should check it out. It enables you to view Salesforce data right inside Outlook and should make life a bit easier for the email-inclined. 

If you are using cases in you customer service then you might enjoy the possibility of creating custom reports of emails. That should give you a good, reportable insight to how the cases are getting solved via email.

Salesforce touch is improving but it is still lacking one key feature: creating and editing events. That would be one of the most useful features for our sales warriors on the go. We use events to write down the meeting notes from all customer meetings and loving the informative reports we can do with that but unfortunately the iPad revolution is slightly held back with the inability to manage events on the touch interface. Surely we can't be the only company baffled by this? What gives?

Making sure that the data is entered in the right form

As all admins know, sometimes users would like to input data in a different form than it actually should be. I'm not going to go in to all the problems incorrectly formatted data can cause but sometimes external systems require some key data in the right format and there just can't be any errors in the way it is typed in.

We have integrated Intrum Justitias credit decision and monitoring services straight in to Salesforce so that all the salespeople can just click a button to request a credit decision for a customer and get the decision in a matter of seconds. It is a part of our sales process and a required step before making an offer. Actually we get a lot more than just the credit decision. They provide us with address information and the official name of the company. It is great for our data accuracy because we can also update that information to the account with a single click. So by doing things by the process it actually saves salespeople time! How wonderful is that?

The service works great, but it is solely based on the business code that we use in Finland (locally known as "Y-tunnus"). All the registered organizations and corporations have it and it has a standard form: 1234567-8. And in order for the service works we need to make sure that we input the business code properly.

So all the business codes in Finland are nine characters long and they are formed of 7 digits between 0-9 in the beginning, a dash in the middle and 1 digit between 0-9 in the end. It's important to take the business code apart like this because we will need write a validation rule and express the form of our business code in a REGEX function.

Creating the validation rule

So we have a field called "Y-tunnus" and we want to make sure that the data entered in to this field has 7 numbers, a dash and one number after the dash. First we need to go to the Account object and create a new validation rule. The validation rule should give us an error message if the field "Y-tunnus" is not following the aforementioned form factor. So

NOT (REGEX( Y_tunnus__c , "[0-9]{7}-[0-9]{1}"))

The way the function works is widely documented on Salesforce's support site but basically in this case we first define that we need numbers between 0-9 with the [0-9] and that we need 7 of them {7}. After that we just use the normal dash and define that it should be followed with a number between [0-9] and that there should only be one of those {1}. And because the validation rules always work a b it backwards in logic, we need to add the NOT () funcion. So when the "Y-tunnus" is NOT following the defined form it gives you an error message.

If you only want this validation rule to be active when there is some information in the field you can add

&& NOT(ISBLANK(Y_tunnus__c))

to the validation rule. That way it will only give you an error if there is something in the field but it doesn't match the criteria.

Since we also have business in Sweden and we need to keep track of their företagsnummer I'll chuck in a prepared formula for their 123456-7890 style business codes as well for good measure. :)

NOT (REGEX( Foretagsnummer__c , "[0-9]{6}-[0-9]{4}"))

I hope this will help you keep your business numbers in check.

Setting a limit for number of campaign members

Limiting the number of campaign members in Salesforce might come in handy in many situations. We are habitually organizing seminars and other customer events where we can only have a limited number of attendees. Normally there is no way of setting a maximum number of people that can be invited to a campaign so I was given a task to figure out a way of doing it.

We specifically wanted to track the number of people with the status "Attending" so that Salesforce would warn us when we are trying to add more people than there is actually room for. We also arrange some events where people are allowed to bring a colleague or any other avec so in this post we will consider that as well. But to get things working we needed a couple of things:

"Maximum number of attendees" field

This one goes straight in to the Campaign object. It's just a normal number field and we can input the number of available spots in there. If it is left blank then there are no limitations on the campaign.

Counting the attendees

We are using standardized campaign member values and it really helps in creating numeric values from the member statuses by using the CASE function. It is important to have standardized member statuses if you are doing it like this because the case function checks the exact value of the Status field and gives different values depending on the status.

In our seminars and some special events we have campaign member statuses "Attending" and "Attending + avec" and we need to translate those in to numbers so that the standard roll-up function can summarize them. So for this we need to go to the Campaign member object and create a number field with the following formula:  

CASE( Status , "Attending", 1, "Attending + avec", 2, 0)

This formula first checks if the Status is "Attending" and gives the value 1 for those. Then it checks if the value is "Attending + avec" and gives the value 2 for those. Else it's just 0.

If you don't have the need to reserve two seats for one invitee you can just use CASE( Status , "Attending", 1, 0) as the formula. That will most likely be enough for most organizations.
This field doesn't need to be visible because mainly we will be using it to do the roll-up summary.

Creating the roll-up summary

Now that we have a field in the campaign member object that shows a number if the person is attending (or two if they are attending with an avec) we can move on. So we go go back to the Campaign object and create a Roll-up summary field that sums the values of our newly created field.

I named this field Attendees so in the validation rule it will show as Attendees__c. This field is nice to have on the page layout because it gives us the relevant information on how many people have signed up with a simple glance.

Creating the validation rule for maximum attendees.

The help text that Salesforce tells you on top of the validation rule is not very clear. Technically "If this formula expression it true, display the text defined in the Error Message area" is correct but I find it a bit confusing at times. Instead I tend to start the thought process with the words "It should give you an error message if..." and the right form of validation rule comes quite naturally.

So in this case it should give you an error message if the number of attendees is higher than the maximum number of attendees.

Attendees__c > Max_number_of_attendees__c 

And we are done! Now the system will inform us if we are trying to overbook the event. You might want to set the maximum number of attendees to be slightly lower than it really is at first so you get notified before you hit the limit. Or if you are expecting no-shows it can be slightly larger. I guess common sense and experience will be good tools to evaluate the number here.

The solution that I ended up with might not be the most elegant but it can be done without any code and it has served us well. If you have a simpler way of achieving this I'd love to hear about it!

How to make the marketing department like you vol. 1

The standard functionality of Sales Clouds Campaigns isn't necessarily everything your marketing department wants it to be. Still it is something that many companies continue to use to coordinate their email mailing lists and events they organise. I've customized our campaign object in several ways to make the user experience better and add some functionality to it and I'll be going through them in the following few posts.

One of the first things that came up was the crazy inability to define standard campaign member status values depending on the type of the campaign. Instead you need to go every time to the campaign and click on the Advanced Setup button to do so. That's not very intuitive and definitely not user friendly. And the values the marketing people add might not be standardized and therefore harder to report on. Actually it seems like such an obvious feature that it should be a standard.

Now as any developer or consultant will tell you it is possible to go on and write some apex to change that but luckily in the world of Salesforce and AppExchange there is also an easier - and free - way to do it.

The solution is called AAkonsult Campaign Status. It is a very straightforward app that solves the whole issue of having to manually enter campaign statuses every single time. It is a great help even if you do just one type of campaigns but it really shines with multiple campaign types that require different member statuses. The setup is also a breeze and doesn't require any code. I recommend it for everyone who uses the Campaigns object. The app is available for Professional, Enterprise, Unlimited and Developer editions.

Few examples from our organisation:
email campaign has values will be sent, sent and email read.
Customer event has values will be invited, invited, attending, attending + avec and not attending.
VIP Ice Hockey has values table of 4, table of 6 and Extra seat (additional cost)

We are also setting limits on how many people can attend a single event and Salesforce will instantly tell us if we are trying to overbook the event. I'll explain that in more detail in a future post.

Admin's creed

This is my instance. There are many like them but this one is mine.

My instance is my best friend. It is my life. I must admin it as I must admin my life.

Without me, my instance is useless. Without my instance, I am useless.

I must maintain my data true. I must scrub data better than my end users, who are trying to contaminate it. I must activate the workflow rules before they input the data. I will.

My instance and myself know that what counts in this war is not the reports we make, the dashboards of our C*Os or the triggers we write. We know that it is the data quality that counts.

My instance is human, because it is my life. Thus, I will learn it as a brother. I will learn its record types, its objects, its templates, its workflows, its custom fields, its layouts. I will keep my data clean and ready, even as I am a nerd and sweaty. We will become part of each other.

Before Salesforce I swear this creed: my instance and myself are defenders of my company, we are the masters of our CRM, we are the saviors of our customer experience. So be it, until there is no incomplete data, but useful reports.