Round Robin Lead Distribution

Here is an interesting problem: how do you import a stack of leads and evenly distribute them between several reps? There is no inherent function within salesforce to accommodate this feat. But there is a way to do it. Here is one good way.

Essentially, you put an auto number on the lead record that assigns a new number to each new lead as they are created (or imported). To evenly distribute the leads between several reps, you simply divide that lead number by the number of reps and use the remainder +1 to assign each lead to one of the reps.

I'll use three reps in my example. If the lead number (the one we automatically generated) is divided by three and you just examine the remainder, you'll see this progression:

Lead Number Function Result Remainder Remainder+1 (Assignment)
1.......................... 1/3........... 0......... 1.................... 2
2.......................... 2/3........... 0......... 2................... 3
3.......................... 3/3........... 0......... 0................... 1
4.......................... 4/3........... 1.......... 1................... 2
5.......................... 5/3........... 1.......... 2.................. 3
6.......................... 6/3........... 2......... 0................... 1

Then just use assignment rules to assign the lead based on the result: just assign all the "1s" to the first rep, all "2s" to the second rep, and all the "3s" to the third rep.

The way I did this was to create two new fields. The auto generated number field called "Lead Number" and a formula field called "Assignment."

The formula I used for the Assignment field was this:

MOD( Value(Lead_Number__c) , 3 ) + 1

MOD gives you the remainder of a division, so you can see that I divided Lead Number by 3. The "Value" function takes Lead Number, which is actually a text field, and turns it into a real number. Then we add 1 so that a 0 is not returned.

Then create your lead assignment rule called "Round Robin" with 3 rule entries. The first entry will assign all leads with an Assignment field = 1 to the first rep, and so on for the second two rule entries.

If you have more reps, just divide by that bigger number and create the right number of rule entries.

Many-To-Many Relationships

A client called me with a problem today. He has a custom object called "Projects" in which he keeps track of the construction projects his company is working on. He also keeps his contractors in the system as Accounts and Contacts.

My client wanted to be able to create a link on the Account record to the Project Record to show which Projects a given contractor is working on. The problem he ran into appears when a contractor works on more than one project. Many contractors can link to the project record, but one contractor can't link to many project records.

The problem is based on the one way link. Since the contractor (an account record) has only one field pointing to the project record, only one project can be assigned per contractor. We have what is called a "One-to-Many" relationship and we need a "Many-to-Many" relationship.

While the functionality is not "built-in" to, there is a way to solve this problem. The solution is to create another object that serves as a link between the contractor and the project. We called this object "Project Contractor". Each "Project Contractor" record will have two fields, a lookup relationship field associated with the Project Object and another lookup relationship field associated with Accounts. Each record will, in essence, be a relationship between a particular project and a contractor. We name each record according to the project and the name of the contractor separated by a hyphen, for example, "Downtown Building-Jones Construction".

Using this third object allows there to be a related list on both the Account Detail Page and the Project Detail page called "Project Contractor". Each related list can show multiple records. Therefore, a given contractor record (an Account record) can show on its related list all of the Projects that the contractor is working on. Then on a given Project record, there will be a related list that shows all of the contractors that are working on that project.

You can move between the two records (contractor and project) via the Project Contractor record contained in the Project Contractor Object that will be found on the related lists of the Account and Project detail pages.

In this way we have a many-to-many relationship link between the Account record and the Project record. Very, very cool.

Button for Reverse Number Lookup

Here is a pretty simple tool that I have found really helpful for some of the work that I do. I get leads from all over the country on my website. My web form, which feeds to web-to-lead in salesforce has a "best time to call" field that I added. I don't want to make the visitors to my web site fill out too much info because it tends to scare them away, so I only put a few neccesary fields - like the phone number, but I don't ask for time zone or city. However, if I'm going to call when they asked me to, I need to know where they are.

The easiest way for me to determine that is by doing a reverse number lookup on their phone number. I use the reverse number lookup on the website to find out the city and state of the lead. Then I know what time zone they are in.

That website allows you to feed the number you want to lookup in the URL. As a result, you can make a link or a button on your lead record, account record, or contact record that will automatically take the phone number stored in that record, send it to the whitepages website, and open up a page with the search results - big time saver.

Watch this video to learn how to set it up. The link you will need is included below.{!Contact.Phone

Desktop Login Shortcut

One little time saver that a client asked me about a few days ago was, "How do I put a shortcut on my desktop that automatically logs me into Salesforce?"

Its actually pretty easy, but you'll have to make it yourself.

Okay, here is how you do it.

1) Go to the login page of salesforce and right click on the desktop. From the menu that pops up, click on create shortcut. That will put a shortcut on your desktop.

2) Next, edit the shortcut by right clicking on the shortcut and selecting properties. You should see the following link in the URL field:

3) You need to add the login validation text to the end of that link. It looks like this:


The username is your salesforce login username email address. Note however, that you cannot put an @ symbol in the URL so it is replaced with a %40. So it will look something like this instead:


and obviously enter your actual password in place of "password". So the finished link will look something like:

4) Click on OK to save the properties box. Your desktop shortcut should now be active and will log you in automatically to and bring you to your home page.

Another useful trick is to add a keyboard hotkey to the shortcut. You'll see the "Shortcut Key" field on the properties editor (same one you had open before). Type in S in the shortcut key. As soon as you type it, a "CTRL + ALT + S" will appear in the place of the S that you typed. Click on OK to save the properties box again.

Now, when you want to login to, just hold down CTRL + ALT and type S and automatically a browser will open and login to your account.

Finding Information in Salesforce

One of the issues I see new users to struggling with frequently is knowing where to go and what feature to use to find needed information. If you need to find a record, what is the quickest way? If you want all the contacts in a certain area, how do you do that? If you want to see a list of top performing sales reps, what function do you use? has made it very simple to find the answer to any of these questions, but with any capable system, finding your way through all that capability and learning to use those tools can be challenging. Let me see if I can make it any easier. generally gives you 4 ways to find and display information:

1) The Search Function
2) The View Function
3) Reports
4) Dashboards

Let me take these one at a time:


The Salesforce search function has two levels of complexity: basic search and advanced search. Search is a great feature - and quite powerful. The search form in on the sidebar every page of salesforce except setup pages. It is typically the fastest way to find one record. If you need to find someone at a certain company, just type some portion of the company name into the search box and click search.

Advanced Search searches more fields than the sidebar Search, including notes, descriptions, and comments. It has the ability to use operators such as AND, OR, and AND NOT to help you find exactly what you are looking for. You can put quotation marks around multiple keywords to perform an exact phrase match, for example, "call john smith". Plus, it allows wildcards - add an asterisk to the end of your keyword to search for items that start with the word, for example, "acme*".

You should note: Search does not display results from related lists. For example, if you are searching for a contact at a company, the search function will only find and display the Account Name, even though there may be several contacts at that company. Once you see the Account you are looking for, open that account record to display the contacts associated with it on the related lists.

I find many users of salesforce only using one way to find information, when another way would make much more sense. The most common mistake I see is overusing search. For example, one user I worked with needed to see all his contacts in a given zip code to plan a series of on-site visits in his territory. He was trying to find these contacts using search. A much better way to find this information is the VIEW feature of


Finding information is always about choosing a database, filtering that data, and choosing how to display it. As a matter of fact, functionally, this is all a database is good for. Storing information is only useful if you can later find what you saved. The View is just a more capable way of filtering and displaying information than the search.

The primary limitation of the View is that it limits the filtering to the object type in which it is located. An salesforce object is a Lead, Contact, Account, Opportunity, etc. You might also have custom objects such as Projects or Invoices that help you manage your particular business processes. Since the View is limited to one object type, it cannot be used when looking for information spanning multiple types, such as all the contacts associated with a given account. To combine multiple object types, a Report must be used.

At the top of each object main page, you will find the View drop down menu. Salesforce has already created many views that it thinks most people will commonly need such as All Contacts on the contacts page or My Opportunities on the opportunities page. But to really get the benefit of the View function, you'll have to learn to create new Views. To create a new view simply click on the "Create New View" link located right next to the View drop down menu.

The Create New View page has three basic sections: 1) View Name, 2) Search Criteria, and 3) Select Columns. Each of these are fairly self explanatory, although the search criteria requires you to use basic logic functions such as "equals, does not equal, contains, does not contain", etc. Some people initially have some challenge with this if they prefer the visual functions of other CRM packages such as ACT. It is entirely logical though and allows for more precision when searching for certain records.


The Report function overcomes the limitations of the View function and ads several additional capabilities. An important capability of the report function is the ability to combine information from multiple record types. This capability is addressed when a new report is created by asking the user to select the type of data to report on and offers the user the option to select data from related lists at the same time. Other important features of the Report function are its export capability, charting functions, and drill down functionality.


The Dashboard is named such with the idea of a car's dashboard in mind. The dashboard tells the driver all he needs to know about the performance of his car. In the same way, the salesforce dashboard tells the company leadership in a glance about the performance of the company. It can also tell any user of salesforce about the performance of whatever aspects of the company they are responsible for. For example, sales reps can see dashboard elements about their sales, support reps can see dashboard elements about support, etc. Many different dashboard pages can be created and they are completely customizable.

The Dashboard takes the data of the report and puts it in a chart. Reports have their own charting function, but the dashboard allows you to take the charts of many reports and put them on the same page. Each user can create any kind of dashboard that they like. Elements can be tables, charts, graphs, pie charts, etc - there is an unlimited variety of ways to display performance information.

So there you go - when trying to find information, remember that there is more than just the search, also use Views, Reports, and Dashboards to get the most out of your implementation.

Creating Mailing Labels

Here is probably one of the more common and mundane tasks that a user of has to do every so often - create and print mailing labels of customer addresses. Both marketing users and sales reps will occasionally need to take a list of contacts and print mailing labels from them. The task is fairly straightforward, but it contains quite a few steps and can be confusing. To make it a little easier, I've laid out the basic steps below and made an example video so that you can watch me do it.

The Basic Steps of Printing Mailing Labels

1) Create a report in that contains all the contacts or accounts that you want to mail to.

- There are address fields in both contacts and accounts as well as shipping and billing addresses in both areas. I mention that so that you'll be sure to get the correct address into your report.

- When you make your report, be sure to include the first and last name, and all the address fields you need such as street, city, state, zip, etc.

- Set the criteria to filter only the contacts you need. You might want only a certain area code or all the contacts that are receiving the newsletter.

2) Export that Report to MS Excel using the export function given on the report.

- Save that report in the excel file format (it might give you the option of saving as a web page).

- Save that report to your hard drive. Word will need to load it from the hard drive.

3) Prepare Labels in MS Word using Mail Merge function.

- You'll be using both Excel and Word to complete this process. Excel contains the list of addresses, Word formats the labels and prints them. So we'll use the Mail Merge function in Word to prepare and print the labels.

- The Mail Merge wizard is located by clicking on Tools/Letters and Mailings/Mail Merge. It does a very good job of taking you through all the steps. Basically, in Word you have to choose a label type, load the recipients list that is in the excel file, format the label, merge the list, then print it.

There are a lot of individual steps to this whole process, but once you grasp what is going on, you won't have any difficulty. Watch the associated video for all the details. If you have any questions and need assistance with this process, you can reach me by clicking here.

Pulling Clients from the Black Hole

Do you ever have clients that you lose in a black hole? You know, the "what ever happened to the xyz account?" question that comes up every now and then. has done an amazing job of helping companies keep track of existing clients, but there is one report type that I haven't seen inside salesforce that can be solved using excel instead.

I call this kind of report the "without" report. Salesforce has built-in functionality to show you items that are linked; for example, it can show you all the contacts that have upcoming activities. It can show you all the Accounts with open Opportunities. But what if you want to see all the Contacts "without" an open Activity or all the Accounts "without" any Opportunities associated with them - you can't - because you are asking salesforce to show you what doesn't exist rather than what does exist.

A client asked me for just such a report this week. He was worried about losing prospects in a "black hole"; meaning, he wanted to know which Contacts had no open Opportunities or open Activities. He knew he had dozens of Contacts that had no upcoming activities - these clients wouldn't be hearing from him because he had never set a task for some kind of followup in the months to come. These clients were in a black hole and he wanted to pull them out.

Here is how we found those contacts. We created two reports. One called "All Contacts" and the other called "All Open Activities." We exported both reports to MS Excel using "Printable View." We then copied the second report into a second worksheet on the first report and renamed the tabs. So to sum up what we've got so far, we had one MS Excel workbook containing two sheets, the first worksheet is called "All Contacts" and the second worksheet is called "All Open Activities." There isn't much on these worksheets. The first one has one column called Contacts. The second worksheet has two columns: Contacts and Activities.

Essentially, the way we solve this problem now is to copy onto the first worksheet the activity from the second worksheet associated with each contact. So for each contact on the first worksheet, a lookup will be made onto the second worksheet. If that contact exists on the second worksheet, we'll copy the activity over to the first worksheet. Then we'll see all the contacts that have an open activity and all the ones that don't - which is the answer we're looking for. Now by simply sorting the list, you'll have a block of contacts with no upcoming open activity.

The excel function we used to lookup the activity on the second worksheet is called VLOOKUP. This formula will be located in the column next to the contact name on the first worksheet. It has 4 operators. They are:

Lookup_Value: This is the field containing the value that you want to search the second sheet for. So we'll put the cell containing the contact name in there. It will be the cell just to the left of where you're putting the VLOOKUP formula.

Table_Array: This is the table containing all the contacts and their open activities. For us, it is the contacts and activities columns on the second worksheet. Highlight that entire table, both columns. Be sure to fix the value with $ signs so it does not slide as you copy VLOOKUP into the rest of the column. It will look something like this: "All_Open_Activities!$A$1:$B$35"

Col_Index_Num: This is simply the column that contains the answer (the activity) that we're copying from the second sheet into the first. For our purposes, it is column 2 - so just put a 2 there.

Range_Lookup: Just put FALSE. This has to do with how closely excel tries to match the contact names. In our case we want an exact match. If it doesn't find an exact match it will put "NA" for the answer.

The formula will look like this: =VLOOKUP(A2, Sheet1!$A$1:$B$35, 2, FALSE)

Now just copy the formula all the way down the second column and sort the list based on the activities in the second column which will put all the NAs together. The NAs are the contacts that do not have any upcoming activities scheduled.

This same methodology can be used for any "without" report. It can show you the records of any object that do not show up on the related list of another object. Accounts without Opportunities, Leads without Activities, Accounts without Contacts, etc.

Sales System or Business Management System?

When most businesses think about transitioning to, it is normally with the intent of streamlining the activity of the sales reps. Management starts to realize how many sales have been lost because a rep just forgot to call someone back or forgot a detail that cost the company business. Salesforce is for most companies a way to clean up the business of sales. In their mind it is a system for the sales team. Other companies have made the leap to salesforce because they need more visibility into the sales process and the pipeline. In both cases, management is thinking about the sales process, either cleaning it up or getting more information from it.

Once a business gets started with however, they start seeing the potential to manage aspects of the business that are not sales related. Before long they say, "Hey, what is this cases stuff? Could our support reps be using that?" The next thing you know, you've got the technical support team using Salesforce built this functionality into the system already with Cases/Solutions.

Campaigns are another early extension to the sales related functions. Marketing is often closely associated with sales and tracking the effectiveness of marketing campaigns is a natural progression from tracking the progress of a sale.

You'll see another built-in part of that many companies start using soon is "Contracts" - don't confuse that with "Contacts!" Once a sale is complete, a contract is typically signed. Why not track the contract right in salesforce?

I think the next logical function that many companies move to next is some kind of tracking of the production/fulfillment of the product. When a sale is complete, somebody in the company gets working on delivering to the customer whatever product or service just got sold. How nice would it be to manage this process in salesforce as well? After all, all the information about the customer and their needs is already in the system. An opportunity fully details exactly what they need, why not just ad a section to salesforce that contains more data about the details of the fulfillment of the deal. In this way many companies begin their foray into custom objects.

Maybe HR and Finance want to get into the act. I've seen an "Employees" tab on one client's system. HR used that custom object to keep employee records up to date. For finance, there are plenty of ways to integrate. In my last post I wrote about a custom object I helped a client create called "Invoices". Others use products from the AppExchange to fully integrate with quickbooks.

The possibilities are truly limitless. The challenge is simply " Vision". If you can "see" it, there is probably a way to do it. I guess that is where I come in for my clients - I have seen so many neat ideas as I work for multiple clients, that I'm able to recommend extensions to salesforce functionality that my clients may not have thought of. It is a cool position to be in.

I think is reaching the point (I haven't heard them say this, I just suspect it) where they might wish they had called the product something else. The name kind of has them pigeon-holed as a "sales system" when it is really so much more than that.

Custom Objects: Customer Lifetime Value

Today I helped a client create a mechanism for determining the lifetime value of a given client. His problem is not unique. Essentially, this client does not know how valuable a given customer will be when the sale is won. In their business, they have an initial setup fee and a monthly service fee, but the length of time that the customer will continue to use their services is unknown, sometimes it's for 3 months, sometimes much longer. So without knowing how much a client will earn for the company, the "Amount" field of the opportunity is just a guess.

We discussed possibilities. One idea was to update the opportunity on a monthly basis to show the accurate total value. This is problematic since one Account might have multiple opportunities as the company upsells other services - which will confuse the total value. In addition, returning to the same opportunity month after month and updating the value is labor intensive.

We also tossed around the idea of creating a total value field right on the account page that could be updated. This solved the multiple opportunities issue but not the labor issue and it isn't very clean in the sense that you have no feel for what services makes up that total value.

We ended up opting for a custom object called Invoices. The custom object would contain the amount of each invoice and other fields directly from the invoice such as invoice number, description, type of service, etc. One of the fields is a lookup field to the associated Account. The Invoice object was added as an account related list and as a tab next to the opportunities tab.

The custom object really harnesses the power of An object is the term that uses to classify all records used for a given purpose. For example, "Contacts" is an object, "Accounts" is an object, "Opportunities" is an object. They will usually have their own tab, fields, and page layouts. Custom object records can link to other objects - (just like a contact links to an account) and once linked can show up on the related list of the other object. Once a custom object is deployed, users can start creating new records of that object type.

So for my client with the new Invoices object - now, on each Account record, he has a list of all invoices for that client. The records can be imported en-mass using any of several tools or entered each month by someone in finance as part of their normal invoicing process.

This now makes possible any number of reports that show total value of any customer, thus allowing Return-On-Investment calculations, etc to be made.This was a clean solution, easy to implement, and added a great deal of visibility value to this client.