Accessing relation on Filter() result in formula

Hi there,

I’m trying to make use of the recently overhauled notification feature. One use case I have is to notify interested (“subscribed”) users when a new Entity of a certain type has been created.

I was thinking to add a “watch group” entity, that users could subscribe to (“add the watch group to a relation field on their user profile”) and was thinking about an automation rule (on create) that notifies the following users collection:

[Watch Groups].Filter([Technical Name] = 'my_entity').Sort([Creation Date], false).First().Users

However, I get the error message “cannot access collection field here”. Is it not possible to access relations on a filter result?

Incidentally, the other way round isn’t working, too:

Users.Filter([Watch Groups].Filter([Technischer Name] = 'wissensspeicher_plugin_create').Count() > 0)

This results in the error message: Ouch, we can’t calculate Count, Sum, Join, Avg, Min, Max, First or Last inside a Filter. Please create two Formulas instead.

Is there any way to achieve the result I want to achieve?


Can I clarify how you want it to work:
You have an entity (in what database?) called ‘Watch group’. The database it is in links to Users (via a many:many relation?) so that User can choose to link themselves to ‘Watch group’ (and potentially other groups?).
In an automation (triggered when an entity of type ? is created) you want to notify all Users who are linked to ‘Watch group’ entity.

Did I get it right, and can you fill in some of the answers to make sure we’re basically on the same page.

Hi Chris,

thanks for your reply. Please excuse my misleading wording, I haven’t really internalized all the correct Fibery vocabulary yet :slight_smile:

I have a database called Watch Group, which contains entities describing some random topic somewhere in my Fibery. For example, one Watch Group entity might be called “New Guidebook entry” or “New Tool Evaluation”. For ease of use, they get a “technical name” that is just a non-changing string identifier of that entity, e.g. “guidebook_create” or “tooleval_create”. A user might then say “I am interested in new tool evaluations and want to be notified whenever someone posts some new entity in the database ‘Tool Evaluation’.” and link the entity “tooleval_create” to his/her user account via a many-to-many relationship.

In the database “Tool Evaluation” I would like to setup an automation rule that triggers on creation of new entities, and choose “Notify Users” as its action. The users to notify should be determined via a formula like this:

[Watch Groups].Filter([Technical Name] = 'tooleval_create').Sort([Creation Date], false).First().Users

so that every user, who added “New Tool Evaluation” as a Watch Group to their account will get a notification.

Does that example make it somewhat clearer what I want to achieve?

Thanks for the explanation.

In principle, the second method is logically the right thing to do, that is to say you would want to query the User database to find the users who are linked to the Watch Group with the technical name ‘tooleval_create’, i.e. Users.Filter([Watch Groups].Filter([Technischer Name] = 'tooleval_create').Count() > 0)
However, as you are discovering, there is currently no way to achieve it. The problem boils down to automation formulas not supporting a query within a query.
(you are needing to filter query the Users database based on a filter query of the Watch Groups linked to them)

At the moment, the only workaround I can think of is to utilise a ‘scratch pad’ field in your Tool Evaluation database, to act as a holder for the result of one query, and then use a formula applied to this.

In other words, create a many-to-one relation betweeen Tool Evaluation db and Watch Group db (call it say ‘Scratch’). In your automation, create a first action step to populate this field with the value of

[Watch Groups].Filter([Technical Name] = 'tooleval_create').Sort().First()

and then a second action step to notify users based on the formula

[Step 1 Tool Evaluation].Scratch.Users

As a precaution, in case you want to use the same technique with other automations, you might want to clear the scratch pad field as the last step.

1 Like

Are you sure you want .Sort().First() ?

The other approach, if you’re comfortable using Javascript and the Fibery API, would be a script to take advantage of the more complex filtering capabilities of Fibery’s API / graphQL.

1 Like

Well, I’m assuming that the ‘scratch’ field only ever needs to contain one item (at a time) hence a many-to-one relation and the need for Sort().First().
There are definitely other variations on the same idea though.

And yes, if you’re comfortable with scripting/API there may be ‘nicer’ ways of doing it.

1 Like