Difference between 15 and 18 character ID’s and history of why they both exist

Standard

If you have any data updates, integration, formulas using something like ProfileID, or just simply noticed a bunch of characters in your salesforce.com URL when you are reviewing a record then you might know there is a 15 character ID and an 18 character ID. But what is the difference? And why two ID’s?

The 15 character ID is what you will see when you view the URL. Go to an Account and you will see this; for accounts they start with 001. This ID is case-sensitive (I will go more into that later). The 15 characters are completely unique in your salesforce.com instance, when case-sensitive.

The 18 character ID is seen when you, for example, use a tool like Data Loader to extract your data. The first 15 characters are exactly the same as what I wrote above, but with an additional 3 characters. This ID is case-insensitive. The 18 characters are completely unique in your salesforce.com instance, when viewing as case-sensitive or case-insensitive

What does all this case-sensitive or case-insensitive mean? Basically case sensitive means that ABC, aBc, and abc are seen as unique. Case-insensitive means that ABC, aBc, and abc are seen as the same. Databases are can be setup to be case-sensitive or case-insensitive.

Now to a bit of a history lesson to show why salesforce.com has both 15 and 18 character ID’s. Salesforce.com is built on Oracle databases and Oracle is good as default to case-sensitive for databases. Salesforce.com has great developers and used this case-sensitive database approach. I do not know the math but I know the 12 unique characters (not 15, since each ID starts with 3 characters of the object they are in…ie: Accounts = 001) gives for trillions of records without issue of running out of unique ID’s for each object.

Wait, if I can have trillions of records on each object with the 15 character case-sensitive ID why is their this 18 character ID? When salesforce.com first started out there was no API yet. The API came, I believe, towards the end of 2002 / beginning of 2003 (I could be off a bit). Before the API existed ID’s were not that important to customers (a bit but not like now). When the API came out customers started integrated data with salesforce.com, led to running updates against data instead of just importing data, using Excel VLookups (don’t laugh, that is what we did a lot in the olden days) or external databases to match data for updates, deletes, analysis, etc… If you play with data you might be seeing where this is leading. Salesforce.com didn’t have an issue but external systems/applications did.

Many databases are setup as case-insensitive. Excel VLookup and other functions are case-insensitive. Some development languages are case-insensitive.  Etc…etc… The more data you have in a salesforce.com object you have a greater chance of have two ID’s that were the same when viewed as case-insensitive. I encounter this often. Because of this salesforce.com had to add something so people/tools/applications that were case-insensitive could see each ID as unique and able to update data to the correct ID. So salesforce.com added the 3 extra characters to the end of the ID, and now every 18 character ID is completely unique when case-insensitive.

So that is about it. This is why you see both a 15 character ID and an 18 character ID. All about case-sensitivity.

TIP: If you export data from reports and you want to make sure the ID’s are unique you can use the CASESAFEID() function. Basically create a custom formula field on your object.You don’t need to display the field on your page layout. All you will do is use this field when exporting or needing the 18 character ID instead of the 15.

Using Roll-up Fields for better List Views

Standard

Using salesforce.com list views correctly can save people a lot of time and make users more productive than reporting. Reports have a purpose and list views have a purpose. Unfortunately many do not use list views enough. I will try to give some tips to make list views work better for you by using roll-up fields.

Currently salesforce.com doesn’t allow you to do multiple objects in list views, unlike in reports. List views only allow you to use the one object you are on. You can vote for cross object list views on Success Community site. Until this Idea is implemented, if it ever is, we need to find workarounds. I will give a few examples below. Of course there is more you can do and I surely do not know of everything but I feel these can be salesforce.com time savers.

These list views are just some examples of what you can do. Since everyone’s business is a bit different you can use these are more of a tutorial and then create your own to suit your needs.

Accounts without Opportunities

Using custom report types you can create a report that shows accounts that have no opportunities. But with list views this cannot happen….unless you create a roll-up summary field.

First you will create the new roll-up summary field.

  • Create a new custom field on the account object.
  • Choose Roll-up Summary as the data type, then click Next.
  • For the field label type in Number of Opportunities, then click Next.
  • For Summarized Object choose Opportunities
  • Click Count, then Next.
  • Choose who you want to have the ability to see this new field, then click Next.
  • It is up to you if you want people to see this field on the page layout or if just to be used in views. So either check or uncheck what fits your need. For this example check all the boxes so the field shows on all page layouts.
  • Click Save

Now you have a new field that simply counts all opportunities under an account. If there are no opportunities for an account then the number will be 0.

So knowing that you now have a count of opportunities we can create the list view.

  • Click on the Account tab
  • Click the Create New View link
  • Name the view Accounts without an Opportunity
  • Under Filter by Additional Fields choose Number of Opportunities EQUALS 0
  • Click Save

Now you have a list view that will only return you account that do not have an opportunity. This can help you find potential missing revenue.

Accounts without Won Opportunities

So now we will take the example above and add to it. For this we want the same account view to only show accounts that doesn’t have any won opportunities. So they can have opportunities, just none that have been won.

  • Go back to your roll-up summary field on the account object. Click Edit by the field
  • Choose Only records meeting certain criteria should be included in the calculation
  • Make the criteria Won NOT EQUAL TO True
  • Click Save

That is it. You are done. Now your list view, since it always is referencing this field, will only show you accounts that has never had a closed and won opportunity.

Accounts with Opportunities in a certain year

Now lets look at changing the above list view to only show you accounts that have an opportunity in 2013 (as an example). Salesforce.com usually allows you to use relative date values, such as CURRENT YEAR, LAST YEAR, etc…. For this example I wanted to make a point. Unfortunately you cannot use relative dates in roll-up summary fields. I assume this is for performance reasons as relative dates change constantly.

So if you want to do a list view of something like Accounts with Opportunities from last year you will have to put the in two filters….1) Close Date greater or equal to 1/1/2013 and 2) Close Date less than or equal to 12/31/2013.

It is a bit of a pain but if you create the views with names that include the year instead of using “Last Year”, for example, then you are fine. You just have to remember to create new views each year.

 

So you can keep going on and on. My point of this was to try and make you think outside the box most stay in. Get creative and don’t stop using list views because of cross object limitations. List views are great in many ways. You just need to be creative in using them.

Advanced Image formula fields

Standard

Earlier I had a tutorial on how to create an image formula field in salesforce.com. In this tutorial I am going to take this a little farther. I will show how to have multiple images in one formula field.

An example of having multiple images in one salesforce.com formula field could be something like showing multiple images of money for amounts of all Opportunities on an Account.

  • First thing you want to do is create a Roll-up Summary field on the Account object.
  • Name the field “2013 Sales”.
  • Then choose Opportunities as the Summarized Field.
    • Sum
    • Field to Aggregate = Amount
    • Only records meeting certain criteria should be included in the calculation
    • You will want three criteria
      • (Close Date GREATER OR EQUAL 1/1/2013)
      •  (Close Date LESS OR EQUAL 12/31/2013)
      • (Won EQUAL True)

The above will give you a total amount of all 2013 sales for each Account.

With the next steps we will build the field that will display the images. We will show 1 image of money for account that have over $0 in sales but less than $10,001. Show two images for accounts with less than $25,001 in sales. Three images for accounts that have less than $50,001 in sales. 4 images for accounts that have less than $100,001 in sales. And 5 images for all accounts that have more than $100,000 in sales.

  • Create a new formula field
  • Name the field “Show me the money”
  • Choose Text as the formula field type
  • For the formula paste in the following (I will go into detail afterwards)
    • IF ( 2013_Sales__c > 0, IMAGE(“/img/icon/cash24.png”, “money”, 18, 18), NULL)+ ” ” +

      IF ( 2013_Sales__c > 10000, IMAGE(“/img/icon/cash24.png”, “money”, 18, 18), NULL)+ ” ” +

      IF ( 2013_Sales__c > 25000, IMAGE(“/img/icon/cash24.png”, “money”, 18, 18), NULL)

      + ” ” +

      IF ( 2013_Sales__c > 50000, IMAGE(“/img/icon/cash24.png”, “money”, 18, 18), NULL)

      + ” ” +

      IF ( 2013_Sales__c > 100000, IMAGE(“/img/icon/cash24.png”, “money”, 18, 18), NULL)

    • Choose Treat blank fields as zeroes.
    • Go through the rest of the steps and save.

So what you are doing is simply adding more images to the formula field each time the IF statement is true (and when false it is NULL, thus adding nothing). We are using the salesforce.com cash icon (read my first post on image formulas to find out how you can use these images). It is really that simple. The + sign just keeps adding additional images. You don’t need to use the same image on each if statement. You can do whatever you want.

The biggest key to this is the size of the image. The , 18, 18 at the end of each image is the width and height. You MUST put this in or the formula will give an error when two or more images will be displayed. Again…this is a MUST or the formula won’t work as intended.

I add a blank space between each image using + ” ” + . I just like a little space between each image. This is a preference thing. You can simply use a + sign between each IF statement, if you want.

Go play around with this and have fun. Spruce up your salesforce.com pages with some images!

Images inside salesforce.com fields

Standard

Sometimes it is better to represent something with an image or icon rather than text. In salesforce.com you can show images on your pages. And these images can be driven by formulas, thus making them dynamic.

A few examples:

  • If a customer is on Credit Hold show a red flag
  • If a customer has X in sales show a certain image to represent they are a special customer
  • If a customer as a certain type of agreement with you show X image to represent that
  • If a contact hasn’t been “touched” in X days show an image to represent that fact

Lets take the Credit Hold scenario as an example. Say you have a custom field named Credit Rating (credit_rating__c) on the Account object. This is a picklist that has the values of Excellent, Good, Poor, On-Hold. When the customer is on hold you want to show a Red Flag on the Account page layout.

To show images we use the Image function in a formula field. The image can be a local salesforce.com image or you can link to any image on the web.

Okay, lets give it a shot. Here are the steps.

  1. Create a new field on the Account object.
    1. Data type is Formula field
    2. The label name will Credit Standing (credit_standing as the field name)
    3. Choose Text as the Return Type
  2. In the formula section paste in the following
    • IF (credit_rating__c = “On-Hold”, IMAGE(“/img/samples/flag_red.gif”, “red”), IMAGE(“/img/samples/flag_green.gif”, “green”))
  3. Click Next
  4. Choose the profiles you want to have access to this new field
  5. Click Next
  6. Choose the Page Layouts you want this new field to show up in.
  7. Click Save

So what will happen now is a Red Flag will show up if Credit Rating = On-Hold or else it will show a Green Flag. I am utilizing some salesforce.com icons. You can find all the icons  here – http://free-121d5f44d20-121d603d1c5-121ee2b8103.force.com/force2b/salesforceicons . You want to get the URL of the image and then just change it a bit.  Example:  Red flag URL from that page is http://free-121d5f44d20-121d603d1c5-121ee2b8103.force.com/img/samples/flag_red.gif . Take away everything before /samples and that is what you will use in the formula.

Coming soon after this I will show an example of how to have more than one image show up in an image formula field.