I am trying to set up a service-order planning and tracking system.
One entity (in a folder) are customer details: Company name, site location, etc.
Some of the important details in the entries in that folder are people’s names for things like primary contact, technical contact, purchasing contact, machine operator, maintenance supervisor, and so on.
At the higher-level (up a folder level) in my tracking system, I have entities that record details and history of service incidents where we have/will go to the customer’s site and perform service on the machinery we sold them.
For each of those service incidents, I need to include who the contacts are for each incident.
Over time, it is expected that the people’s names in the lower-level Customer records will change, due to normal attrition and replacement/promotion of staff.
My question, then, is this: what is the proper/best way to reference the name of the person in that role at the time of the service incident, so that person’s name STAYS there even if later the company’s employee for that role changes?
Here’s the scenario (names, dates and numbers are chosen at random):
- So, company “Amalgamated Widgets” has a technical contact of “Sally Smart”.
- On October 1, they request a service incident, and we assign an incident number 10014.
- We enter that service incident and reference the current technical contact in our incident form, Sally Smart.
- We handle the incident, we get paid, and the incident is considered closed, or finished.
- On December 1, Sally gets promoted and “Billy NotSoSmart” replaces Sally as the current technical contact.
- On January 1, Billy reports another incident (#11243), and like in step 3, the current technical contact, Billy, is showing in the incident form.
- Three months later, in April, we go back and review all of Amalgamated Widgets’ service incidents.
The desired behavior is that incident 10014 shows Sally, and 11243 shows Billy.
Using a “reference” style attribute would, based on what I’ve seen so far, incorrectly show that both 10014 and 11243 will list Billy as the contact.
Is there a (good) way to get the desired behavior without having to have yet another global catalog (table) of “people”? Because even then it seems like the double-indirect reference (people ==> company ==> incident) would still have the same problem, unless I was required to select the incident technical contact directly from the global list of people. Instead, I’d like the current technical contact to be copied to the new incident, so that the value persists, not the relationship.
Make sense?
Thanks in advance for your guidance.
Thom Randolph