Find with self-join

[Alice Aitch]

I have two tables: 

• Workers 
• Clients

Workers work for multiple Clients/Clients have multiple Workers.

I have a join table that contains a record of each Worker/Client relationship.

Each Worker/Client relationship is managed by a Supervisor. Multiple Supervisors can manage the same Worker but with different Clients. The record in the join table includes the name of the Supervisor.

Initially, I was asked for a report to show which Workers are managed by which Supervisor, and I followed a Filemaker help article to create a self-join search so I find only the first instance of a Supervisor managing a Worker.

Now I’ve been asked for a report on each Supervisor showing the Workers they manage as well as the names of all the other Supervisors for that worker, and I’m not sure how to proceed.

I struggle with relationships and joins, so perhaps I don't have this set up correctly to begin with. Can anyone give my suggestions? Thanks!

[comment]

This is not simple. Just thinking aloud: you could define a calculation field in the Workers table that lists all supervisors of a worker. Then you could have another calculation field in the join table that removes the current supervisor from that list. 

Are supervisors listed in the Workers table, or do they have a table of their own?

--
P.S. Please update your profile to show your version and OS.

 

Edited April 2, 2019 by comment

[Alice Aitch]

Thank you very much for the List suggestion - I'll experiment with that. I wasn't able to spend any time on it today but hope to be able to tomorrow.

The supervisors do not have their own table but have been added to the join table in a text field only through a value list. I've been trying to upgrade and combine some 20-year-old flat file databases, and I think I could fairly easily integrate a supervisors table here.  Do you think that would give me additional options?

 

Thanks,

Alice

[comment]

21 hours ago, Alice Aitch said:

The supervisors do not have their own table but have been added to the join table in a text field only through a value list.

That's not a good idea. They should be either in their own table or in the Workers table, flagged as supervisors.

Have a look at the attached file that shows one possible way to look at this. 

 

OtherSupervisors.fmp12

[Charity]

I can use this. I have almost identical problem. Tanks for posting it Comment.