In one part of your post, you seem to have this set up and working for you and yet you wouldn't post your question if it was. Exactly what is the issue you are having?
Can't get the revision numbers to be sequential starting over with each item?
This isn't something that can be set up as simply as an auto-entered serial number but it can be done.
A key question: Is this database shared over a network where more than one user might possibly enter a new revision record for the same item at the same time?
I don't have the automation set up. I was just trying to explain what I need it to do.
I cannot seem to get the sequential numbering to start over with each id # - I have tried a script and calcs but both fail me so I figure I must be way off.
This system is shared over the network but only one person will be authorized to add the revision numbers. We figure this would help with errors.
Define a self join on the revision table:
MainTable::ID = Revisions::ID
Revisions::ID = Revisions 2::ID
Now Max ( Revisions 2::Revfield ) will return the largest revision number of all revision records for the same ID number and Max ( Revisions 2::revfield ) + 1 will return the next rev number.
This works! Thank you! Another question: How do I get the revfield to have leading zeros? ie: rev id 01 instead of just 1
I need the zero for sorting purposes.
For formatting purposes I'd use: Right ( "0" & Max ( Revisions 2::revfield ) + 1 ; 2 )
That will work for revisions 01 - 99.
You could also use the serialIncrement function for this.
Note: You probably do not need the leading zero for sorting purposes. You should be able to sort on the separate ID and version fields to correctly sort these records and these two fields should be number fields--not text.