In general, setting up cascade deletions for my collections and junction tables work very well. But I have encountered a very specific problem.
I have a collection called ‘questions’ and it is possible for these questions to have sub questions. So I have set up an O2M field called sub_questions and a corresponding M2O field called is_sub_to. Since questions and sub questions have exactly the same fields they are in the same collection, so both these fields show up in the same collection model.
When editing the Relational Triggers for both these fields, where it says ‘On Delete of questions…’ I cannot edit this. It is locked to ‘Prevent the deletion’. I haven’t encountered this on any other collections or fields. My only explanation is that the connected items are from the same collection, but it still does not explain why I cannot set up a cascade.
On the O2M field (sub_questions) I have ‘On Deselect of questions…’ and that one is possible to set to ‘Delete the questions item (cascade)’. But in this case, I do not use deselection.
The questions are part of a parent collection called ‘assessments’ where it is set up to delete all assessment questions when the assessment is deleted. But it doesn’t work if the question has a sub question (foreign key constraint error). So those questions will be left orphaned.
Is there any way to get around this problem, apart from a lot of manual scripting?
I am still on version 11 (11.17.4) if that makes any difference.