Good day! I hope you can help me with the following.
I have a database of projects and I would like to write a script to assign each project an ID number. This ID number is not the Primary key of the record.
These ID numbers are assigned manually now, nevertheless I’d like to figure out how I can incorporate it into a script.
Here are some parameters of this number.
The ID has the structure as follows:
The number - P1701001, where
“P” - stands for Project
“17” is the year.
“01” – month. This parameter as well as the year is derived from the date of the project, which as a rule is assigned either automatically or manually upon creation of a project record.
“001” – sequence number of the project within a month. This one is reset every month and started over.
It is planned that the database will be hosted in a multiuser environment; therefore I assume some precautions should be taken to avoid the situations where the same ID is assigned to two different records.
One way I might approach this is to have the part of the number that is sequential in a "NextID" table with a single field that holds the next project ID. Make this a increment-able field on creation.
Then, your script could just prepend that number with:
RIGHT(Year(Get(CurrentDate)) ; 2)) ,
and other pieces.
Does that make sense?