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

    numbering records within a group


      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!



        • 1. Re: numbering records within a group

          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

            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

              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.