Forum Discussion

AudreyBenenati's avatar
AudreyBenenati
Qrew Cadet
6 years ago

Duration fields/automations

​I am requesting ideas on how to flag certain entries that have obvious or possible errors.

I am trying to base this on a duration field and have tried many workarounds. The field is "age on date of service".

-If it's less than 0 I'd like to create an automation to change another field in the same table to a noncompliant status.

-If it's outside other age parameters, I'd like to create an automation to change other fields to flag the entry for review.

I can't do automations on duration fields.
I tried building a formula field to copy the duration field's value, but this is not allowed.
I can't do these operations on the data entry form because I'm maxed out on derived fields.
When I try to do a QuickBase action instead of automation, the option for modifying a record is greyed out no matter what I do/do not enter into the action's fields.
I tried to build a report and highlight the out-of-range values on it, but you can't highlight based on formula fields.

Thank you.

------------------------------
Audrey
------------------------------
Formulas and functions
Quickbase Automations
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    You are probably overcomplicating this.

    I suggest that you unthink using Automations, and instead think about using formula field to calculate, say the value or a warning field or a status field.  Is there something preventing you from using formula fields?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Audrey,

    Echoing Mark's comment here that simpler is better.  Assuming you just need a single report make a new field, formula checkbox


    Non Compliant Service
    if ( [age on date of service]<Days(0), true,false)

    This will give you check box to report against of the duration is less than zero days.

    Now set a filter in your report that if [Non Compliant Service] is checked and you will have list of all them to be corrected.

    Put the report on the dashboard for your QC person and the can work on the fly to fix things.





    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • AudreyBenenati's avatar
      AudreyBenenati
      Qrew Cadet
      6 years ago
      Records that have a result of 0 or less than 0 are automatic fails and I don't need the team to review. I just want to automate a process to auto fail them, which requires changing several fields. I don't want to flag them for review; that is a separate scenario.

      Both scenarios have the same problem, in that automations can't be done based on formula fields.

      ------------------------------
      Audrey
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        6 years ago
        Audrey,

        Look at Triggers from Juiced Tech.

        www.juicedtech.com

        It is similar to Automations but more powerful.   You can build Scheduled Triggers to check records on a regular basis and kick off a wide variety of process changes.   The Juiced Triggers will work against Formula Fields.

        ------------------------------
        Don Larson
        Paasporter
        Westlake OH
        ------------------------------
  • AudreyBenenati's avatar
    AudreyBenenati
    Qrew Cadet
    6 years ago
    Formula fields of any type, including formula checkboxes, do not appear in the drop down options for automations.

    The reasons to flag something for review are many--over 20 today and growing. I just gave a few examples here. Automations appear to be the simplest means to flag them.

    The formula would be so complex and have many nested rules, which I would need to change at least twice a year, that it sounds like it would not be simpler than having automations do it.​

    ------------------------------
    Audrey
    ------------------------------
    • AudreyBenenati's avatar
      AudreyBenenati
      Qrew Cadet
      6 years ago
      reposting separately
      ​

      ------------------------------
      Audrey
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        If you feel that a daily overnight sweep is best, then you can set up a series of saved table to table imports to import the table into itself and merge on the Key field which is probably [Record ID#], so no new records will be created.

        For example, you create a field which calculates to the value you want a field set to overnight, based on some conditions.  So for example the field called [Update Compliant Status].  It will calculate to say either "non-compliant" or null.

        Then set up a saved table to table copy to import the table into itself filtered where [Update Compliant Status] is not blank, and map [Update Compliant Status] to the field [Compliant Status]

        Then set up a Daily scheduled Automation where Step 1 is to run that import.

        Then repeat the process for a different one of your 20 fields and set up a separate import and make that import the 2nd step in the daily automation.  You get 50 steps in an Automation and if that is not enough(!), then you can always start a new Automation and get 50 more.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------
  • JasonJohnson2's avatar
    JasonJohnson2
    Qrew Cadet
    6 years ago
    Maybe I am seeing this wrong but as I read through the last part I think you need a little help learning how to do a few things that some of us take for granted since we have been this so long, if I'm wrong about that I do apologize. Hopefully this helps some.

    "I can't do automations on duration fields.
    I tried building a formula field to copy the duration field's value, but this is not allowed."
    You can actually use a numeric formula field to copy the duration value, you just need to convert the field using commands like todays(), tohours() or tominutes(). That field could then be used in Automations.

    "I can't do these operations on the data entry form because I'm maxed out on derived fields."
    Even if the duration is not showing on the form you can do a Dynamic Form Rule that when the record is saved and duration is <= 0 and  have the action of the rule put a popup box reminding the user to change the fields. Now that would only work if the users can change the field.

    "When I try to do a QuickBase action instead of automation, the option for modifying a record is greyed out no matter what I do/do not enter into the action's fields."
    In Actions  report links are used. If you create a report link field that Matches the value of Record ID# to the Record ID# of the same table then you can get the Actions to make changes on the records.

    "I tried to build a report and highlight the out-of-range values on it, but you can't highlight based on formula fields."
    if you convert the duration to a number you can choose the formula option for highlighting and make something simple like If([Total Time on Site]>4,"yellow","")  My field is already a duration converted to numeric.

    ------------------------------
    Jason Johnson
    ------------------------------