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
MarkShnier__You
Qrew Legend
Qrew LegendI 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
------------------------------
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
------------------------------ This is a quite a bit to go over for a single Forum Post, but I'll try to land the plane with a few more thoughts worth considering and then maybe others can chime in to help sort this out and make it more actionable! You're not the only one over complicating things, haha :)
It sounds like a one-to-many relationship between Customers and Certificates would work nicely. However, based on your first screenshot, it looks you've related these two table, but the relationship is "backwards." It seem like you are selecting the Certificate record on the Customer Form. Where, instead, you'd want to select the Customer record on the Certificate Form, right? That way the Certificate is "used" (redeemed) once the Customer is selected. And you'd be able to select the same Customer on different Certificates, but only one Customer per Certificate.
Then, to view the info from multiple Certificate(s) on a single Customer record, which I believe is your original question, you'd do so through the Certificate report link (embedded report) field which lives on the Customers table. You set the report link's radio button to "Display the related Certificate records directly on the form" when "Customizing this Form" and then selecting the Certificates report that contains the data you'd like to display (from multiple Certificate records).
Next, you may need to kinda invert the context of your reporting to be handled via the Certificates table (not Customers) to determine whether Certificates are available, redeemed (related to a Customer), still valid, or have expired.
As for the "converting the existing issued certificates over painlessly" … you could dump the Customer records from your current architecture to a .csv. You'd need to be sure to export the Customers::Related ID# and the Customers::Related Certificate. Then, "import with update" the data into the Certificates table, but intentionally kinda swap the fields when you import into the Certificates table. You'd need to be very careful here and reread all that! The idea is you'd update the existing Certificate records (not create new) to contain the Customer IDs if you were to follow the suggested architecture above. I'd actually hold off on this for now until you dialed in the schema. You may also want to consider making a copy of your app (and data) to test these ideas out first, especially the import.
And … you threw a curve mentioning the latter quote workflow! I'm not sure we should even go here yet, as this is getting fairly complex.
Nonetheless, it sounds like perhaps there could be yet another table called Quotes, where one Customer may have many Quotes. Each quote may have a Status field to represent a Booking. So perhaps you are really trying to relate (Booked) Quotes to Certificates (instead of Customers to Certificates)!
The mystery relationship may end up being one Quote has many Certificates. Since the Quote is related to the Customer, it can lookup other info like the Customer - Name. Then, once a Quote is related to a Certificate, the Certificate can look up the Customer info via the Quote. In other words, you'd have a lookup field that is looked up again. For example, Certificates::Quote - Customer - Name. Make sense?
Something like this may model your data as intended if your head hasn't exploded!
Customers
Record ID#
Name
etc.One Customer has many Quotes.
Quotes
Record ID#
Related Customer
Price
Status (e.g. Booked)
etc.One Quote has many Certificates.
Certificates
Record ID#
Related Vendor
Related Quote
Quote - Customer - Name (lookup)
Type (e.g. dropdown for Incentive or Gifting)
Vendor - Name (lookup)
Vendor - Code (lookup)
Certification Reference Number
Issued (Date)
Expired (Date)
Status (consider formula text field)One Certificate belongs to a Vendor
i.e. One Vendor has many Certificates
Vendors
Record ID#
Name
Code
Location (or Related Location)Vendors to Locations may be a many-to-many, but let's not go there!
------------------------------
Brian Seymour
------------------------------
