I am currently developing a sales database where I would like to give each new document created a "sequential number." However, each document requires a reader field to limit viewing of the doc to other sales groups.
If I understand your problem correctly, you want to apply sequential numbers to each document, but that requires that you look the last document up to see its number so you can increment the new document's number by 1. Problem is, the reader names field may restrict you from seeing the last document, so you will not get the highest valid number.
One way around this is to write some code in a closing event of your form that manages a single document that keeps a record of the serialization of your documents. You can then just look to this document to figure out what the next number is. This offers some speed advantages but has a little pitfall in that it will be a little more exposed to document corruption and replication/save conflicts. An alternate method would be to create a "stub" document that tracks the serials for each document that you create. This document would have no readers field and might contain fields like (serial_no) and (parent_unique_id). I would include the parent_unique_id field just in case you run into problems later with missing documents. If you do, then you can write an agent to manage orphans and missing children easily.
Make sure that your closing code that creates this "stub" document immediately follows it up with a simple lookup that verifies the document was created successfully. If it was not, do not allow the save of the initial document. Hope this helps you.
This was first published in September 2001