complex joins, portals, the limits of FMP, and potential insanity
I am setting up a more flexible means of maintaining records related to psychological treatments. I used to use a very simple, single event record which held date, type, note, client billing, and insurance billing. These events records were used for treatment records, billing, and therapist practice accounting. In it's simplicity it was quite limited to a single client with a single therapist and a single billing for client and insurance. I have been pushed by circumstances to move to a more complicated table structure.
Treatment situation is based around "events" which are usually a treatment session, but can also be phone calls, receipt of payment, invoice mailed, etc
Treatments can have multiple clients
Treatments can have multiple therapists
Clients can be in multiple treatments
Therapists can be in multiple treatements
So a join is in order, right.
And any particular "event" is going to be many instances of the join.
Here is where my question comes in.
An event has additional information associated with it.
An event can have one or more notes associated with it
A single note might be associated with all therapists and clients
addtional notes might be specific to a client
An event can have one or more fee charged, fee collected, and/or billings associated with the therapist and/or clients
1) So I set up the basic join above.
2) I added a Note_fk field to the join which usually wil usually be the same for all join records for the event,
but there might later allow an additional client-specific note through another join record
3) In my FinancialTransactions table I added a field for join_event_fk
e.g. any part of the event can then have multiple fee/billing events (client can be charged, insurance can be charge, payment can be collected, therapist pay-by-session can be issued)
This seemed straightforward in that an particular attendee would have a single join_event record and could have all the associated accounting related to it.
All seemed well in conceiving this, but when I started to set up the layout it got problematic. I think the problem may be trying to related multiple financial transaction to a join_event. It seems difficult to get the correct reference to a record using relationships, e.g. I couldnt get portals to properly display all related info. I started to think the problem was in my setup.
Is this design going to lead me down the road to insanity?
It occurred to me that maybe I should make the relationship between join_event and financial transaction like those to of client, therapist, and treatment, where the join_event holds a single fk to a single financial transaction. So more join_event records.
Any advice on what will really be workable given FMP's referential powers and layouts.