Hello, and thanks in advance for any assitance. Fair warning, as a noob I may be missing the obvious. (I have tried to find an answer for this.) Here's the problem:

I'm working on a project where my client provides quotes, but not all quotes become jobs. In addition to the Quote Number which is indexed and applies to every record (no issue there), he wants an indexed Job Number assigned only to those quotes that are accepted as jobs. This means that he wants an indexed sequence that falls in order of record, but has the ability to skip records without skipping numbers.

I think that I may need a field returning true (radio button?) that then prompts a search for the last record with a job number applied and provides the next indexed number in a separate field, but I've no idea how to actually accomplish this. Am I making the task more difficult than it is, or is this just one of those things that must be hashed out?

The ID use use to create links between tables should not hold ANY meaning to the end user. And should be unique and not user editable. So as long as this field you speak of is not the KEY fields between tables then you can imposed any "logic" via a script.

When you wish to ASSIGN your number you could attach a script to a button that grabs the Max(table_self::Job Number) + 1 provided you have a Cartesian self-relationship.

your table occurrences..

table::somefield [X] table_self::somefield