4 Replies Latest reply on Jun 20, 2011 9:15 AM by Kays

    conditional sequence number

    Kays

      Title

      conditional sequence number

      Post

      hi

      i have these 3 tables:

      task(idTask)

      Stream(idStream, idTask)

      Action(idAction, IdStream, SequenceNumber )

      What can i do si that the SequenceNumber incremented automaticaly depending on the IdStream.

      Example, in the table Action i wanna have:

      IdAction          Id Stream            SeqenceNumber

         1                       1                            1

         2                       1                            2

         3                       1                            3

         4                       2                            1

         5                       2                            2

         6                       3                            1

       

      thx in advance

      K|Z

        • 1. Re: conditional sequence number
          philmodjunk

          How will you use the sequence number? There's more than one way to generate it and what you plan to do with it may rule out certain approaches.

          Will this be a shared database where multiple users are trying to create multiple records with the same Id Stream at the same time? (Care must be taken to keep mulitple users from generating identical SequenceNumber values for the same Id Stream.)

          • 2. Re: conditional sequence number
            Kays

            no just one user will be handling this.

            i duplicated the table "Action" and made it work just perfectly but my boss want another solution without duplicating the "Action" table,

            any ideas :) 

            K|Z

            • 3. Re: conditional sequence number
              philmodjunk

              If you just need the sequence number for display/reporting purposes, you can do it this way.

              Define an ordinary serial number field. We won't display this field, but use it to make sure that records are sorted in creation order.

              Define a summary field as the "count of" this new serial number field. Specify that it be a running count and that the count starts over with each Id Stream value.

              If you sort your records by Action, Stream, and the above serial number field, this new summary field will display the sequence you've specified. Obviously, this approach has it's limitations which is why I asked for how you planned to use this number. This method has the added advantaget that it will work in a shared database with multiple records beign created by simultaneous users.

              There are other ways to do this with a self join relationship and the max function, but it has to be set up just right in order to update correctly with each new record you add. This method can also result in duplicate values in shared databases unless extra precautions are taken to identify and correct the occurrence of duplicate values.

              • 4. Re: conditional sequence number
                Kays

                thanks man exactly what i needed :))

                K|Z