You may want to work through the tutorial on Table Occurrences found here: Tutorial: What are Table Occurrences?
Each "box" in your screen shot is a "table occurrence".
I suggest that you make two occurrences of your Clients table as the simplest way to remove the "loop" in your relationship graph that is the source of this warning message. (If you click OK, FileMaker creates an extra occurrence for you to make the relationship possible but without any such loop. But an extra occurrence of Clients looks like the better option than letting FileMakaer create the duplicate TO here.)
Thanks Phil. I'll take a look now
Is this looking more like it (red boxes are the TOs)?
It's not what I recommended, but if it works for you...
But shouldn't that bottom right table occurrence refer to an occurrence of Sessions? you seem to have two occurrences of Sessions on either side of your join table.
What I recommended was using two different table occurrences of Clients.
You show a match from clientName to clientID. This should not be. ID's should be auto-entered serial numbers of UUIDs generated from the Get ( UUID ) function. A name should not match to an ID.
And you seem to have a join between Clients and Attendees. Can an Attendee be a client or representative of more than one client? If not, then you don't need a join table between clients and attendees.
Also, your data model is set up so that each program is unique to a particular client. Is that really the case or might a given program be presented to more than one client (but perhaps in different sessions?).
Thanks for your help.
I think I've over-thought and over-complicated this so I have taken it back to basics. I have also changed some of the terminology and naming conventions to make it more user friendly for our staff.
One more question though. As you can see from my Tables and Relationships, each Service_User should only ever have one ReferralForm. Depending on the Service_User type, a user may never have a ReferralForm.
How can I restrict a Service_User from having more than one ReferralForm? Is that something that can be created through relationships or is it something I would need to script into the user interfaces to check to see if a ServiceUser already has a ReferralForm?
I think that your question actually is either:
An organization can never have more than one referral form--in which case you can set up a text field in Service_Users with this auto-enter calculation: _OrganiazationIDfk & "|" & _ReferralFormIDfk and then set a Unique values validation on this text field.
An organization can never have more than one referral form for a given Services record--in which case you can set up a text field in Service_Users with this auto-enter calculation: _OrganiazationIDfk & "|" & _ReferralFormIDfk & "|"_ServiceIDfk.
There are other, interface level methods that can be used to make it impossible to select the same ID combination more than once as well.
No. The Organisation table can have multiple Service Users. The Referral Form relates to the service user and not the Organisation.
So I'm trying to figure out how I can create a Service User, then, at a later date, complete a Referral Form that is unique to that user. A Service User would only ever have a maximum of one related Referral Form.
The simplest then would be to get rid of the seperate referral form table and just move those fields into the Service_User table.
If you choose not to do that, you can set up a layout based on Service user and include the fields from the Referral Form layout on this layout just as though the fields are part of the Service User table.
Thanks. I appreciate the advice. Merging the two tables sounds like the best way forward. I can then separate the contact data from the referral form data by creating separate forms for each "section"