Serial number related to Parent record?

[skearton]

Hi folks,

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 gurus approach this?

[comment]

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.

So, if a user deleted quote 10001.03, the next number should still be 10000.04, right? So where would this number come from, when the project has only 2 quotes and the highest quote number is 10001.02?

What I am trying to say is that these requirements are somewhat conflicting: when a new quote is created, it's number should be generated based on previous quotes of the same project - even deleted ones. But after that it should be independent - AND still remain unique.

Why don't you simply concatenate the ProjectID and the QuoteID? True, you will have gaps in the sequence when you look at the quotes of a project. But you will have those anyway if you allow deletion of quotes. And at least you will have a reliable system in place - something you cannot say for any other method.

[skearton]

Thanks Comment,

I see where you are coming from. Could a calc be setup that sets the quote serial addendum number (ie .01, .02, .03, 04) back to .01 for each new project that is made and it is related too?

What I don't want, is quote IDs that look like this:

10000.01, 10000.02, 10001.03, 10001.04, 10002.05, 10002.06 (where the quite addendum number is serial through all of its records).

[comment]

That's the same question as before, isn't it?