Discussions

Expand all | Collapse all

Automation: Trigger on Summary Field Change

  • 1.  Automation: Trigger on Summary Field Change

    Posted 04-24-2020 15:18
    I want to trigger an automation in a parent table, when the number of its children changes. I am using a summary of field, basically "# of children", and I have named that field in my trigger definition "When any of the following fields change:" I can add and drop children, and the summary field (number) reflects that change, but automation does not seem to get triggered. Shouldn't this work?

    ------------------------------
    Jeffrey Pierce
    ------------------------------


  • 2.  RE: Automation: Trigger on Summary Field Change

    Posted 04-24-2020 15:48
    Sorry, no way.
    Well  not no way but not that way.  Email Notifications and Actions and webhooks and Automations will only fire when a record is edited.  They do not trigger just because of the passage of time (like at the stroke of midnight a task becomes overdue) or the # of children changes.​

    But you have an easy answer.  You can trigger when a record is added or deleted on the children table.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Hi Mark. In the past 8 months, has this changed? I need the same functionality, but my records are more complex than "number of children." I am looking also for a solution that may work for my problem.

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 4.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    No this has not changed and is likely never to change.  One solution may be to run an Automation which which records the value of a field at midnight when the Automation runs and detects if it has changed from what it used to be the previous midnight.  ie to record the before and after values of a field every midnight and record when they change and purge the values that did not change - so making an Audit trail of the changes.

    Or else you just need to find a hook as to what drives the change in your summary field.  The reason I offer up the suggestion above is that maybe your summary field is changing with the passage of time.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Hi Mark. Thank you for the quick response. Here is an overview of the problem, in detail. I have two tables, one for Purchase Orders, and one for Line Items. The Line Items are parts which are ordered. There is a checkbox on the Line Items record which denotes when the part is received. The Purchase Order record has two summary fields; one totaling Parts Outstanding, and one totaling Parts Received, derived from that checkbox. I am trying to create an automation that automatically changes the PO status to closed when Parts Outstanding =0 and Parts Received is >0. 

    My only concern with the solution you noted above is that the data may change multiple times, as parts are not received all at once, so it may not always accurately reflect the Parts Outstanding =0.

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 6.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    I think your automation would be when a "Part" is edited (in this case, checked) AND "Parts Oustanding" = 0, THEN change the PO status. So, if you had 5 parts, the automation would run when the last part has been checked off as received because at that point the parts outstanding value would be zero by the time the automation ran.

    ------------------------------
    Jordan McAlister
    ------------------------------



  • 7.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    I tried this, and it did not work.

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 8.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Did you make use of the summary fields being looked up down to the child line items in your trigger?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Yes. Still no luck.

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 10.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Can you post a screen shot of the trigger for your Automation including to "open" the section or the filter?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago


    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 12.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Can you also post the Action Step 1.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago


    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 14.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    The Action has an error. :) 
    You want to match the Record ID# of the Purchase Order to the Related Purchase Order.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    I changed it (I actually saw it after I sent the screenshot!). It still did not work, even with referencing the Record ID#

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 16.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Can you post both the current Trigger and the corrected Action in one post?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 17.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago


    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 18.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    The filter on the Action is still incorrect.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 19.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    I've modified it 4 different ways, still with no success. Thoughts on what the filter on the action should be?

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 20.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    It should be [Record ID#] on the left and Trigger: Related Purchase Order on the right.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 21.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Got it. User error on my part. I think I had too many filters when I had Record DI# on the left. It works now, quite successfully. THANK YOU!

    ------------------------------
    Matthew Witzgall
    ------------------------------



  • 22.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    OK! Glad we got there in the end.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 23.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    Well it sounds easy.  Lookup those summary fields down to the lines table and trigger the Automation off a change on the lines.

    The other approach is to have the status on the PO be a formula field based on those summary fields or a combination of a manual status field and those summary fields.

    For example, you might have  a manual status of Order Placed or Order Cancelled, but then have a calculated Status fields that would say that the Status is "Closed" if all lines have been received else, fall back to use the manual status.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 24.  RE: Automation: Trigger on Summary Field Change

    Posted 12 days ago
    I have not yet tried this option.

    ------------------------------
    Matthew Witzgall
    ------------------------------