3 Replies Latest reply on Dec 18, 2016 9:20 AM by philmodjunk

    numbering records within a group

    adim

      Hello everybody

       

      How do I automatically number a card with a serial number accounting for the whole database AND a serial number for each group (like a state),

      like 123456NY1234, with 123456 the total number of records and 1234 the number of records in NY?

       

      Many thanks for your help!

       

      Alex

        • 1. Re: numbering records within a group
          philmodjunk

          Why do you want to do that in the first place? (In database terms, it's not a good idea and there are likely better alternatives)

           

          Sometimes you have to do this kind of thing because a client insists on it or you have to support either a special labeling system or a legacy ID system that predates your solution, but I don't advise this ID method if you can possibly avoid doing so as getting correct, consistent values--especially in a multi-user solution, can complicate your solution.

          • 2. Re: numbering records within a group
            beverly

            I would make these separate fields/variables and recombine for reports/printing as needed.

             

            Sent from miPhone

            1 of 1 people found this helpful
            • 3. Re: numbering records within a group
              philmodjunk

              To expand on how different serial numbers for different categories can complicate your solution, the typical methods use one method or another, ExecuteSQL, A sorted relationship, the Max function with an unsorted relationship... to get current max value for a given category in order to add one to that value to get the next value in sequence.

               

              Where this typically fails is when two users try to add a new record in the same category at the same time. They can both get same max value and thus the same next value. You thus have to test for and handle duplicates should they happen.

               

              I know of a method that does not have this limitation, but it requires a different dedicated table for each category. This makes it impractical if the number of categories is not fixed.