Serial Number question
I have a projects table with a __kp_ProjectID field. I need to create quotes that are related to a project, but we need to have the project ID number as part of the quotes number. Now, this quote number is one that the user would see and will NOT be the primary key field that is auto generated whenever a new record is created in the quote table. I know not to mess with that. I need a calculation or something that auto generates the quote ID that the user sees.
For example, I have a project ID number of 10000. When the user creates a new quote for that particular project, I need the first quote to have the number 10000.01, the second one generated would be 10000.02, the third would be 10000.03.
For project ID 10001, the subsequent quote IDs would look like this: 10001.01, 10001.02, 10000.03.
If a quote was deleted for the above project, the serials are not reset: so if a user deleted quote 10001.02, then the remaining related list of quotes for that project would be 10001.01 & 10000.03.
How would you handle this?