Capturing name changes - solutions?

[txag08]

I'm working on a pet project to familiarize myself with developing in ServiceNow Studio. I like to go to see live theater and I'm building a database of theater I've seen. I've run into a bit of a problem, and though I've come up with a solution, I'm not entirely satisfied and am just curious to see how others might attack it.

The problem is that I have a table of actors and I have a table of theater buildings. Both actors and buildings can change names. I also have a table that collects all of the performances I've seen. When I am looking at a performance record, I want to see the name of that actor or building at the time of the performance, regardless of what it might have changed to later.

I've gotten around this by creating tables of "alternate names." For actors, I created a "Credited as" field and just fill that in with an appropriate name. The problem I have with this is that the Actor related list contains the "Credited as" column and it is mostly a column full of empty values, because of course, this is an outlier condition.

For the building issue, I included two fields on the performance table: one that's a reference to the theater table and one that's a String for the theater name. I set up a business rule to automatically fill out the string value with the name of the theater at the time of the performance. If the names of the two fields are the same, I hide the second field on the form. If they are different, the second field will be displayed.

Anyway, like I said, the solutions work, but just seem a little clunky to me and I was curious to see how someone else might approach this or if there may even be some helpful feature in ServiceNow that I've overlooked.

[Slava Savitsky]

Similarly to how you capture the name of the theater at the time of performance, you could also capture the name of each actor at the time of performance in a string field and only display that column in the related list and remove the other one (the reference) from the list layout.

[txag08]

Yes, I did think about that. The problem is that I do want that reference available on the list so that I can click it and go to the Actor's record. If I only display the string field column, I will not have that clickable reference.

[Mark Manders]

Do you have a date stamp on your 'performances I have seen' form, to show when you saw it? With that, you could just get the cast members that were present at that date. 

It's still not full proof, because you could have a play running for years in which the main role is played by 2 actors that allow for them to also have a night off, so they are both still on the play, but it should limit the real changes (first actor leaves the production, second one comes in). 

[txag08]

Yes, in fact, that is what I'm doing. I have the table for the production which has a list of all actors and their start/end dates with the production. Then on the "performances I have seen" I have added a customized relationship as a related list that filters only on those actors that were with the production at the time of that performance. I also have a "Replacements" table that covers issues like understudies and alternating roles.

The issue is that sometimes an actor changes their name. So I see, for example, "Mary Roberts" in one play. Then a few years later, I see her in another play, but she has changed her name to "Mary Stanley." She's still the same person, and I still want a reference link to direct the user to her one record in the database. However, I essentially want that link to say "Mary Roberts" on the page for the first play and "Mary Stanley" on the page for the second. If it's possible, I can't figure it out, so I was just trying to see if anyone else would attack this differently.