Time difference in calculated fields

A user sent me a requirement today which I found interesting (to be honest a bit easy which I could complete and test quickly),  but this is a common requirement and can be daunting for a Siebel newbie. So thought of posting the solution for everybody.

Requirement:
When a Service Request is created the default status is “Pending”. Now the user starts work on it and finally changes the SR status to “Closed” when done. We need to display the time taken to complete the Service Request (from “Pending” to “Closed”) in a field called “Handle Time”.

Assumptions:
For the solution I have assumed certain things which are
1. We need to display “Handle Time” in minutes.
2. Changing the status to closed is the last thing that user does on Service Request.

Solution:

1. Create a calculated field with expression:

IIf([Status] = ‘Closed’, ([Updated] – [Created]) * 24 * 60, ”)

2. Set the “Immediate Post Changes” Flag to true for Handle Time
3. Expose this field on the Applet and you are done.

Note: In case if there is some processing that is done after the status is closed then this will not give you accurate time as the updated date will change accordingly.

For this case you can follow the below given approach.

  1. Create a base field “Close Time” to store the time when SR is closed
  2. Create a On Field Update Set user property to update “Close Time” with following experssion
    1. “Status”, “Close Time”, “IIF([Status] = ‘Closed’, Today (), ‘’)”
  3. In the Handle Time field change your expression to the following
    1. IIf([Status] = ‘Closed’, ([Close Time] – [Created]) * 24 * 60, ”)

Comments are welcome!!

This Post has been viewed : 11,616 Times


6 Responses to Time difference in calculated fields

  1. Hi, When the status of the SR is closed then the SR goes Read Only (per Vanilla) and the record cannot be modified. In the second approach per me we are unnecessarily creating a field and using a column at the DB which is already present in the shape of LAST_UPDATED on the table level. Per me this is just a fancy way of doing thing.

    • Second approach is for situation where we can update after closing.
      Imagine you have an admin screen (we often have) where some users have ability to modify the SR anytime.

      User closes the SR today and after two days admin goes and changes something first approach will start giving wrong time.

  2. Oops!! Seems like you are working in highly customized environment where you have Administration -> Service Request. I have never seen this and probably will never in vanilla. Siebel’s Service Request is so strong that it can handle all the business scenarios and the way SRs are handled across the globe. Seems like the BA’s/ Architects are NOT aware of the service module in Siebel and there was a need to configure this type of screen. — ABC

    • You are right… that’s the disadvantage of working with a product… you only know things that you have been told by someone.. and experience matters a lot.

  3. How to default the time setting to end of business i.e. 5pm instead of 12:00am when selecting date through the calendar control?

Leave a Reply

Contribute