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.)
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 :)
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.
thanks man exactly what i needed :))