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...
Forum Discussion
Thank you so much for responding! I am going to work on it right now! (fingers crossed)
------------------------------
Kim Cameron
------------------------------
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
Status
Is Active
etc.
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
------------------------------
Brian,
Thank you so much for the other suggestions. And you are exactly right about the difficulty of changing the infrastructure. Our policy has always been 1 cert to 1 person, until recently. So at the moment I am stuck trying to figure out how I would switch over all of my current relationships/link between certificate and customer to the middle man table if that is the route I take.
To answer your question, there is never an instance when one certificate can belong to more than one person.
And then now, the multiple certificate leads me to somehow linking a booking to a certificate so that the certificate is not used more than once or so that the correct certificate is used with the correct booking. I feel as if this has tossed me into quicksand. I am wondering if the answer might be to add the customer in again and link to original with the additional certificate.
Any thoughts?
------------------------------
Kim Cameron
------------------------------- MarkShnier__You
Qrew Legend
I think it might help us help you if you give some examples in plain English of what the certificates represent. And explain with an example how a customer used to be assigned to a single certificate in the past, and now they are being asssign multiple certificates. What does a certificate represent?
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------I agree with Mark that plain English examples would help!
Oftentimes it helps to "search for the nouns."
Without more insight into your biz, I think what we all may be trying to describe is the process of "Accreditation" or "Certifications." Certifications diff from Certificates, in this sense. But since they sound similar, they may cause ambiguity and confusion. Perhaps we have Customers, Certificates and the process of issuing of these Certificates to Customers (represented by the pivot Certifications table).
But … I just don't know if it's worth "normalizing" Certificates in this scenario. What other data points about Certificates do you track? For example, Types, Tiers, Descriptions or the Organization that issues these Certificates? In other words, that may be unnecessary, in which case, could the Certificate just be a dropdown field on the Certifications table?
Examples help! Check out the attached screenshot from an Excel file using "vlookups" that may gives you ideas or provide more clarity.Keep digging!
------------------------------
Brian Seymour
------------------------------
