Sequence number +1 based on query on other table

[Phil Gsy]

Hi i would like to add a new table (TRADE_SEQ) to my document management database which would contain a squential number in the format currentyear#### so it would start at 20100001. The next sequence number would be generated automatically when the largest one on this table has records against it in another table.

This would allow the user to save all documents for one trade against the currentyear#### code and allow quick searching.

Im happy creating the table and adding the field to the profile and search form its the seq+1 if records against the largest exist in the other table

Many thanks in advance

[gpl]

You are storing 2 values in the 1 field, the year and the sequence number

Far better that you have 2 columns, one for the current year and one for the sequence, your front end can then worry about the formatting and presentation.

You can use a trigger to calculate and store these 2 values