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
------------------------------
