Discussions

Expand all | Collapse all

Need to Convert a Duration Lookup Field to Numeric

  • 1.  Need to Convert a Duration Lookup Field to Numeric

    Posted 09-21-2018 21:32
    I have a Duration Lookup field from another table, and I want to create a checkbox formula that says yes or no if the lookup field value is greater than zero. The ultimate goal is to have a checkbox that will limit a trigger in a Workflow Automation (only change the records where the box is checked).

    All of this is a way to try and get around the limitation in Workflow Automations with duration fields.

    Example of logic: IF ( [TSK - Est. Edit Time] > 0, TRUE)

    Every fix I tried to apply told me I was using the wrong type of field.



  • 2.  RE: Need to Convert a Duration Lookup Field to Numeric

    Posted 09-21-2018 21:34
    I would check if there is a conversion function for this.


  • 3.  RE: Need to Convert a Duration Lookup Field to Numeric

    Posted 09-21-2018 21:36
    The conversions I tried (ToNumber, ToHours) still gave me errors saying it was the wrong value.


  • 4.  RE: Need to Convert a Duration Lookup Field to Numeric

    Posted 09-21-2018 21:46
    I am making an assumption here. But I think you get the idea. Assuming the duration field is a difference between two date fields, [From Date] and [End Date], I would have a formula checkbox in that table that says [From Date]=[End Date]. Then bring in that checkbox as a lookup field and that is the field you want. The logic here being that at the end of the day, the duration is the difference between two or more date or date-time fields and if you just want to see if the difference is zero, that means those two fields are equal. Does that help?


  • 5.  RE: Need to Convert a Duration Lookup Field to Numeric

    Posted 09-21-2018 21:50
    The corrected formula is this

    IF([TSK - Est. Edit Time] > Hours(0), TRUE)

    or you can also write is as just this


    [TSK - Est. Edit Time] > Hours(0)




  • 6.  RE: Need to Convert a Duration Lookup Field to Numeric

    Posted 09-21-2018 22:07
    Mark, I thought of that, but the duration can be even a few minutes or seconds, or technically even milli seconds. So if the duration is less than 1 hour, and you are looking for the hours, I am not sure if it will show 0 or 0.x. You know what I mean?


  • 7.  RE: Need to Convert a Duration Lookup Field to Numeric

    Posted 09-22-2018 01:48
    In fact, Zero seconds is equal to zero minutes is equal to zero hours is equal to zero days is equal to zero weeks.

    Zero duration is zero duration no matter the Units.

    So we can use Hours(0) or Weeks(0) or Seconds(0) to test against and the result would be the same.