Automation to add values to multi-select field
I have an Organizations Table and a Category Table. They have a many-to-many relationship through a Join Table.
I want to be able to filter Organization records in the Organizations table using Category Table fields. Each Organization record may have multiple Categories (and vice versa - hence the need for a many-to-many relationship).
But because it is a many-to-many relationship, the Organization is a Master table and thus cannot lookup the needed fields from the Join Table.
As a solution, I assumed I could create a multi-select field in the Organizations table, and use an automation in the Join Table to update the multi-select field in the Organizations table with an aggregated mirror of records in the Join Table).
But I see that the automation merely replaces the multi-select field value; it cannot add a new value, and thus multiple values cannot be recorded in the multi-select field.
Is there a way around this?
(or is there a better solution to my need for filtering records in the Organizations table, using data from the Join Table?)
I want to be able to filter Organization records in the Organizations table using Category Table fields. Each Organization record may have multiple Categories (and vice versa - hence the need for a many-to-many relationship).
But because it is a many-to-many relationship, the Organization is a Master table and thus cannot lookup the needed fields from the Join Table.
As a solution, I assumed I could create a multi-select field in the Organizations table, and use an automation in the Join Table to update the multi-select field in the Organizations table with an aggregated mirror of records in the Join Table).
But I see that the automation merely replaces the multi-select field value; it cannot add a new value, and thus multiple values cannot be recorded in the multi-select field.
Is there a way around this?
(or is there a better solution to my need for filtering records in the Organizations table, using data from the Join Table?)
