Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

Need to Convert a Duration Lookup Field to Numeric

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.

Formulas and functions
Quickbase Automations
  • SuryaExpert's avatar
    SuryaExpert
    Qrew Assistant Captain
    7 years ago
    I would check if there is a conversion function for this.
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      7 years ago
      The conversions I tried (ToNumber, ToHours) still gave me errors saying it was the wrong value.
    • SuryaExpert's avatar
      SuryaExpert
      Qrew Assistant Captain
      7 years ago
      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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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)


    • SuryaExpert's avatar
      SuryaExpert
      Qrew Assistant Captain
      7 years ago
      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?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      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.