Showing images in a list view - A picture IS worth a thousand words!

If you have been using Salesforce a lot, you are probably familiar with the list view by now.  That is what gets displayed when you click a tab, showing you a list of records that your organization has for an object.

You may have also tried to edit or even create a new view because the default view is not quite what you want.  You may want different data, or you may want the information presented in slightly different order.  However, no matter how you customize it, it's still showing you texts after texts after texts, which is rather dull.  You may have a list of all inventories you have in your warehouse, including its inventory count.  Unless you sort your list by quantity,  it may not be straightforward to see which inventory is running low.

Here is the good news:  you can actually use a few simple tricks to display some images in your list view.  Here is how.

For this example that I am using to track my inventory, let's say I want to use different colors to represent the level of inventory count.  So if my count for a product is less than 200, I should pay some attention to it; and if it falls below 50, I really need to do something immediately.  This is like the traffic light analogy.  Green represents safe, referring to merchandise that I have abundant supply of.  Yellow represents attention, referring to stock level between 50 and 200.  Lastly red represents warning, referring to stock level below 50.

Do you know that Salesforce comes with some predefined images?  You can find some of them by reading this document (NOTE:  it is a document from Salesforce dated back in 2005 so some information may have become out of date).  If you want more variety, you can download a Graphics Pack app from AppExchange that has even more images you can use.

For this example, I'm going to use the traffic lights which are readily available at Salesforce.com:

	/img/samples/flag_green.gif
	/img/samples/flag_yellow.gif
	/img/samples/flag_red.gif

In order to use the image files, all you need to do is to create a formula field in the Merchandise__c object.  The output of the formula field will be a string that indicates the name of the filename of the image.  I am going to call this formula field Quanaity_Level__c.

This formula is going to make use of two functions, image() and if().

If you have done some programming at all, you probably can guess if().  The if(condition_test, condition_true, condition_false) function takes three arguments.  The first argument condition_test is the condition to check, the second argument condition_true is what you want to return if the first argument is true, and the third argument condition_false is what you want to return if the first argument is false.

The function image(image_url, image_text, image_height, image_width) takes four arguments.  The first argument image_url is the url to the image file.  The second argument image_text is the text to display if the image does not load for some reasons.  The last (optional) arguments image_height and image_width represent the size of the image.  The image shrinks or stretches if the size specified here does not match the actual size of the original image.  If these two arguments are omitted, the image will be rendered at its original size.

What we are trying to do is to check the Quanaity__c field of the Merchandise__c object.  With careful coding, applying the above business logic I outlined, this is what the formula field will look like:

if(Quantity__c >= 200, 
  image('/img/samples/flag_green.gif', 'Abundant'),
  if(50 <= Quantity__c && Quantity__c < 200,   
    image('/img/samples/flag_yellow.gif', 'Attention!'),
    image('/img/samples/flag_red.gif', 'WARNING!!!')
  )
)

Let's say you have already defined a new list view called "Merchandise with Quantity".  It may look like this:

After you have created this formula field, all you need to do is to include it in your list view definition.  Your list will look like this.

Of course, you don't really need to have an image every line.  You might only want to show the flag when your merchandise has gone down to dangerous level, because that will really make your merchandise with a low inventory count stand out.

Do you know how to change your formula field so you can produce this list?

Lastly, keep in mind that formula fields can not only be used in your list view.  Once you have created it, it can be used in many places, such as in your form (either edit page or detail page) or even reports.  Let's go and explore!

Using standard controller's "getRecord" method

There seems to be a common problem using the getRecord function, so I am going to write it down here.  This problem has haunted me a few times now.

Whenever you use a controller extension, extending a standard object, you can use the getRecord() to get the reference to the record.  For example, if you have an extension to the contact object, this can be what your Apex class looks like:

public class ContactExtension
{
    private Contact contact;

    public ContactExtension(ApexPages.StandardController sController)
    {
        this.contact = (Contact)sController.getRecord();
    }
}

Besides the obvious constructor, you will most likely have some other methods inside your class to manipulate the contact.  Let's say you want to provide a form so the user can change email address and phone number of the contact.  Your VisualForce class looks very simple

<apex:page standardController="Contact" extensions="ContactExtension">
    {!greeting}
    <apex:form>
        <apex:inputField value="{!contact.Email" />
        <apex:inputField value="{!contact.MobilePhone}" />
        <apex:commandButton action="{!save}" value="Save" />
    </apex:form>
</apex:page>

The {!greeting} is going to display some greeting messages.  That means we will need to write a getGreeting() method in the class.

If this is the definition of your getGreeting() inside the class,

public String getGreeting()
{
    return 'Hi ' + contact.FirstName + ' how have you been?  Please update the following.';
}

you will then get an error message that looks like this:

System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Contact.FirstName

Most of us think that as soon as we call the getRecord() method, then the entire record is loaded into the contact variable.  However if you have an object with 5000 fields, and you only need to use 2 or 3 in your Visualforce pages, do you think it really makes sense to load all 5000 fields for that record into the contact variable?  Probably not.

Then what can you do?  The easiest way is to do add this into your Visualforce page:
<apex:outputText value="{!contact.FirstName}"/>

If you say, but I don't want to display the contact name twice!  Then you can simply change this to:
<apex:outputText value="{!contact.FirstName}" rendered="false" />

By adding rendered="false", that will hide this variable.  Or perhaps use <apex:variable> instead, such as:
<apex:variable value="{!contact.FirstName}" var="contactFName" />

When you load this Visualforce page, the standard controller will scan through your Visualforce page and knows that you need this field, and when making the getRecord() method call, it automatically and implicitly adds this FirstName field into the SOQL.

Remember, if you want to use any field in your extension via the getRecord() method, you need to let it be known by making sure that the field exists in your Visualforce page!

(Another alternative is to write your own SOQL in your Apex class, but why make an extra SOQL call when you can avoid it?)

Caution with the ID field

The subject says it all.  It is important to know that Salesforce's Id field is a field that needs to be handled with care.  It is a read-only field.  

Here is a use case that I have encountered.  I'm going to keep using my Rig example from my last blog entry.  Suffice to know is that I have an object that stores information about rigs, which are big machineries you deploy at well sites in order to drill oil.  One of the things we capture in the Rig object is the Id of the rig owner (Rig_Owner__c), which is a lookup to the standard Account object.

Let's say all account information and rig information has been imported into Salesforce from an external system, and all data has been verified to be imported successfully.   Now one of the rigs has been sold from company A to company B.  We need to update the rig information.  When the user clicks the lookup icon to the Account, too many records are returned because the system has over 50000 account records.

It's decided we need to add a custom checkbox (or boolean) field into the Account object, called Rig_Owner_Indicator__c, and is set to true only for accounts that are rig owners.  This should help eliminate the number of records returned when we use the Lookup Filter associated with the lookup field.

After adding the column and defaulting the field values for all records to be false, now it's time we set those account records that are actually rig owners.  All rig owners are already identified in the Rig_Owner__c of the Rig object.  So one way we can run our code is:

List<Rig__c> rigs = [SELECT Rig_Owner__c FROM Rig__c];

List<Account> accounts = new List<Account>();

for (Rig__c rig : rigs)

{

    account RigAccount = new Account();

    RigAccount.Id = rig.Rig_Owner__c;

    RigAccount.Rig_Owner_Indicator__c = true;

    accounts.add(RigAccount);

}

update accounts;

However, you will get an error message that says the Id is not writeable, as this screen shot from Eclipse shows.

The offending line is

    RigAccount.Id = rig.Rig_Owner__c;

In order to fix the problem, you will need to instantiate your Id in the line where you create a new instance of the Accout SObject.  That is, if you rewrite the above as below (note the bolded line), the Apex code will execute successfully, and your Rig Owner Indicator will be populated as desired.

List<Rig__c> rigs = [SELECT Rig_Owner__c FROM Rig__c];

List<Account> accounts = new List<Account>();

for (Rig__c rig : rigs)

{

    account RigAccount = new Account(Id = rig.Rig_Owner__c);

    RigAccount.Rig_Owner_Indicator__c = true;

    accounts.add(RigAccount);

}

update accounts;

The code above will now compile.  That goes to show that when you are trying to manipulate the Id field, you need to set it when you instantiate the SObject.  Make sure you do NOT attempt to set the Id field.  Remember, it is a read-only field.

Lastly, the revised Apex code is still faulty because it will fail if more than one rig belongs to the same rig owner.  This is related to adding SObjects to List collection objects.  I will leave that to my next blog.

Counting records using SOQL

Sorry for the lack of update.  The last few days I was studying hard for my Winter 13 Maintenance exam for both my Developer and Administrator certifications.  Sure glad I passed them both, and I'm now back to my blog.

Today I'm going for a smaller article but for me personally, a significant one.  It is one of those things that really reminds me I have to have a change of mindset when constructing SOQL queries. This example below is a really really simple example, but I have to admit, it took me awhile to get there.

Let's say, I have a Province table and a PostalCode table.  You want to display a list of provinces including the number of postal codes for each province.

I came from SQL Server background.  In the past, when I saw a request like that, I would think, ok, start from the Province table and then inner join to the PostalCode table and get the count.   The query would look something like that:

SELECT p.Name, Count(pc.*) AS NumPostalCodes
FROM Province p INNER JOIN PostalCode pc ON p.Id = pc.ProvinceId

My point is, I always think from Province table (the parent) towards the PostalCode table (the child), and not the other way around.  Of course in SQL it does not really matter because you could just swap Province and PostalCode around and you will get the same results.

Alas, in SOQL, you cannot have two table names in the FROM clause.  What could you do then?  It would be nice if I could do something like:

SELECT Name, (SELECT Count(Id) FROM Postal_Codes__r) 
FROM Province__c

however SOQL does not allow aggregate functions in subqueries.

Let's say instead of getting the count, you just want to dump an entire list of provinces and the related postal codes.  In SOQL you could do it like that:

SELECT Name, (SELECT Name FROM Postal_Codes__r) FROM Province__c

This is not bad but notice that this is not a linear list.  The second column of each row returned is in turn a result set.

If you want a simple linear result set, just like you would in the SQL world, what you want is:

SELECT Province__r.Name, Name FROM Postal_Code__c

This SOQL will give you a linear list of all provinces and their related postal codes.  Look!  The starting point of this SOQL is from the child table.

This is what I talked about earlier, you have to have a change of mindset when construting SOQL queries.  Once you get that, getting the count is simply a slight modification of the query statement:

SELECT Province__r.Name, Count(Name) NumPostalCodes 
FROM Postal_Code__c 
GROUP BY Province__r.Name

Again, like I said, this is a very simple example, but for me to get to the last SOQL it took me almost an hour.  It's just not natural for me to think from the child table when I encounter a request like that.  I hope you find this information useful.

Next time I want to talk about retrieving records across 2 tables via the junction table in a master-detail relationship.

Using the standard Name field

Be careful what you save in the Name field - it is a text field, so if you have Names that are 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, then if you sort them, 1 is followed by 10 instead of 2.  If you really need to treat them as numbers, use a custom field of the Number type instead.

Process execution order

I think it's really important to understand the execution order of some of the processes in Salesforce.  For example, you need to know that when saving a record, validation rules will be executed before assignment, and assignment takes place before workflows.  Finally, escalation rules will get fired.

Do note that there are a lot more processes happening in the background (triggers, for example).  To understand Salesforce, and to study for your exam, make sure you know the complete order of execution!!

Attributes for : showHeader, sidebar, standardStylesheets

As you know the most simplistic Visualforce page can look like this:

<apex:page>
<h1>main title</h1>
content
</apex:page>

You can provide attributes such as showHeader and sidebar to determine whether you want to show the header or sidebar, respectively, simply by setting the values to true or false, such as:


<apex:page showHeader="true" sidebar="false">
<h1>main title</h1>
content
</apex:page>


Do note that if showHeader is set to false, then sidebar will also be hidden!

Note also, that simply by setting showHeader = false, it does NOT mean you are not using the Salesforce's stylesheets (i.e. Salesforce stylesheets are still being applied).  If you do not want to use any Salesforce's stylesheets at all, then you also need to use the standardStylesheets attribute and set it to false.