1 Reply Latest reply on Aug 18, 2009 3:12 AM by comment_1

    Calculated Value for Primary Key

    leesan

      Title

      Calculated Value for Primary Key

      Post

      I have a problem.

       

      I have a Client table (client_id, client_name, address).

      I want the client_id is automatically assigned when a new client is registered based on following criteria:

       

         Format: X-00# where X is the first letter of client name and

                    # is the (total +1) number of clients with the same first letter as X.

       

         Scenario 1:

                My first client is John Doe.

                So the client_id for John Doe is J-001
        

         Scenario 2:

                I already have 3 clients (Jennifer, Dave and Jackson)

                So, John Doe's client_id is J-003

       

      How do I code this logic? Thanks.

       

        • 1. Re: Calculated Value for Primary Key
          comment_1
            

          The best way to do this is not to. A primary key should be completely indepedent of any attribute a record might have - not to mention other records (consider what would happen if a client is renamed, or deleted).

           

          You can easily append the first letter of client's name to an auto-entered serial number (running consecutively for ALL clients). It's also posible to calculate dynamically each client's ordinal number within their "group". But neither of these should be used as the primary key.