How to create a collection from a property of another collection in a rule?

Get Help
1

Hi. I searched here but didn’t find any clue to do that.

  • I have three tables: contact, organism, task
  • Contact has a relation to a one or several organisms
  • Task has relations to one or several contacts and organisms

When updating Contacts in a Task, I would like to automatically update the linked Organisms.

Is it possible?
Thanks.

2

It might be easiest if you provided a screen grab of the map for the space

3

Is it right that you want any/all Organisms belonging to a Contact to be added to the existing list of Organisms connected to the Task, when the Contact is added to the Task?
What should happen if a Contact is disconnected from a Task?
What should happen if an Organism is disconnected from a Contact while the Contact is linked to a Task?

4

Hi Chris. Sorry, I missed your answers.

Is it right that you want any/all Organisms belonging to a Contact to be added to the existing list of Organisms connected to the Task, when the Contact is added to the Task?

Correct

What should happen if a Contact is disconnected from a Task?

The organisms linked to the task should be updated. In fact, I wanted to “recalculate” the Organisms field each time the Contacts field of a task is updated.

What should happen if an Organism is disconnected from a Contact while the Contact is linked to a Task?

The way I designed the application, that cannot happen.

5

Based on what you have written, I guess your space relations look like this:
image

In which case, it sounds like you could do the following:

  • use a lookup field in Task to get the Organisms from linked Contacts
  • create a new relation between Task db and Organism db (one-to-many) called something like ‘All Organisms’
  • define an automation that will update All Organisms with the union of the original relationship and the lookup

If you need help with any of these, let us know.

1 Like
6

How could I not think about Lookup!!!
Sorry Chris. It was a stupid question. Thanks a lot.

By the way, maybe another stupid question: How do you merge (the union you talk about) two collections of the same type (e.g. two collections of organisms)?

7

I’ve just investigated, and I think my original suggestion needs modifying, sorry.

  • you will actually need a lookup field in Organisms to find the Task from the Contact (called say ‘Contact Task’)
  • you will probably need a many-to-many relation between Tasks and Organisms called something like ‘All Organisms’ (because actually an organism could theoretically be linked to one Task directly and then linked indirectly via a Contact to a different Task)

Then based on your original request, you need two automations (to run when Contacts are linked/unlinked to/from the Task).

image
image1077×760 28 KB

image
image1074×747 27.7 KB

Where the update formula is something like this:

Organisms.Filter((Task = [Step 1 Task]) or ([Contact Task] = [Step 1 Task]))

What this does is query all Organisms to find those that are either directly related to the Task or indirectly related (via Contacts).

FYI, you may also wish to have two additional automations to run when Organisms are linked/unlinked to/from a Task (doing the same action).

image
image1022×90 2.65 KB

image
image1000×72 2.69 KB

2 Likes
8

Wow! Thanks a lot for your investigation and the long and complete answer.

A “union” function would be way easier but, meanwhile, that works.

1 Like
9

Indeed.

It is on our radar.

1 Like