Assign a task to more than 1 coordinator

[Smiley]

Hi

i have an event table and a coordinator table. Until now each event had only one coordinator. There has now become a need that at some large events require more coordinators - could be upto 5.

I have a login for the coordinators where they could see events which are assigned to them.

The relationship is that the cordinatorID in the coordinator table matches the coordinatorIDF in the event table.

I created a coordinator2IDF in the event table and tried to relate it aswell to the coordinator table but what happens that it even breaks the first relationship.

How could i modify my table it should work like that with my already created table which was until now a one to one relationship?

i would appreciate if someone could help me out on this...

[comment]

The proper way to resolve a many-to-many relationship (which is what you have here) is through a join table.

Filemaker allows you to cut some corners and establish a many-to-many relationship even without adding a third table, by using a multi-key field as the matchfield on one or both sides of the relationship. In your case, that could be a checkbox field in the Events table, using a value list of CoordinatorID values.

There are some prices to pay for this convenience: most importantly, you cannot have any attributes attached to the relationship (e.g. the date of appointment or the role of the coordinator vis-à-vis the project). Reporting on projects by coordinator or coordinators by project is also difficult.

[Smiley]

thanks for your help , the checkbox version works great but does not look that great as i have a list of like 100 coordinators , anyway i could achieve the same result in having to select them through dropdown lists or some other easy method?

without having to create a join table which doesn`t work me out .

[comment]

You can select them any way you like, as long as you end up with a return-separated list of the selected CoordinatorID values (which is what a checkbox field contains). You can even use a drop-down list, if you train your users to hold control/command+shift (IIRC) while selecting. Although I suspect your users will thank you if you provide them with a better selection mechanism - e.g. a portal in a popover.

Edited November 8, 2016 by comment