Hi Oscar !

Your problem is a common problem in database application word. As I know MSFT database products doesn't have such nice thing like sequence field.
I also have looked for an ready to use solution but I could not find it. I have to develop my own soulution. It is based on such idea:

1. sequence has name and is concerned with a range of rows of data table.

2. there is a table/file that stores sequence name and last sequence numbers
( for exmaple INVOICES-SALESMAN1-2001 | 12345 )

3. Sequence name is based on table name and values of a few fields (salesman,day...). There is a function that evaluates sequence name from the values.

4. the fields values define also a range of rows of the data table (INVOICES)
(for example squence name INVOICES-SALESMAN1-2001 is bound to such sql range ... WHERE INVOICES.salesman ="SALESMAN1" and year(INVOICES.day)=2001 )
There is a function that evaluates select-sql WHERE statment

5. when an user enters(chooses) values into these fields (salesman,day) you can evaluate sequence name and now you can look into the table where last sequence numbers are stored. If you find row with the sequence name, operation is finished, if not - no problem - you have sequence name so you have also select-sql "WHERE clause". You can run SELECT MAX(invoicenumber) FROM invoices WHERE &YourEvaluatedWhereClause INTO...
(If invoice numer is a string an number is at first place eg. "12345/SALESMAN1/2001" you can use MAX(val(invoicenumber)) or you can also store invoice numer and invoice suffix in two separated fields.)

6. after user saves data you can store founded max value in last sequence numbers table/file.

It is basic idea of my solution.
Maybe it helps (I hope).



>Hi Nadia:
>
>I need to handle a set of “folio(s)” for different documents like invoices, purchase orders, etc. Such fields need to be specially handled by a function allowing the user to supply an optional number and the function handling this folios has to be smart enough to detect the next one (not exactly numeric) or any other available from a list. I stick to the idea of not using these unique value fields the primary key.
>
>MM contains a couple of functions to handle auto incremental numeric and base 62 character fields, but the stored procedure that triggers such functions seems not to be flexible enough for my purpose.
>Oscar
>
>
>>Just curios: what is the situation, where you need two auto-increment fields in one table?