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
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)
mark.shnier@gmail.com
------------------------------
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
------------------------------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
------------------------------
