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
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
------------------------------OK, I am going to provide the details and then I will also provide some examples.
We have cruise certificates that certain partners (aka Vendors) purchase from us to use as incentives and/or gifting. There are 2 distinct types of certificates: one covers a portion of the cost of the cruise (incentive) and the other one covers the entire cost of the cruise (gifting). Certificates are coded by a Vendor Code, a Location Code, Certificate # and Issue Date. Each certificate has a life of 18 months - it expires 18 months from the Issue Date. The recipient of the certificate must redeem that certificate with my company.
All of the certificate information is loaded into a table "Cert Tracker". So, in my table "Customers", I have a lookup field to pull the certificate into the customer record. Up until recently, this has worked great. Now we are having instances where a customer is issued more than one certificate, or 2 certificates but from 2 different vendors, but all falling within the same general time period.
For example, Susie renews her membership early with her vacation club and as an incentive to do that, she was given a cruise certificate and then 2 months later, she qualified for a cruise certificate with a completely separate vendor by selling so much of a product. She now has 2 certificates that have to be used within 18 months. Because of this, I was attempting to do the many to many with the middle table, but I am not seeing a way to convert the existing issued certificates over "painlessly".
One reason it is important for me to be able to link the certificate to the customer record is because I do pull data regarding overall usage, vendor specific usage, customers using certificate but upgrading, etc. When that customer calls in to book their cruise, we normally provide multiple quotes and I would like to be able to link or reference which quote is done based off of which certificate and then as the quote then becomes a booking, it can show that certificate as redeemed.
I do tend to overthink things, so perhaps there is something I am missing or making harder than it truly is. I apologize in advance for that!
I am attaching a couple of screenshots, but please let me know what more you need.
Thank you for your patience and assistance!
Kim
------------------------------
Kim Cameron
------------------------------- MarkShnier__You
Ok, so you definitely need a child table of certificates. It will be possible to copy the existing certificates into the child table if you decide that you want all the certificates in the child table so that you don't need two methods to run reports.
I have done that before and I can get on a short zoom call with you if you like to do that together. It can be dine without damaging your existing certificates, which would then be hidden once you are satisfied they all got migrated to the new method.
There also an east way to roll up the all the certificates up to the parent record if you need a few key fields rolled up so that can be listed in a report of customers.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
