Saturday, December 22, 2012

How to add number of Hours to a Date Field


I am back with a very basic requirement which might looks very easy, but the solution might not be that easy to implement.

Requirement:
I have a Service Request form applet where I have following fields exposed on the UI:
  • Severity - LOV field values: a) 1-Critical b) 2-High c) 3-Medium d) 4-Low
  • Commit Date - Date Time field
Depending upon the Severity, being set by the user, system need to stamp the "Commit Date" field which is based on the calculation as per below table
If Severity Equals
Then SLA Hour =
Set Commit Date to [Created Date] + SLA Hour
1-Critical
10
[Created Date] + 10 Hours
2-High
20
[Created Date] + 20 Hours
3-Medium
30
[Created Date] + 30 Hours
4-Low
40
[Created Date] + 40 Hours
All this needs to happen whenever user changes the value for “Severity” field.

You can think of various ways to achieve this:
  1. The famous old method says extract the Year, Month, Date, Hours, Minutes and Seconds out of the “Created Date” field, get the SLA Hour depending upon the “Severity” value and do the calculation (add SLA Hour to the "Hours" variable ) and then combine the parts to get the date back. But the Problem with this method is, you need to be very careful with the calculation because while adding the "Hours" get exceeded by 24, then you need to increase the date by 1 and if then again adding 1 Day to the date reaches the month limit, you need to increase the month by 1 and so on ........................ problem, problem, problem :|
  2. I was also thinking to first convert the "Created Date" variable into "Seconds", then add the ("SLA Hour" * 60 * 60) to it and re-convert it back to Date. But didn't find the required function/methods available OOB for this in eScript. So, I dropped this idea as well.
So, the only tricky part here is how to get the number of “Hours” get added to a Date and after some of the experiment I was able to achieve it with some simple and easy method.

Here you go:
  1. Get the master data setup in the LOV:
  2. Create two calculated fields:
    Name
    Calculated
    Calc Value
    Type
    SLA Hour
    True
    LookupValue("SLA", [Severity])
    DTYPE_NUMBER
    Temp Commit Date
    True
    [Created] + ([SLA Hour]/24)
    DTYPE_DATETIME
  3. Check the “Immediate Post Changes” = True for field “Severity"
  4. Create a Business Component User Property:
Name       :           On Field Update Set
Value       :           "Severity", "Commit Date", "[Temp Commit Date]"

That’s it. Make sure you have the “Immediate Post Changes” checked for “Severity” and be careful about the “Type” for the two calculated field created, otherwise it won’t work.





5 comments:

  1. HI Gaurav,

    I just started following your blog. What i could i say dude, i learned so many things from your blog. You are doing really a tremendous job.God bless you :)

    ReplyDelete
  2. Hi Gaurav Sir,

    here i have requirement that in Opportunity and contact Home page applet there are Pick Applet fields, in this pick applet it will display country code numbers like 00412,00425,004875 and you can select the records this is the normal process of pick applet, but issue here is normally the pick applet field is empty, when user enters any number in pick applet field eg 0005 and give a tab Pick Applet get's open automatically ...here my requirement is when user enters a number and tab it ...the pick applet should not get open, unless it called manually How to avoid it ? need ur special attention !

    ReplyDelete
  3. Hi Dinesh,

    Remove the "Bounded" property of the "Picklist" used for this configuration.

    Moreover, you can use the "Forum" section of this blog to ask question which are not related to the blog post :)

    Cheers
    Gaurav

    ReplyDelete
  4. Hi Gaurav,

    Nice Scenario.Thanks for sharing your knowledge.

    Regards,
    Purusotham

    ReplyDelete