Time calculation in expression/formula field

Posted by ithrees on 09-Apr-2015 06:15

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

All Replies

Posted by murali on 09-Apr-2015 10:04

Could you please post  your formula and the error message?
 
[collapse]
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.

[/collapse]

Posted by ithrees on 09-Apr-2015 11:57

Posted by murali on 09-Apr-2015 12:42

Can you try
 
return ( ({!End_Time_tt} - {!Start_Time_tt}) / (60*60*1000) );
 
[collapse]
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.

[/collapse]

Posted by pvorobie on 09-Apr-2015 12:53

Please use Formula Debugger ,

Posted by ithrees on 10-Apr-2015 01:13

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

Posted by ithrees on 21-Apr-2015 23:48

Any update on this?

Posted by ithrees on 22-Apr-2015 00:04

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

Posted by pvorobie on 22-Apr-2015 11:01

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?

Posted by ithrees on 23-Apr-2015 06:55

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.

Posted by pvorobie on 23-Apr-2015 11:35

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}")

Posted by ithrees on 24-Apr-2015 07:05

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

Posted by Sri Harsha on 07-May-2015 11:38

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.

Posted by pvorobie on 07-May-2015 11:44

I would recommend

parseInt(x) - parseInt(y)

Posted by Orchid Corpin on 07-May-2015 17:24

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

This thread is closed