Automation not firing every time due to multiple forms/tables
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:
The relationships are as follows:
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:
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!!
------------------------------
Andrew
andrew.fry25@gmail.com
------------------------------
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
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:
- 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.
- 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!!
------------------------------
Andrew
andrew.fry25@gmail.com
------------------------------
