Forum Discussion
MarkShnier__You
Qrew Legend
Paul,
You should set up an Automation which Trigger off some change in the Parent Record. The Action will be to update the child records where their [Related Parent] field is equal to the [Record ID#] of the Trigger Record and set a date time field on their records to the current date time.
I have not tested that lately as to if that will fire slowly enough to trigger individual record changes to fire separate Notifications.
If they do not fire slowly enough to fire individual record change notifications, then the solution will be to daisy chain one Automation to trigger a send notification. So the first one will update a date time field #1, and then a separate Automation will trigger on date time field #1 and in turn the Action will be to update datetime field #2 which will trigger the email notification.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
You should set up an Automation which Trigger off some change in the Parent Record. The Action will be to update the child records where their [Related Parent] field is equal to the [Record ID#] of the Trigger Record and set a date time field on their records to the current date time.
I have not tested that lately as to if that will fire slowly enough to trigger individual record changes to fire separate Notifications.
If they do not fire slowly enough to fire individual record change notifications, then the solution will be to daisy chain one Automation to trigger a send notification. So the first one will update a date time field #1, and then a separate Automation will trigger on date time field #1 and in turn the Action will be to update datetime field #2 which will trigger the email notification.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
PaulStreit
4 years agoQrew Member
Mark,
I set up both an automation and an action as you described. Both have the same issue - I somehow have to trigger an email notification when a formula field checkbox is true.
Is there a way to detect the change in the formula field, then update a trigger field and then save the record which would trigger the automation?
I included some formula URL code in my original post but it didn’t work, not clear why.
Thanks
Paul
I set up both an automation and an action as you described. Both have the same issue - I somehow have to trigger an email notification when a formula field checkbox is true.
Is there a way to detect the change in the formula field, then update a trigger field and then save the record which would trigger the automation?
I included some formula URL code in my original post but it didn’t work, not clear why.
Thanks
Paul
- MarkShnier__You4 years agoQrew LegendA notification will Trigger when formula fields change, as long as this happens when the record is being edited manually or by API.
Are the formula fields changing when a record is edited? Or through the passage of time or due to the changes in either lookup or summary fields? The latter one will not Trigger a notification.
Can you explain what tables you were actually dealing with and what you were trying to do. Is it something like when all conditions are true on a project to notify the child team members?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- PaulStreit4 years agoQrew MemberMark,
The formula field on the parent record is changing through the passage of time and due to changes in summary fields so it will not trigger a notification directly.
The parent table is a table of proposal records. The child table stores a list of approvers, one approver per record. Some approvers must approve before other approvers, so there is a sequence priority field in the approver table that stores the sequence priority which is simply an integer like 1 for the first set of approvers, 2 for the second, etc. There is a a formula checkbox field in the parent proposals table that is set to true when all of the first set of approvers have responded and approved. When the formula checkbox field toggles to true I want to update a trigger field in the parent table proposal approval record that triggers an automation to update a similar trigger field in the child approver records filtered on the sequence priority 2 field which in turn would trigger email notifications to each second sequence priority approver.
So I'm thinking that the way to do this is to have a formula URL field that checks if the formula-checkbox field is true, and if so, places the proposal record into edit mode, updates a trigger text field in the proposal record with the current date/time converted to text, then saves the record by redirecting back to display the proposal table default home screen report. The update of the trigger field would then fire an automation or action that would update a text trigger field with the current date/time converted to text in all sequence priority 2 child approver table records.
I'm assuming that when the automation updates each child approver record that this effectively edits and saves the record which would trigger the email notification. Is this a correct assumption?
I wrote code for the parent proposal record formula-URL field. The [Logic Checks Complete] field is the formula checkbox field that indicates when all sequence priority 1 approvers have approved and that sequence priority 2 approver email notifications can be sent out. Field ID 210 is the proposal record text trigger field that would be updated with the current date/time by the formula-URL field. It doesn't work.
I'm guessing that maybe assuming a formula URL field can execute automatically without a user manually clicking it as a button is the issue, but I don't really know. If that's wrong, is there another way to automatically update the trigger field based on the state of the formula checkbox field?
The formula URL field code is shown below:
//Check to see if sequence priority 1 approvals are complete
IF([Logic Checks Complete]=0, "",
//Edit the current proposal record
URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
//Set the automation trigger field (a text field) to the current date/time
& "&_fld_210=" & ToText(Now())
//Redisplay the current proposal record to automatically save the change which should trigger the automation
& "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#])
)
I suspect that many QB app developers encounter this situation where they want to trigger an email notification, action, or automation based on a calculation instead of a record add/delete/modify event, so how to address this would likely be useful to the entire community.
Thanks,
Paul
------------------------------
Paul Streit
------------------------------- MarkShnier__You4 years agoQrew LegendOK, I finally understand the question.
The references to APIs are a red herring.This kind of APIs are used for a user to click to cause an edit. Nothing to do with your use case.
Also, there is in fact a user causing a record to be edited to trigger a process, so these summary fields are not in fact changing due to the passage of time. They are changing because a User approved a record and hence edited a record.
So we do in fact have a clear trigger event. The event is an Approval Level 1 record being approved.
Notifications to the Approval Level 2 recipients can be done using an Automation.
We need to have a summary field of the [# of Level 2 Approvers already notified]. That will be the summary count of the # of Level 2 approvals where the [Level 2 Approver date/time Notified] is still blank. This date/time field is the one that will be updated by the Automation to trigger the Notification.
Let's make sure that we have a formula date/time field called [Current Date/time] with a formula of Now() on the Approval records. Also we will need to have the lookup of that parent field for [Ready to Notify Level 2 Approvers].
The Automation will Trigger when a Level 1 Approval is done, subject to the filter that [Ready to Notify Level 2 Approvers] is checked and [# of Level 2 Approvers already notified] =0. Presumably, this would then trigger when the last of the approval level one approvals was done.
The Action once triggered will be to Modify Records in the Approvals table , subject to the filter that they have the same [Related Proposal] as the trigger record.
I believe that the modify records action will be done slow enough that it will be seen as a single record notification update for Notifications. If you get this working but the Notification only fires a multi record change notification which would not be suitable for your needs, let me know and we will just daisy chain one automation to call the next and that will slow it down enough to make individual record change notifications.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------