Hi, I found that automation triggers and formula fields are not “synchronized” to have a consistent state, in other words here is the sequence:
I create an automation rule that is triggered by an entity create event.
I create a new entity that has formula fields.
The automation trigger is fired immediately after creation but before formula fields are calculated, so those fields, when accessed by a script action, are still null.
That implies two issues:
-) if the automation trigger filter is on a formula field => the automation is not run since those fields are still null;
-) the action script fails finding those fields as undefined.
I was thinking of adding a “sleep” at the beginning of the action script, but setTimeout() seems not to be supported in scripting, and anyway field values should be re-acquired, since arg.CurrentEntities has null values.
It can be a good idea, but are they filled “transactionally” so that I can use any formula field as the trigger? If the fields are updated “randomly” I can’t pick one (or more of them) as event trigger.
You can choose all of them in the update section of the trigger, and then add a set of filters which checks that they are all filled.
Then it doesn’t matter the order in which they are calculated, the filter criteria will be met only when the last update has occurred.
I made some tests and unfortunately that workaround solves the problems of having formula fields filled but causes multiple triggered actions.
Here is the sequence that happens:
Define an automation rule with trigger on update on 2 formula fields and some non-formula fields, filter is the 2 fields not being empty.
create a new entity
the “system” creates the entity and trigger is fired (1) then the formula fields are populated so the triggers is fired 2 other times (2, 3)
then (that’s the point) the system evaluates the filters of the rules and both formula fields are populated, so the rule is executed twice, which creates data inconsistency.
A workaround might be to create a field “myModificationDate” to be set by the rule action with now() value and add a filter in the rule that if the modification date (the builtin field) is within +/- one second of myModificationDate, then the rule is skipped… but then this workaround becomes a bit clumsy…
I’m not sure I get all the details of your use case, but it might be easiest to solve by just adding a formula field that returns true when all the fields that need to be populated are not empty.
Then you can trigger on this field being updated to true.
Thanks, good idea, that works to replace the entity create event in presence of formula fields, but for entity update event… that this additional “is everything updated?” field would have a tough time to understand when the formula fields have their “final” values (it should replicate all formulas to check that).
I think that it would be useful to have formula fields being calculated transactionally so that all are updated “at the same time” or having a “sleep” function available.
As I said, I’m not sure I get all your details.
Perhaps you want to explain the fields involved and the sequence of events that happen (making no assumptions about when formulas might get calculated) and highlight when you want things to happen (and when you don’t want things to happen), e.g.
Task has editable fields
Date
Assignees
and formula field
Next week (calculates if Date is between 0 and 7 days in the future)
Sequence:
Task is created (with Date field set at time of creation
Assignees are manually added subsequently
Automation should trigger if Next week is calculated and Assignees is not empty
ok, that case works being a creation trigger with a simple formula, instead for example
-) you have a formula field that takes a value only on a special condition of the entity, so it may be filled in or it may be empty if the special conditions are not met. In these cases you cannot just check if the formula field is not empty to understand if the formula ran or not.
-) even more difficult on update event: how can you check that a formula field has its updated value (what I called final value)? It may have the same value as before since the formula has calculated the same output with different inputs (we are talking about entity update event) or it may have the same value because the formula calculation is still in progress. That’s the typical reason behind transactional behavior in DB.
-) when the trigger is on update of non formula fields and formula fields, the rule action may be triggered multiple times (I made some tests and got that) since the timing of update of formula and non formula fields are not synchronized and the evaluation of the filter happens after evaluation of the triggers. I think triggering on update of a single “is everything updated?” formula field would not solve since that field would be a sequence of OR conditions so it suffers from what described before.
I am moving to trying not to use formula fields and get values formulas in the action script.
Maybe in the future a transactional update of the entity will natively simplify that
Can you give a concrete example, with the fields you have and what you want the automation to do.
It’s possible you can solve it with formulas in the automation actions (not necessarily scripting).
Thanks a lot for your time but I am building an integration between Fibery and ToDoist and scripting is anyway necessary so I am moving in that direction.
cheers
Hi @ESGdataBoi, it’s a work in progress, since some aspects must be managed, for example:
reminders are separate objects in ToDoist, so update to a reminder does not trigger the task update webhook;
loops of updates must be avoided (Fibery updates ToDoist that updated FIbery) and detection of whether the update comes from ToDoist App or from Fibery calling ToDoist API is not possible natively, unless using different users in a Team but then the project hierarchy is no more supported…
and so on.
This integration is linked to a complex task management app (DB + automations) I made in Fibery, when it will be working, I’ll considering sharing.
cheers
@Giuse im not a coder/programmer myself, but perhaps taking a look at 2sync.com to see how they accomplish 2-way sync between notion databases and Todoist could help? I know Notion API and Fibery api won’t be apples to apples, but could potentially provide some inspiration?
thanks, the “coding” part is not that difficult, I am analyzing the capabilities of both APIs, in particulare ToDoist sync API is rich but surely less powerful than Fibery’s one, so multiple methods for syncing are needed.
