That's not a good description, because the described setup gives no reason for any "conflict with the key id".
Let's start with a very basic question: how many teachers can one student have?
THese are one to one mentoring relationships. Occasionally a student changes his/her mentor. But a student has only one teacher (spiritual director) at a time.
Occasionally a student changes his/her mentor.
Well, then you need to decide if you need to track such history, or keep only the current setup.
In any case, any actual meetings that have taken place will preserve the identity of the meeting parties - so if you want to ignore a relationship that hasn't been "consumated", you may safely do so.
Message was edited by: Michael Horak
Table 1 = Clients
Table 2 = Teachers
Table 3 = Join for client-teachers (assuming they have more than one teacher)
Table 4 or field in client table = Mentor
Table 5 = Mentor contacts with client to get a history
From a layout in the client table you should be able to display their mentor and all their teachers and their mentor contact-history
From the Teachers table you you should be able to display clients being Mentored and all their clients. You should also be able to show their client-mentor contact history.
I am not sure what the problem is here... Where are you getting stuck?
I don't know about this, Lyndsay. You sound very decisive, but so far I have seen nothing that would justify more than the two tables mentioned in the original post, i.e. Clients and Meetings.
The good News! I have recreated the data entry form, the reports for the related teacher-mentor Activity table and all is working OK. Previously when I created the reports, I was not able to display the names of the teacher and the student.
The puzzle: I don’t know what changed. I recreated this in a duplicate of the live database for practical reasons and safety. This duplicate is standalone and not operating on the network. The only thing different is that I duplicated the client table. However, I made no reference to it in the report or the data entry form. So I am completely baffled. The real test, will be tomorrow when I am at the center and apply these changes to the live database.
And Lyndsy, I considered a separate table for the teachers but it went against all I have ever been taught about databases. There was no unique information I needed to track for teachers and would have created unnecessary duplication and clumsy scripts. The teachers and other clients receive newsletters and information from the center. Therefore, I wanted to keep all “people” in the client database.
Thanks to both of you for your willing support.
Now I need to learn about this “portal thing” in FM. Conceptually I understand it, but creating them has proven problematic. One step at a time. At 72 one can still learn.
ALLELUIA! Updated live database and all worked well AFTER I corrected the Activity Table to use the Duplicate CLient list to reference the teachers. ALLELUIA! Case solved. QUESTION: Can I remove names from duplicate table so that in effect it becomes a Teacher Table or are these tables not independent of each other?
Good... glad you have what you want.
I agree that a "People" table is more appropriate than separating categories of people under most circumstances. There is a case, particularly in more complex systems, for separating teachers from their students.
As you will appreciate... it can be a bit hard to determine the complexity of database or the depth of developer exerience when a question is asked.
If you think about a relationship... where this from A equals this from B... a Portal is a window that can look from A into B to see the records that match because of that relationship.
It is cool that you are now learning FMP at 72. I hope you get to say one day that you have been using it as long as I have now.