We have boxes with positions 1-81 in them. Each individual record gets two slots in the box.
Can you explain in more detail what you mean by boxes, slots and positions? Perhaps with an example?
Do you mean that you have a physical box or is this some kind of object on your layout?
In your database, might it every be possible that two or more people might be creating new records in this table at the same time?
Sure thing. It is a physical box that we put tubes in. There are 81 slots in each box and we call each slot a position. We put tubes into the slots/positions. We don't go back and change old boxes, we just add new ones as we need them.
Only one person edits the database at a a time and we don't edit each others records. If I get this process automated I will probably lock the Box numbers and Positions so that they can't be changed by regular users.
There is a script step that can reset the "next serial value" setting on a serial number field. It's called "Set Next Serial value".
Thus, you can create a script to create your new record that checks the values in the serial number fields of the new record and resets them to the correct initial value when needed.
But never, ever try this in a database where two or more users might be creating new records at the same time as there would be a chance of getting two records with the same set of serial number values due to performing the script at nearly the same time.
I actually did not know about scripts until your answer. I just wrote one. It's an If statement that looks at the value of the Position and if it's 81, resets the next value to 1. Where and when do I run this?
Add a New Record/request step as the first step and run it each time you want to create a new record. You can add a button to your layout to perform the script.
I just went on a nearly 6 hour dive into FileMaker Pro scripting and had a blast. I've got everything up and running. Thanks for the push.