Hello everyone,
In short, I am trying to use an automation to combine multiple single select linked fields into a single multi select linked record.
I have two bases: projects and team. Depending on the complexity and size of the project, there may be 5-15 resources assigned. We assign these resources through their own single select linked field so that we can utilize the timeline/utilization function. This makes doing things for the entire team complicated - such as creating a distribution list for the team members - as we have to create a lookup field for each resource's email, another field to combine them, and another field to format it.
I thought a simple automation could address this issue. It is pretty simple: when one of the 15 single select linked records is updated, it updates the multi select "team" field with all the assigned resources. I am running into two issues: 1)If I don't put a separator, then the multi select merges them all together (Instead of the field having individual links like [Resource One] [Resource Two] [Resource Three} it updates the field to be [Resource OneResource TwoResource Three]. 2) If I add a separator, like a comma, it becomes an issue if there are multiple blank fields. I assume this is because it is trying to update with [Resource One],,,[Resource Four],,,,[Resource Nine] and gives me the error "Field "XXXX" cannot accept the provided value: Could not find matching rows for string."
This seems like such an easy automation, and I am just missing something on the formatting, but I just cannot figure it out and am hoping someone here can help: