AnsweredAssumed Answered

unique serial numbers including date

Question asked by tkmstudio on Aug 9, 2015
Latest reply on Aug 9, 2015 by philmodjunk


unique serial numbers including date



I'm new to writing scripts for FP Pro13

I've learned much today by searching the forums but I'm stumped now... I may have created more questions than I resolved. I'm not confident enough to run this yet, I already know I'm stuck and it is incomplete. Anyhow, I Hope I can get some direction.

I am entering artworks (items) into my database.   Each work has Art Title, Art Year, etc. all those parts are working well.  We have about 600 works entered. Many more to come.  

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.   The Id should reflect the date of creation, not the date that the work was entered into the database.  The artworks come in as they are located, so a straight serial - first in first out numbering system is not ideal   SO

Ideally I'd like to make an "ART Inventory ID" numbers that look like TKM1996001,TKM1996002,... TKM1996999   TKM1997001  This field should copy/list the Art Year from the SAME record -( it shouldn't search through the whole table, just the record at hand)

Once the year is set,  (and some items in Art Year are non-standard  eg  1996-2015 (indicating a work made in 1996 and restored in 2015) so I want to limit the text to the 1st 4 digits)    Then there would be a incrementing serial number, 001,002,...999  If I enter an artwork from 1996 it would increment +1 from the previous 1996 artworks.  If I next enter an artwork from 2009 it would increment +1 from the previous 1996 artworks.

I tried something along these lines and realized I'm in over my head.  I'm not certain it's possible.

"TKM"&"List ( field {; Art Year} )Left ( text ; 4 )&SerialIncrement ( text ; incrementBy1 )"

There's obviously a bunch of code missing that would make the increment jump around from year to year. I also have no idea how to restrict the copy action to the active field. Maybe that's a default. ?   Maybe this isn't possible at all, and someone can suggest some other way to handle this archive.  


Thanks   Kelly