Expand all | Collapse all

Automation not firing every time due to multiple forms/tables

  • 1.  Automation not firing every time due to multiple forms/tables

    Posted 01-04-2020 01:17
    Warning: Lengthy novel ahead ..... 

    I know the reason why the automation isn't firing sometimes, but can't think of a way to fix the issue and obtain the results I need. Any thoughts, suggestions, ideas, push in the right direction, or push in any direction, is greatly appreciated.

    I have 3 tables:
    • Work Orders
    • Jobs
    • Assignments

    The relationships are as follows:
    • Work Order < Jobs
    • Job < Assignments
    When I start a new work order, the Work Order Status default is "Pending" and the Sub-status default is "Missing Information".
    There is an Add Job button that takes me to the Jobs form.
    Within the Jobs form I have the Assignments form embedded and editable.

    I have a formula text field called "Missing Information" in the Work Orders form that indicates which "required" fields in the Work Order and related Jobs/Assignments are missing data. I say "required" because I don't have them marked as required in the field properties or the forms' customization. As the different fields are populated with data, the Missing Information field is dynamically updated to only show those fields that are still lacking data.

    However, I don't allow the Work Order Status/Sub-status to be changed until all "required" fields have data in them.

    This is when the automation comes in to play.

    The automation is triggered when a work order is added/modified, and the Status is "Pending" and the Missing Information is BLANK. It changes the Work Order Status to Active and the Sub-status to Scheduled/Working.

    The issue I am running into is this:

    The Work Order has multiple fields that can be filled in at any time. If all of the Work Order fields are populated first and then a Job and Assignment are added, when the Work Order is saved the Automation is not triggered.

    The reason is that clicking on the Add Job button causes the Work Order to be saved, and all of the Work Order fields contain data.
    However, the Missing Information field is not blank yet, because it is missing a Job/Assignment (and associated "required" fields for those forms too).

    Once the Job and Assignment are added, it takes me back to the Work Order form, where I can finish by saving. But because all of the Work Order form fields were previously filled in (prior to adding a Job/Assignment), the automation doesn't see the Work Order as having had a modification since the prior save, from when the Add Job button was clicked. At least that is what I believe is causing the automation to not trigger.

    If I leave at least one field in the Work Order without data, add the needed Jobs/Assignments, then fill in the last remaining Work Order field and save, the Automation triggers and works as expected.

    How can I have the automation be triggered regardless of which form (Work Order, Job, or Assignment) was last modified? I need it to only trigger when a recorded is added/modified to any of the three tables (Work Order, Job, or Assignment) and the Work Order Status is Pending and the Work Order Missing Information is BLANK.

    I have thought about 2 possible solutions but they present their own problems:

    1. I have thought about having the Work Order Status/Sub-status fields be formula fields. However, once the Status goes Active and the Sub-status goes Scheduled/Working, I can't have them reverting back to Pending/Missing Information, even though additional Jobs/Assignments could be added after the Work Order goes Active and those could in fact be missing information. I have an exceptions report to capture those instances. Once a Work Order goes Active, it needs to remain in an Active Status.
    2. I hide one of the "required" fields in the Work Order form, via Dynamic Form rules, until there is at least one Job and one Assignment. Then the field will appear, and when the form is saved, it will have been modified, with a Status of Pending and Missing Information BLANK, thus triggering the automation.

    Other than that, I don't have a solution to my issue.

    Again, I apologize for the lengthy novel and sincerely appreciate any insight someone might be able to afford me.

    Happy New Year to everyone!!


  • 2.  RE: Automation not firing every time due to multiple forms/tables

    Posted 01-04-2020 08:53
    I suggest a 2nd duplicate Automation, but this one will trigger off a job being added or perhaps modified.  You can look up any fields form the Work Order to build your trigger.

    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach

  • 3.  RE: Automation not firing every time due to multiple forms/tables

    Posted 01-04-2020 10:51

    Fantastic idea!! Thank you so much for the recommendation.

    I find that sometimes I over complicate possible solutions trying to get it all done in a single automation.

    I guess the simple, effective, and efficient way would to do what you suggested and create a second automation.

    I will give it a try.

    Thanks again for your input, I appreciate it.