3 Replies Latest reply on Aug 4, 2010 3:17 PM by philmodjunk

    Script to replace calculated value when conditions met

    Teague

      Title

      Script to replace calculated value when conditions met

      Post

      Hi, I would love a hand with what is likely not a challenging problem but for some reason, I just can't get it right.

      I have two time variable that I would like to subtract to provide a calculated duration variable that I would prefer to output in minutes only but is currently producing an "hour:minute:second" calculation:

      D2B_time = Mod (Balloon_time - Door_time + 86400; 86400)

      However, sometimes there is no Balloon_time. In these situations, I have been entering the Balloon_time as 0 but the D2B_time product is an incorrect set of hours and minutes that is throwing off my data set. I would like to write a script that sets the D2B_time to "0"  (or clears the field or something similar) in this situation. My script current attempt at this script  is:

      Set Field By Name[If (Activation_Events::Balloon_time = "0";
      Activation_Events::D2B_time; 0)]

      but this seems to do nothing at all. I have also been playing with setting the script trigger but that hasn't helped.

      Any input would be greatly appreciated as I clearly over my head a little.

      Thanks much!

        • 1. Re: Script to replace calculated value when conditions met
          philmodjunk

          Normally, you would compute elapsed minutes for an interval as:

          ( Time2 - Time1 ) / 60

          If you want a value of 0 when time2 = 0, use this expression:

          If ( Balloon_time = 0 ; 0 ;
               (Balloon_time - Doortime ) / 60 
              )

          • 2. Re: Script to replace calculated value when conditions met
            Teague

            Thanks so much for helping. I think I'm still missing something. I've tried multiple permutations on your equation above and at best all I seem to be able to get is a boolean response with a 0 when time 2 (balloon_time) = 0 and a 1 when it doesn't.

            Currently I have the calculation written as:

            (Balloon_time - Door_time) / 60
             or
            If (Balloon_time = 0; 0;
            (balloon_time - Door_time) / 60)

            but this just makes all my calculations a "1". Rearranging the order or trying multiple If clauses such as:

            If (Balloon_time  ≠ 0; (Balloon_time - Door_time) / 60)
             or
            If (Balloon_time = 0; 0; (Balloon_time - Door_time) / 60)

            also seems to set up a boolean expression rather than a 0 when time 2 is 0 and a calculated interval when time 2 is not 0 (which is what I'm after).

            Any other thoughts?

            thanks for the help.

            • 3. Re: Script to replace calculated value when conditions met
              philmodjunk

              Both Balloon_time and Door_Time should be fields of type time or both should be of type timestamp. You should use timestamp if there is any chance that midnight will fall inside the interval between these two times as subtracting timestamp values will still evaluate correctly where time fields will not.

              Here's a demo file you can look at:  http://www.4shared.com/file/YVcHlbNn/TimeIntervalMinutes.html