Forum Discussion

TonyaJacques's avatar
TonyaJacques
Qrew Member
6 months ago

Creating a Many to Many relationship and bringing records into the

If I have a database where my tables are events and audits and I want to be able to have many events and many audits that can be related both ways, I know that there is an intermediary table required, which i have set up. How do I bring the existing data into the intermediary table without creating a new record. The goal is to list certain events that are related to the audit on the audit form. Any help is appreciated very much.



------------------------------
Tonya Jacques
------------------------------

6 Replies

  • What do you mean exactly? Do you have existing data that you've loaded into your intermediary table or are you referring to something else in your system?



    ------------------------------
    Chayce Duncan
    ------------------------------
    • TonyaJacques1's avatar
      TonyaJacques1
      Qrew Member

      I have information in the audit table and I have information in the events table. I am trying to create a field in the audit table to be used on a form that allows a user to search and select events associated with an audit record. This would mean a many to many relationship is needed right? I am having trouble with all of the events showing up in that field. Does the middle table that I am calling "events and audit links" have to have all of the records pulled into that table from both sides (events and audit tables) in order for the events to show up in the field for the audit form?



      ------------------------------
      Tonya Jacques
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain

        If I'm understanding correctly - when you're on an audit record - you want something that allows a user to pick one or more events associated with that particular audit, and then conversely the same event may be picked/chosen on multiple audit records? 

        If that's correct - then yes, your best solution is the many to many table between them called something like 'Audit Events' where an Event has many Audit Events and an Audit has many Audit Events. You don't have to pull in all of the records to this table - instead you let your users add / enter each event that they want to apply to that audit one by one. There are a couple different ways to do that depending on if you're using new or old forms in QB. 

        When you create a relationship QB will natively create a report link for you to display/show the children records when you're on a parent form. If you're on an Audit Record - you can show that report and use the 'Add Audit Event' that is natively available which would open up a native form where the Audit is already pre-filled and the user then picks from a dropdown which event they're associating. The user then saves, and repeats that add form process for each event they're associating. 

        If you're still using the Legacy Forms in Quickbase - you can also use the embedded grid edit feature, where you can embed that same report link on the form and enabled it to be a grid edit report where it operates more like a grid edit/excel type row entry, where they just pick a new event in each row. It's a little quicker since you're not adding/saving each time, but the drawback is that the dropdown for events isn't as 'modern' and so you can't type search to filter the dropdown. 



        ------------------------------
        Chayce Duncan
        ------------------------------