3 way join table first record joins all 3 tables, following records only per-to-per

[aman.petrie]

Hi,

 

I am new here and quit new in FM world but have a silly question regarding join tables for more than two tables. I have created three table join

Table 1:

id1

text

 

Table 2:

id2

text

 

Table 3:

id3

text

 

Join table:

id

id1

id2

id2

 

I built a Layout for first table having two portals on it, showing records from table 2 and table 3. The relation is through a join table.

 

Creating a first portal record in both portals creates only one record in join table that refers to both tables (2 and 3), a three way join. For all following portal records FM creates separate join records between table 1 and 2 as well as table 1 and 3.

 

Does anyone know why it is so and how to overcome it?

 

Thank you in advance

 

 

 

 

[Vaughan]

Please use REAL examples, not table A, B and C. We're not Dr Seuss.

 

Describe your REAL situation, there is likely somebody here that has already developed a solution for it.

[aman.petrie]

Thank you Vaughan,

 

I attached a concrete example where I have four related table through a Join table: Meetings, Tasks, Notes, People.

 

After creating a Meeting I create through portals first Task, first Note, first People in the Meeting. In the join table all the relations to those tables are in one join table record. Creating a second Task and Note generates a separate (a per-to-per relation between Meeting and Task/ Note) record.

Creating a third Note creates also a new record in join table (a per-to-per relation between Meeting and Notes table) and so on. So the first record is like a multi way relation where all the rest of the relations are all in separate records as one-to-one.

 

The problem appears when I delete one of the first portal records. It deletes the first record from the join table and all the other relations based on this first record in the join table between the Meeting table and other tables. (the checkbox of delete related records in relationship graph is checked)

 

My question is whether I do something wrong or is it a bug in Filemaker. I know I can handle it with scripts if I don't delete related records automatically.

 

Thank you very much for your help if someone can help me.

 

Best regards

 

 

 

TESST.fmp12.zip

[bruceR]

If FileMaker is doing what your design tells it to do, why would you then claim that FileMaker has a bug, instead of asking how to correct your design problem?

[bruceR]

I would think your design should be more like this.

TESSTMOD.zip

[aman.petrie]

Thank you very much BruceR for the example.

 

It gave me new insight how to design in Filemaker. Nevertheless, is solves half of my problem. It works fine as long as I always have a meeting record. If I create a Task from Tasks layout, it won't and shouldn't always have a meeting. Now I cannot assign it to someone. Choosing from Peoples Layout a person I would like to see all his/her tasks (created through meetings and separatelly). Now, if the meeting exist it works and if not I won't see those Tasks from Peoples layout.

 

Therefore I need a second join table between tasks and people for assigning tasks to someone and to see all tasks assigned to someone form Peoples layout. From that need I got my own initial idea of adding tasks relations also into the same join table (currently the table Meeting_People). This solution didn't work as I expected. Making a new Task from Task layout and assigning it to someone, filemaker looked for a first record that referred to that person and saved the relation into that record (that was initially a record for stating who took part of some meeting). Assigning next tasks to someone from Tasks layout were joined through a new record in Meeting_People table, so as I thought could have happened for the first one as well.

 

Now I know that I have to use not more that two way joins in Filemaker, do you think my understanding is relevant?

 

I attached my new solution here (relation for adding assignees to tasks), do you think my approach is now more Filemaker way?

 

Thank you in advance

 

Aman

TESSTMOD2.fmp12.zip

[bruceR]

I'm not sure that FileMaker has much to do with the question here.

 

Vaughan's question still applies. What are you trying to do?

 

The question is about what kind of entities you are creating and what kinds of relationships and rules apply to the connections.

 

What makes you say this?

 

"Therefore I need a second join table between tasks and people for assigning tasks to someone and to see all tasks assigned to someone form Peoples layout."

 

If a task is linked to just one person, you could just create a field fkAssignedTo in the Task table.

 

That said, a join table will work for a single connection; and it also allows for the possibility of linking more than one person to a task.

 

Why isn't the task-people portal displayed on the Task layout?

[aman.petrie]

Thank you BruceR for your comments,

 

I needed a second join for seeing from peoples layout who has which tasks.

 

And I also added the needed portals to peoples layout and to tasks layout (attached). Now it works in principle as I wanted it to have.

 

I'm trying to do a solution for managing my meetings, projects, tasks, notes and people as well as their skills. Having all this in one DB allows me to see which people has which tasks and what was said in the meetings, who participated and what tasks were assigned to who. Now I guess I have enough insight to start with and I have to thank you very much for your help Bruce, sharing your thoughts with me.

 

Best regards

 

Aman

TESSTMOD3.fmp12.zip

[bruceR]

You're welcome. The addition of the portal to the task layout works better.

 

I see that you have the task_people::fkPersonID field displayed twice, once as a standard field and once as a drop down menu.

 

As previously mentioned, you can eliminate the join table if you only care about linking one person to a task.

 

If you keep the task_people join table, you could add additional fields such as, for instance, role. 

TESSTMOD4.fmp12.zip