If formulas were available within filters, the following automation would be very easy to get working, but in reality, I’m stumped.
Is there an way to use the results of step 1 in any other way than a formula?
I want to delete this point log if the user it’s linked to is the same user who was just unliked from the task.
Relationships:
Point Log (M to 1) User
Point Log (M to 1) Task
Task (M to M) Assignee
Currently I think you would need to use a script to do this.
I figured it out using a formula!
It involves creating a checkbox on the Point Log itself that basically had the logic I wish I could put in the automation, but it’s a fine workaround for now.
“Delete” Formula Field on Point Log:
If(
MatchRegex(Task.Assignees.Join(Name, ", "), User.Name),
false,
true
)
The Automation:
You need to be cautious when combining database formula fields and automations to achieve a specific goal because they are two independent services, and the relative timing of changes cannot be guaranteed.
For example, the linked Assignees change to trigger the automation. The filter in the automation’s delete action checks the value of the formula field.
In parallel, the Assignee change causes the formula field to be updated, but it is not guaranteed that this update will have completed before the filter check occurs. If the formula service is busy updating other entities, it is possible that the automation will not perform as intended.
Good point @Chr1sG, is there another approach you would recommend I take here?
I’m not really a developer so using scripts is a little out of my league.
Possibly. But let me check some things first.
You wrote…
Is this correctly understood as: if an Assignee is unlinked from a Task, then delete all of the Point Logs belonging to the Task that also belong to the user who is the unlinked Assignee
?
Yep, you nailed it!
In that case I would suggest the following:
add a formula field to the Point Log db as follows:
define an automation in the Point Log db that runs when this field becomes true and deletes the Point Log:
In case it’s not clear what is going on, here’s the explanation:
the formula takes all the assignees for the Task and selects only the one which matches the User for the Point Log. It will return true if there isn’t a match - this will be true if the list of assignees no longer contains the User for this Point Log. In such an event the automation will cause the Point Log to delete itself.
Strictly speaking, it is not an exact match for what you asked for, since you wanted a trigger when the unlinking occurs, but the solution above will cause the Point Log to be deleted if there is any time in its lifecycle when the User is not amongst the list of Assignees (e.g. if a Point Log is created for a Task and the User is not an Assignee at that time) but I hope this distinction is not important.
