Time calculation in expression/formula field - Forum - Rollbase - Progress Community

Time calculation in expression/formula field

 Forum

Time calculation in expression/formula field

  • Hi All,

    I am getting formula error when I try to subtract a time field from another time field. I am not sure weather this comes because of the return type is decimal as it there is no type for time to return.

    Is there anything I am missing here or it has any different work around?

    Thanks in advance,
    Ithrees

  • Could you please post  your formula and the error message?
     
    From: ithrees [mailto:bounce-ithrees@community.progress.com]
    Sent: Thursday, April 09, 2015 7:16 AM
    To: TU.Rollbase@community.progress.com
    Subject: [Technical Users - Rollbase] Time calculation in expression/formula field
     
    Thread created by ithrees

    Hi All,

    I am getting formula error when I try to subtract a time field from another time field. I am not sure weather this comes because of the return type is decimal as it there is no type for time to return.

    Is there anything I am missing here or it has any different work around?

    Thanks in advance,
    Ithrees

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Can you try
     
    return ( ({!End_Time_tt} - {!Start_Time_tt}) / (60*60*1000) );
     
    From: ithrees [mailto:bounce-ithrees@community.progress.com]
    Sent: Thursday, April 09, 2015 12:58 PM
    To: TU.Rollbase@community.progress.com
    Subject: RE: [Technical Users - Rollbase] Time calculation in expression/formula field
     

    Flag this post as spam/abuse.

  • Please use Formula Debugger ,

  • I tried putting 'return' and no use. When I try with the debugger it shows the correct value without any error. But I couldn't create this field as its saying there is an error in the formula

  • Any update on this?

  • This is what I am getting when debug the formula..

  • It seems like you have two values in ms, subtracting them and dividing dividing on number of ms in 24 hours. What seems to be wrong?

  • Debugger says nothing wrong but the system is not allowing to create this formula field saying error in the formula without telling what the error is.

  • If no value is set for Time field, corresponding template token will be resolved as empty string. That will cause an error in your formula. But if value is set, formula works just fine.

    To resolve this try using parseInt:

    parseInt("{!time}")

  • In my case I only need to calculate the value when the time is update other wise it should return 0.

    So I tried with,

    if ("{!End_Time_tt}" !== "" && "{!Start_Time_tt}" !== ""){
      return ( ({!End_Time_tt} - {!Start_Time_tt}) / (60*60*1000) );
    }else{
      return 0;
    }

    But still I get the error when there is at least one record without time values. I wonder why it is trying to compute something inside a failed condition.
    I am still looking for a way to achieve this without using after update trigger.

    Ithrees

  • The server side replaces the tokens with actual values during validation. So, your formula becomes syntactically incorrect to process.

    line 2 in your formula turns out as  :  return ( ( - ) ) / 60*60*1000);  (if the values are empty)

    Try assigning them to variables and use them. The forumla should validte successfully then.

    var x = "{!End_Time_tt}";

    var y = "{!Start_Time_tt}";

    if (x !== "" && y !== ""){

     return ( (x - y) / (60*60*1000) );

    }else{

     return 0;

    }

    Please let us know if this works.

  • I would recommend

    parseInt(x) - parseInt(y)

  • Hi,

    I think he is trying to return a Decimal, use parseFloat() to your start time and end time BUT you cannot directly parse it once the value is empty it will return NaN(see image below), I suggest to use conditional variable. See code below, just replace my tokens.


    Images: Null value in using parseInt/parseFloat empty field

    var sTime = "{!Start_Time}" == "" ? 0 : parseFloat("{!Start_Time}");
    var eTime = "{!End_Time}" == "" ? 0 : parseFloat("{!End_Time}");
    
    rbv_api.println(sTime);
    rbv_api.println(eTime);
    if (eTime !== 0 && sTime !== 0){
      return ( (eTime - sTime) / (60*60*1000) );
    }else{
      return 0;
    }


    Hope this may help.

    Regards,

    Orchid