Parent to Child relationship where the Child can select multiple parents?

Question

I have a table of "Cases" and a table of "Attorneys" where many cases can belong to an attorney. Each case has a "Lead Attorney" but can have multiple "Assisting Attorneys". I am attempting to be able to have a multiple select list where I can select multiple assisting attorneys from the "Attorneys" table however I am not sure how to do this. Would the easiest solution be to create a new relationship where a case can have multiple attorneys?

------------------------------
Niraj Shah
------------------------------

markshnier__you · Answer

I suggest that your initial relationship is correct where you have one lead attorney has Many&nbsp;cases.But then your next relationships will be the classic mini to mini situation. One assistant attorney has many cases but of course one case has many assisting attorneys.

So the solution is to builds middle &nbsp;table or a join table it is sometimes called Case Attorneys.Then when viewing a case record, there &nbsp;would be a lead attorney select from the drop-down at the top of the case and then there would be an embedded child &nbsp;table of its assisting attorneys. And conversely there would be a record for the attorney and it could list two separate tables. One table would be the table of cases where that attorney is the lead attorney, and a second embedded &nbsp;report would be a list of the cases where that attorney is an assisting &nbsp;attorney.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

nirajshah5 · Answer

As always, thanks Mark. To paraphrase are you saying that I keep my Attorneys and Cases table and then create a new Case Attorneys table via where both the Lead Attorney and the Assisting Attorneys can be added by a relationship among all tables?

------------------------------
Niraj Shah
------------------------------

markshnier__you · Answer

Exactly.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

nirajshah5 · Answer

Thanks, Mark.&nbsp;I'm trying to think of a way to make the Assisting Attorney's a multi select user field versus having to have the user add multiple attorneys as the assisting attorney. Do you think this is possible in anyway?------------------------------Niraj Shah------------------------------

markshnier__you · Answer

&nbsp;If you make it a multi select field it will cripple your ability to do any kind of meaningful reporting.

I suggest you set up the many to many and then if you feel strongly that the data entry is too slow we can work on ways to improve the data entry.&nbsp;
------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

Forum Discussion

Parent to Child relationship where the Child can select multiple parents?

Related Content

Using Grid Edit to Edit Multiple Records at Once

Cross App Relationships

Add child record through parent form and redirect back to parent form in edit mode

Totaling Child Records Live on Parent Form

Edit Multiple Records at Once with Grid Edit