Adding Multipile Lookup Field Values to Parent Record

Question

I am trying to add the ability to add more than one reference lookup field value to a parent record. I have two tables "Notes" and "Code_Violations". When I click on the "Create a New Note" button the Note form opens. On it there is the "Code - Violation" lookup field displaying the code violation options from the "Code_Violaitons" table. I would like the ability to add multiple code violations to one Note record if possible. Maybe, if possible, when I select the first one a new Code-Violation lookup field displays ready to add another if desired?

------------------------------
Brian Dunk
------------------------------

------------------------------Brian Dunk------------------------------

chayceduncan · Answer

I will chime in and based on reading the above - it sounds like a 4th table is needed compared to what Marcelo is suggesting. In this case - his suggestion is mostly right but I'll add some flavor to it.&nbsp;

You need 4 tables - HazMat, Code Violations, Notes, and for this lets call it Note Violations.&nbsp;

Your hazmat and code violations are good to go. Your notes table also sounds like its good. In the note - you should have 1 relationship - just to the Hazmat table since I assume the note in question is specific to 1 Hazmat record. You do not need a relationship to Code Violations.&nbsp;

Your 4th table - Note Violations or whatever you choose to call it - is going to serve as your many to many fix, it's your intermediary table that allows this type of setup. It will have two relationships - the first to Notes where Notes have many Note Violations, and then another to Code Violations where Code Violations have many Note Violations.&nbsp;

What I suggest you do then is open up your Note form and in there you should have a field for 'Note Violation Records' - its the default Report Link field Quickbase will build for your new relationship. You can use this report link field to display a report, and enable it for Grid Edit. This then would allow your users an Excel type row entry, where they can enter a note, put in comments, dates, files etc, and then in that grid list out each code that is currently in violation that they want to cite as part of that note.------------------------------Chayce Duncan------------------------------

marcelobenavide · Answer

Good morning. I think that it is not the appropriate design, you must have a table specks and a code_violations table, if you need to associate in a many-to-many relationship you must have a third table in the middle where you can create several notes to several code_violations.

------------------------------
Marcelo Benavides
------------------------------

briandunk · Answer

Ok, that sounds fine but just to be clear the Note is the parent record and I am trying to add multiple Code Violations tothe Note record. How would I set that up right now I have it setup as the Parent Table is HazMat_Codes and child is Notes and "Code-Violation" field is a lookup field in Notes table.

------------------------------
Brian Dunk
------------------------------

marcelobenavide · Answer

I'm not sure about your question, but the correct desgin is:&nbsp;

------------------------------Marcelo Benavides------------------------------

briandunk · Answer

Hmmm sorry for not understanding. I struggle with the whole table lookup setup.&nbsp; This is the dropdown and how it is currently being displayed on the form so it is showing the information I want.&nbsp; I just want the option of adding another code violation to the form if I want.
------------------------------Brian Dunk------------------------------

Forum Discussion

Adding Multipile Lookup Field Values to Parent Record