Unique Serial Number including data from same record.
I've deleted a similar post because I've scaled back my ambitions. I had wanted to generate a serial number that looked like this TKM1996-001 , TKM1996-002 etc. TKM2003-001 , TKM2003-002 etc. re-incrementing the number for each year seems to be a straightforward matter of logic, but I was told it is very complicated to script. ? So perhaps TKM1996-001 , TKM1996-002 , TKM2003-004, TKM2015-005, TKM1999-006, etc is going to be the best I can manage.
I've tried a few things now using calculated fields, set fields and self-relationships, but I'm not getting the results I want. at this point, I'm confused as to whether the field is even supposed to a TEXT, a NUMBER, or a CALCULATION should I be auto-entering in the field, or just limiting what's allowed through validation. I'm totally f&*ing confused now.
here is an overview of what I've got going on.
As I am entering artworks (new records) into my database Art Title, Art Year are first up in the tab order. Somewhat later comes the field Art Inventory Number TKM
I want my ID/serial number to include the letters TKM followed by the "Art Year" (limited to 4 digits because sometimes Art Year might be entered as something like 1999-2003) . Art Year should be (copied or looked up) from within the same record. Followed by a unique serial number
Each work should end up with a permanent ID number-It can't change once it is written and labels are put on crates and artworks.
Can someone please explain how I can do this?
p.s The one thing in particular that has me going in circles is how to restrict copying data/lookup/whatever to the same record. perhaps once I have the unique ID number, I will be able to set relationships to other tables, or other fields within a record based on the unique ID number. chicken/egg.