AnsweredAssumed Answered

Multiple tables to one layout strategy?

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

Title

Multiple tables to one layout strategy?

Post

Greetings,

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,

Saul

Outcomes