1 Reply Latest reply on Jun 11, 2011 5:54 PM by DavidJondreau

    Serial Number question



      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?

        • 1. Re: Serial Number question

          It depends a little on how this db is going to be used.

          The easiest solution is to have a relationship between Projects and Quote based on your primary key (you should have that relationship regardless). Then in the Projects table, define an unstored calculation, say Quote Count = Count ( Quote::_kp_QuoteID ). The in the Quotes table, your user-visible Quote ID field = Project::Project ID & "." & ( Quote Count + 1) .

          If this db is heavily used by multiple users, you may run into trouble if two people try to create a quote for the same Project at the same time. If that seems likely, there are other ways, such as having a script control the creation of new Quotes and a table that tracks each Quote ID. But that's probably more work than you need to do.