Auto-Enter number in range and go back to beginning of range when maximum is reached
I can tell that this is a possible but I just don't know enough about FileMaker to implement it.
We have boxes with positions 1-81 in them. Each individual record gets two slots in the box. We've been entering the data manually until now in three different fields- Box Field, Position 1 Field and Position 2 Field. I would like to automate this.
So let's say that the last record has Box Field as 18, Position 1 Fields as 66 and Position 2 Field as 67. When I click on New Record I would like the Box Field to stay as 18, Position 1 to update to 68 and Position 2 to update to 69. I can get this to work with Auto-Enter: Serial Number and Validation: In range.
The problem occurs when the last record has positions 80 and 81. I would like the new record to increase the box number by one to 19 and for the position to return to 1 and 2.
A couple of caveats. I would like to implement this system on a database that already has these positions filled for many records and want to update them only for new records. Also, sometimes Position 1 reaches 81 first and sometimes Position 2 reaches 81 first so they both have to know when to return to 1.
I'm guessing that I have to link all these fields together somehow. Any help in getting this to work would be appreciated.