SalesforceUser.com

In Part 2 of our series, we discussed how we create tasks for ourselves and other team members and where to find those tasks again later. In part 3, we continue the process of organizing and presenting that information in a concise and easy to follow manner.

So, in this lesson, we continue to examine the creation and use of reports to help manage our tasks. If you recall from our earlier discussion, we generally want to know the following things about individual tasks…

1. What is the Task.
2. Who is the Task assigned to.
3. When was it assigned.
4. When is it due (if has a due date).
5. Is it on time, or is it late

More below the fold!

Along those lines, we need to start considering what kinds of questions we might ask ourselves, or our bosses might be asking themselves, with regards to the tasks assigned to us or our team members. Consider the following scenario:

It is the start of the work day, and we want to look at a list of the tasks we have assigned to us. Now, this has already been done for us (as we showed in Part 2 of this series) but lets add some twists. What we are doing here is trying to define the criteria for our list of tasks.

• We only want to look at tasks assigned to us.
• We want to see who created the tasks (because our coworkers might be telling us what to do).
• We want to group these by overdue and not overdue, with overdue tasks being shown first.
• We want to group these things by priority. High priority tasks first, then normal, then low.
• We want to see the age of the task, and have our lists be ordered by the age, oldest to lowest.

In order to accomplish this, we need to create fields for Salesforce to “group” off of when we run reports. We need to somehow indicate whether the task is overdue or not. Salesforce does not by default tell us these things, but we can accomplish them.

To accomplish this we make use of a powerful feature provided to us, the custom formula field. Now, in order to make use of this feature, you are going to need to be an administrator, or you are going to need to sit down with one.

When reports are written (and we will cover this later) they can be made to group around certain fields. For example, you might make a report that shows all of the opportunities you have created, grouped by the account name. Therefore, for the task reports we want to design, we need to make some changes the existing list of task fields (which you can see below).

Now, in order to sort by the age, and to group by whether or not this task is overdue or not, we are going to have to make some new custom task fields. The fields we will need to make are:

Before we go to the video, lets take a moment to discuss the theory behind these modifications.

When calculating the age of the task, we want to consider the following “cases” or “states”. What I mean is, consider for a moment the lifecycle of a task and how its age is affected. The task lifecycle might be briefly described as…

• Task is created and assigned.
• Task is worked on. Status changes are recorded, notes are taken. Repeat as needed.
• Task is completed.

Now, consider what you might think of the age of this task. When the task is still not completed (ie. Open), then the age of the task should be considered as the difference in time between the moment you look at the task and the date/time it was created. For example, you look at the task on 10/16/2006 12:00:00, it was created on 10/15/2006 12:00:00 and it is still open. Then the task is one day old.

Now consider the effect of the task being completed. Lets say for example, your boss assigns you a task on 10/15/2006 12:00:00. You complete the task on 10/16/2006 12:00:00. Your boss comes back from vacation and checks on that task to see if it is complete on 10/18/2006 12:00:00. Now, if we used the same consideration as in the previous example, the task would be 3 days old. But really the task was only 1 day old when you completed it.

The video demonstrates the process in detail, but to facilitate your own efforts, I present the final formula here.

IF( {!IsClosed} , {!LastModifiedDate} - {!CreatedDate} , NOW() - {!CreatedDate} )

Lets break the formula down. We use a conditional statement to test whether or not the case is open. In this case, the condition statement takes the form of…

IF(Logical Test, Do this if true, Do this if false)

So, in our example, we test to see if the Task is closed (i.e. complete). Depending on the result of that test we use on of two different formulas to evaluate the age of the Task.

We also make use of some Salesforce fields and functions. We use the NOW() function/subroutine. This function gives us the current date when it is used. We also use fields from Salesforce. These fields (as we have discussed in previous lessons). When we use Salesforce fields, they come in the following format. A field always starts with “{!” and ends with “}“. In between the two ends, we have the Field Name. For example, if we use {!CreatedDate} in a formula, Saleforce will evaluate that and return the Created Date. Field names and field labels should not be confused, they are related but are not identical.

Case 1 (The Task is Closed/Complete): If it is, we evaluate the Age of the Task as “The date it was last modified - date it was created”. Using the Task fields, this is represented as…

{!LastModifiedDate} - {!CreatedDate}

Case 2 (The Task is Not Closed/Complete): If the Task is not closed, the we evaluate the Age of the Task as “What is the date at this moment - date it was created”. Using the Task fields, this is represented as…

NOW() - {!CreatedDate}

———— Begin Advaced Section ————

Ok, this section is more advanced, and involves some extra effort. You should pursue this section if you are really interested in writing reports that show an ontime status and allow you to group by it.

Lets consider the slightly different scenario of On Time Status. Unfortunately for us, we can not work with the standard Due Date for Tasks when working with Formula Fields. For whatever reason, Salesforce does not make this accessible to us when creating formula fields (Hello, Salesforce, feature request!). This presents a problem for us. Without the ability to access the due date, we can not create a custom formula field that would calculate for us whether this is late or not. This means that we are limited to using the age of the task, and making visual comparisons.

There is a work around however. We can create our own due date field, and use that as the basis for a formula that determines if we are on time or not. There are issues with this approach.

1. This approach creates a field keeping duplicate data, never a good thing.
2. If you want to keep the functionality that works with the existing due date (being able to search for overdue tasks on the home tab as an example), then users will need to put the date in twice (once for each due date field).

This is probably not a good idea, but it is a great training exercise, so we will complete it anyway. There are some software work arounds that will allow us to duplicate some of this functionality without creating new fields, and we will cover that in another post.

The video demonstrates the process in detail, but to facilitate your own efforts, I present the final formula here.

IF({!IsComplete},"Is Completed",IF(ISNULL({!Task_Due_Date__c}) ,"No Due Date", IF( {!Task_Due_Date__c} < TODAY(), "Overdue","On Time")))

Lets break the formula down. We use four conditional statements. First we need to determine if the task is complete or not, and if it isn’t complete we must then determine if there is even a due date, if there is, we must further determine if we are “On Time” or “Overdue”. If there is no due date, we can simply indicate that there is none.

Instead of the NOW() field, we use the TODAY() field. It is quite similar to the NOW() field, but instead of returning a Date/Time value, it only returns a Date value, which we need when making a comparison.
We also make use of our new custom Due Date field, as well as the built in TODAY() function/subroutine. You should also notice the presence of our new custom Due Date field {!Task_Due_Date__c}. All custom fields added to Saleforce by us will take the form of “{!” + “Field Name” + “_c” + “}“.

Test 1: Using the IF(Logical Test, Do this if true, Do this if false) as before, we evaluate if there is the Task is complete.

Test 1 Result if True: If the Task is complete, we simply return “Is Completed”.

IF({!IsClosed},“Is Completed”, ** Omitted **)

Test 1 Result if False: If the Task is not complete, we simply run the next series of tests.

IF({!IsClosed},”Is Completed”, Test 2)

Test 2: Using the IF(Logical Test, Do this if true, Do this if false) as before, we evaluate if there is a Due Date. The test we need to perform is one that sees if a date has been entered. Depending on the result, we run one of the two following statements.

Test 2 Resut is True: If there is no Due Date, then we simply indicate that there is “No Due Date”.

IF( ISNULL({!Task_Due_Date__c}) ,“No Due Date”, Test 3)

Test 2 Result is False: If there is a Due Date, we know that the task can be either “On Time” or “Overdue”, so we run Test 3.
Test 3: We simply need to compare the Due Date with the Current Date to see if we are “On Time” or not. We use the same conditional as before, only with a new test.

IF( {!Task_Due_Date__c} < TODAY(), “Overdue”,”On Time”)

Just as before, make the test, and if the Due Date comes before the Current Date, we now we are “Overdue”. If the Due Date comes after the Current Date, we know we still are “On Time”.

IF( {!Task_Due_Date__c} < TODAY(), “Overdue”,“On Time”)

By using all of these conditional tests, in conjunction with our custom date field, we are able to determine whether or not a Task is “Complete”, “On Time”, “Over Due” or “No Due Date”. We can use these results to sort our Tasks in reports and s-controls to better manage tasks. For now, go ahead an watch Video Lesson #2b: Creating an OnTime Status Custom Field.

In our next part of this series, we will create the report using this criteria. As always, keep your emails coming with your suggestions.