I have a many to many relationship where one side of the relationship has an EXTREMELY LONG list of items (regulations and related clauses). I want to use a dependent lookup to related cases to relevant sections of Acts.
For example:
Police investigation a CASE into Criminal A. The criminal may have allegedly breached a few clauses of the Crimes Act and also the Stalking Act. So I want a CASE Table record to be related to the relevant clauses of the alleged breach. But the list of acts and clauses is massive. So I would like to select the Act first, and then the dependent clause for that act second.
For example
Case 101 (from the Case Table)
is related to
Crimes Act clause 10
Crimes Act clause 12
Crimes Act clause 26
Stalking Act subclause 12(c)
Stalking Act subclause 12(d)
from the Breaches Table.
Is there a modelling way or a dependent lookup way to improve the user experience so that when they are in a case, and need to related that case to many pieces of regulation and many clauses, it could be done nicely by 1. selecting the Act, and 2, selecting the related clause of that Act?
I am struggling with the concept of multi-value, multi-row many to many relationship.
Perhaps no relationship between the Case Table and the Breaches Table? with the Breaches table having say 3 columns, auto id, Act and Clause, and then the Act clause column is A lookup to a table Called Regulation, and the Clause column in Breaches is sourced from a Clause Table, and then the Regulation and Clause table are related and therefore a dependent lookup could be made between the two data entry fields on the Breaches table relating to the Act and Clause tables.
Perhaps that would work ... typing it out I may have solved it. I will check back.
