How to prevent databasejoin element updating existing records

[Stevebenson223]

I have a simple Fabrik list (called "People") with only First Name and Last Name (field) elements. This list is used by other lists to pull in these names using databasejoin elements. (My app is to record historical details of theatre productions, so the other lists will typically be cast lists of theatre productions.)

Occasionally, some of these people will change their last name (e.g. marriage). If I simply change their Last Name in the "People" list, it will change their name in all the theatre production cast lists I have already created. I don't want to do this because the records should reflect their name at the time of the production.

And I don't want to create a new record for their new name as it will duplicate the person and make it very difficult to implement a search facility to list all the productions for that person.

Maybe I'm missing something obvious but is there a way to prevent the databasejoin element being dynamic and changing all previous records? Or to use a different (non-dynamic) element type to pull in the names from the "People" list?

I've been struggling with this for some time and tried various alternatives but none work.

 

[juuser]

Only thing that comes into my mind atm is to use calc element. And in calc element code on new record return databasejoin element value and with existing record get and return exiting value from database.

 

[troester]

Maybe you have to change your setup.

If I understand it correctly the same person should show up with the 'old' name in an old production and with the 'new' name in a new one...
What if you want to add an old production and you changed the name in the People list already to the new one?

I think you need both (or more) names in your People list, maybe with year.
Either all informations in the LastName field or put the LastName (+ year) in a reapeat group.
You can then use the calc element to pull e.g. the LastName depending on the year of the production...

In principal:
A databasejoin element is a "link" to the recent label in the lookup table.
If you want to keep 'historical' labels you must copy them to an extra field, e.g. with the calc from @juuser , the autofill plugin or some php plugin

 

[Stevebenson223]

Thank you @juuser and @troester for your excellent thoughts on this.

I did consider having additional Last Name fields with date of name change, but I couldn't work out a way to put that into the databasejoin field. Using repeat groups and an additional calc element is a great idea.

I'm going to try both solutions and see which is most intuitive for users.