1 Reply Latest reply on Aug 21, 2015 1:16 PM by philmodjunk

    Unique Serial Number including data from same record.



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


        • 1. Re: Unique Serial Number including data from same record.

          I wouldn't call it "very complicated" but it's usually not a good design decision as it will be a more complicated approach than the very simple option of sticking with a straight serial number that always increments, is never changed and the sequence is never reset. If this is a value to be used as a primary key in relationships, this is a crucial consideration to make as anything along this line raises at least the slight chance that an assigned value will not be unique or will need to be changed for some other reason.

          To produce this value, the best bet is to isolate the serial number component from the rest of the data. One field is just an auto-incrementing serial number. The other combines that with the other text and formats it with the needed leading zeroes. The serial number part would be a number field. The other field would be text with an auto-enter calculation and a Unique Values validation set on it.

          "TKM-" & Left ( ArtYear ; 4 ) & "-" & Right ( "000" & SerialNumberField ; 4 )