We have to do that with our invoices. They're preprinted NCR forms with an Invoice number already printed on the form. Since each cashier has their own printer loaded with these forms, I have to mark each invoice with the correct next number in the series for that cashier once they print the invoice. Please note that I don't use this number as any kind of primary key in my database system--I have a standard auto-entered serial number for that.
The details can vary and I have to make a few assumptions about your setup here. So read carefully and make adjustments as needed and ask questions if something doesn't make sense.
Assumption: Each user has their own account name and password that they use to access the database.
When a user opens the database use a script selected to run when the file opens (Use file options to do this) to capture the user's account name in a global field:
Set Field [Table::gAccountName ; get ( AccountName ) ]
Define a table, SerialNumbs, with at least these fields:
Link this table to your table where you need the serial numbers added to each new record:
Table::gAccountName = SerialNumbs::AccountName
Use a script to create the new record where you'll assign a serial number:
Set Field [Table::SerialField ; SerialNumbs::LastSerial + 1]
Set Field [SerialNumbs::LastSerial ; Table::SerialField ]
You can place a button on your layout for them to use to create new records. With filemaker advanced, you can set up a custom menu that replaces the standard new record menu option with a call to this script instead. If you don't have advanced, use security settings to prevent creating new records in this table and set the script to run with full access privileges.
Note that with your specified ranges of serial numbers, you'll have to allow for the future case where user 1, for example, creates a record with serial number 19,999 and now the next value falls in another user's range.
Thanks so much for responding so fast.
I am using FileMaker Pro Advanced 11.
1. The person I am doing the db for does not want to use user account, Just a straight access.
2. Using FmTouch for the Ipad to sync data to desktop
3. Dont think FMtouch can do too much scriptiing
4. FilemakerGo does not sync at all.
What I thought of doing was create 3 Auto-Serial Numbers all starting with different numbers as mention above and linking the user to the numbers generared with a drop down menu of the users name.
I hope that works because I am out of ideals right now.
Whether you use account names or not, you have to identify the user in order to select the correct serial number. If you don't use separate accounts, you can still use the method I describe, you just have to modify it to establish a relationship based on whatever method you are using to identify the user.
In our system, for example, we use Get ( UserName ) as this is a preferences setting specific to a particular account on a particular computer and we can use it to identify the computer being used to print the tag as each invoice printer is connected to a different workstation.
Oh I see, I will try it.
Thanks A lot