AnsweredAssumed Answered

Multiple tables to one layout strategy?

Question asked by saulr on Jul 7, 2010
Latest reply on May 10, 2013 by WalsySry


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,