MK Partners Archive for September, 2007

Frequently, many users experience frustration when attempting to sort data to their exact specifications. These frustrations often occur because the data entered into Salesforce differs from the type of sort desired by the user. An example of this situation could occur if you wanted to track your DVD collection and sort through the albums alphabetically. For instance, if my DVD collection includes the shows Arrested Development, The Office, and The New Adventures of Old Christine, it would not benefit me to search through all of my DVD’s that begin with the word ‘The’ because my alphabetical sort will recognize the word ‘The’ in quest to alphabetize my DVD collection. As a result, the following formula field explains how a user can sort through their data in true alphabetical order.

1. Select a text-based formula field
2. Choose to create an advanced formula
3. Develop an IF/THEN logic statement to define your sort.
   • In this case, IF a DVD begins with the word ‘The’, then I want to do an alphabetical sort that ignores the word ‘The’.
4. Select the IF function from the function box on the right side of the create an advanced formula screen.
5. Add an open parentheses, type the word ‘BEGINS’, add an open parentheses and quotes around the text to be ignored and close the parentheses.
   • In this case, the formula should read IF(BEGINS(Name, “The”),
6. Now select the MID function in the function box on the right side of the screen to indicate the new starting position of your sort. The starting position will correlate with the number of characters contained in the text you want to ignore.
   • In this example, the word “The” is 3 characters, but a space will come before the next word, which means we want to ignore the first 4 characters.
7. After selecting the MID function, add an open parentheses, type Name, add a comma, type the number of characters to be ignored, add another comma, select the maximum amount of characters in your text field and close the parentheses.
   • The above formula would now read IF(BEGINS(Name, “The”), MID(Name, 4, 251)
8. If the only word to be ignored is the word “The”, you must add the following text to complete the formula: ,Name)
9. If you have other parameters to include in your sort, you must specify the amount of characters to be ignored.
   • For example, if you own the DVD “The New Adventures of Old Christine”, you might want the alphabetical sort to find this record by the word ‘Christine’. In this situation, you must add a formula that would read IF(BEGINS(Name,”The New Adventures of Old”, MID(Name, 26, 229). Now we have created a formula to ignore the first 26 characters of text, which will enable the sort to only recognize the word Christine.

Our two rules would be entered into a custom formula as the following: IF(BEGINS(Name, “The New Adventures of Old”), MID(Name,26,229) ,IF(BEGINS(Name, “The”), MID(Name,4,251) , Name))

Other information to know: When creating multiple rules to sort data, the longest rules must be entered first and followed by rules to ignore fewer characters. Additionally, you must add a closed parentheses to end of the formula for each custom rule you have created.

In summary, developing a custom formula field to sort records in true alphabetical order is easily attainable by creating IF/THEN statements to outline your specific data sort.

Have you ever tried to find out how many of your Accounts have Opportunities? It sounds simple at first, but when you run a report on Opportunities you can only report on the total number of Opportunities, not Accounts. Here’s a simple solution:

1. Create a new Formula Field on Accounts
2. Name the field “Account Counter”
3. Choose Number for the Output Type
4. Just type in the number 1 for your formula
5. Save the new field

Now go back to your report on Opportunities, on the Select the information to summarize step, check the Sum box next to your new “Account Counter” field and click Run Report.

See what happened? At the bottom of the report, you now have the total number of Opportunities as well as the number of unique Accounts.

I’m sure you noticed a long time ago that in order to change the Record Type of a record in Salesforce you have to be on the View page for that record, not the Edit page. If you’re like me, then this makes no sense at all to you. Most users do not distinguish between changing a Contact’s phone number and changing its Record Type, so why do you have to go to two different pages to perform the two changes?

Well now you don’t, a long time ago we implemented Workflow Rules and Workflow Field Updates to automate changing Record Types based on changes made on an Edit page. For instance, let’s say you have a picklist field on Accounts called “Type” with values “Prospect” and “Customer”. And let’s also say that you have Account Record Types with the same names.

You can setup one Workflow Rule on Accounts that runs when the following criteria is met:

• Type equals Prospect
• Account Record Type not equals Prospect

Then setup a Workflow Field Update to change Account Record Type to Prospect.

You can then setup a second Workflow Rule on Accounts that runs when the following criteria is met:

• Type equals Customer
• Account Record Type not equals Customer

Setup another Workflow Field Update to change Account Record Type to Customer.

That’s it! Now remove the Account Record Type from your page layouts and never worry about it again!

Last week Scott @ Arrowpointe posted a great alternative to the dashboard component on the Home Tab. He built an s-control that can be used in conjunction with a Home Page Component to truly provide a quick snapshot of your key metrics.

Scott’s concept is simple yet highly effective, so we’ve leveraged his code to take the component out of the Sidebar and into the main portion of the screen. This helps overcome the current limitation of only 3 dashboard components on the Home Tab. Take a look:

Configuration of the statistics requires basic understanding of s-Controls, but it would be great if someone put together a package that utilized custom objects to provide a friendly user interface.