Delete Related Records, what if multiple parents exist?

[Roeland De Windt]

Forgive me if this has been aswered before, but couldn't find anything that answered my question.

Table ANSWERS
Table REMARKS

In the relation definition between them, I have the ANSWERS on the left, and REMARKS on the right side, with Allow Creation and Delete Parent checked on the right side.

Now the situation is that there can be multiple ANSwers with one and the same REMark. In effect, For each of the ANSwers, the same Remark will be shown. This is desired behaviour.

If I delete 1 (out of ex. 3 ANSwers), the sole REMark is deleted as well. I was hoping to be able to define in the relation that the parent record be deleted when "all records are deleted" in stead of "a record is deleted" in the other table. I want the REMark only to be deleted if the last ANSwer linking to that child REMark is deleted.

Doable without scripting?

[comment]

Your terminology is confusing. If:

2 hours ago, Roeland De Windt said:

there can be multiple ANSwers with one and the same REMark.

then REMARKS is the parent table and ANSWERS is the child table. A child can never have more than one parent (in the same table).

 

2 hours ago, Roeland De Windt said:

I want the REMark only to be deleted if the last ANSwer linking to that child REMark is deleted.

Doable without scripting?

No.

[dwdata]

Hi Roeland,

If the goal is to keep the REMARK if other ANSWERS use it, then you will need to add scripting with logic to know when you want the CHILD deleted.

Please check out the attached file

Good luck!

sample.zip

Edited July 13, 2016 by dwdata

[Roeland De Windt]

@comment

But... ...don't know what to say honestly. This is my relationship diagram (ANSWERS cyan on the left, REMARKS green next stack middle), is it wrong then? It works perfectly for showing groups of answers (defined by QuestionPool_ID) for an employee (Emp_ID_Evaluated) with a general remark alongside it.

 

6 minutes ago, dwdata said:

If the goal is to keep the REMARK if other ANSWERS use it, then you will need to add scripting with logic to know when you want the CHILD deleted.
Please check out the attached file

@dwdata Thanks for that! Scripting to the rescue.

Edited July 13, 2016 by Roeland De Windt

[comment]

3 hours ago, Roeland De Windt said:

This is my relationship diagram (ANSWERS cyan on the left, REMARKS green next stack middle), is it wrong then? It works perfectly for showing groups of answers (defined by QuestionPool_ID) for an employee (Emp_ID_Evaluated) with a general remark alongside it.

I am afraid I don't know what your match fields represents, so I cannot comment on that. If it works for you, then it probably isn't wrong. But you said that:

6 hours ago, Roeland De Windt said:

there can be multiple ANSwers with one and the same REMark.

That implies a one-to-many relationship between REMARKS and ANSWERS (one REMARK to many ANSWERS). So I would expect to see a RemarkID foreign key field in the ANSWERS table linked to a unique RemarkID field in the REMARKS table - and a straight line instead of crow's foot at the REMARKS side in the graph.

Unless the opposite is also true, and one ANSWER can have many REMARKS.