Multiple tables to one layout strategy?
I have some familiarity with Filemaker, but my purposes are pretty limited, so, as always, forgive the potential naive question.
I am trying to put together a client database for a psychotherapy practice. Included is a table for professionals (physicians, psychologists, social workers, etc.). A professional can have one or more roles in a client's life. The professional might
1: Refer the client to me
2: Serve as the client's primary care provider
3: Serve some other role (e.g., neurologist, coach, etc.)
Professional to client is a many to many relationship, and I have set up three join tables corresponding to the above possibilities. This has required three copies of the Professional table.
On the client side, this works. I have fields that will show the referrant and PCP and a portal that shows other professionals.
The problem is on the Professional side. I want a single layout to show me the clients that relate to the professional. So, a single professional might refer 5 clients, serve as the PCP for 3 others, etc. I prefer to show the clients in separate portals on the same layout. However, a layout pulls data from a single table and FM is treating the multiple copies sort of as separate tables. So, if I use the PCP table for a layout, it seems to have no way to show related files through the Referant join.
I realize I can probably create another table that combines all related clients, labels the relationship and then show them on a single portal. That seems inelegant to me, somehow. Any thoughts would be greatly appreciated.
And just for fun extra credit, I really want to get this to work on FMTouch, so I can justify an iPad...
Thanks very much,