Forum Discussion

KimCameron2's avatar
Qrew Trainee
2 years ago

Multiple lookup fields out of same table

I have a table "Customers" and I have a table "Cruise Certificates".  Currently I have a lookup in "Customers" to link the certificate that is issued to the customer.  There are times when a customer is issued 2 or maybe 3 certificates.  I do not know if I am overthinking it or if I just do not know how, but I need to be able to have 2 or 3 lookups but that will pull different certificates.  Is this possible?

Kim Cameron

9 Replies

  • The right way to do this is to have a Many to Many relationship.  This is less scary to set up than it initially sounds.  

    One Certificate has many Customer Certificates
    One Customer has many Customer Certificates.

    You just set up that middle (sometimes called "join") table called Customer Certificates and it will actually initially have no fields of its own.

    Then you make a those two relationships and you look up down to Customer Certificates the name of the Customer for example and you look up from Certificates the name of the certificate. 

    Then on the customer record you will have an embedded report of all the Customer Certificates and the set up the embedded report to show the certificate name. 

    Likewise on the Certificate record you would have an embedded report of all the Customer Certificates where you would show a column for the Customer name.   

    Mark Shnier (Your Quickbase Coach)
    • KimCameron2's avatar
      Qrew Trainee

      Thank you so much for responding!  I am going to work on it right now!  (fingers crossed)

      Kim Cameron
      • BrianSeymour's avatar
        Qrew Cadet

        Hey Kim,

        You've mentioned that one Customer may have multiple Certificates.

        But, before you go the many-to-many route, can a single Certificate actually issued to multiple Customers? In other words, can Customers share a common Certificate?

        If the latter is not the case, I'd make the suggestion that embedded reports (via a Report Link field in the Customers) table may be able to show the certificate information you need. Typically that report link field is create automatically when you create a one-to-many relationship (e.g. One Customer has Many Certificates). In which case, on the Customers Form, you may click the Customize this Form (top-right) and find the the Report Link field, which is likely called Certificates, then, select the "Display the related note records directly on the form" option and point it to a report containing the Certificate info you'd like displayed.

        Alternatively, you may want to check out "Multi-select Text (summary)" where you where the parent record (Customers) could list multiple Certificate values in a single field. You select "Combined Text" when creating your summary field to get this setup.

        Otherwise, if this is a many-to-many relationship, you can then shift your reporting to the context of the pivot (middle/join) table.

        But rereading this, Mark is probably on the right track with the many-to-many, as this also would set up pretty nice reporting from the context of Certificates. The pivot table would represent Certificate Issues and may contain fields such as:

        Related Certificate (ID)
        Related Customer (ID)
        Certificate Number
        Date Issued
        Date Expired
        Is Active

        I really encourage trying to break your own logic before building and really digging into the business questions and process logic before building QB infrastructure … as it becomes significantly more difficult to change foundation architecture once you start building on top of it! I hope that helps.

        Brian Seymour