Automatic Record Numbering in a Shared Database

Many databases applications require that each record contain a unique number that can be used to identify the record. Common examples include invoice numbers, batch ID’s, employee numbers, etc. Panorama can automatically assign a unique number to each new record as it is created, even if several people are using the database simultaneously over a network.

Setting up automatic record numbering should usually be done before you convert the database from single user to sharable. First set up the field that will contain the automatic record number. This field must be a numeric field. To specify that this field should contain a unique record number, the default should be + . Do not specify any increment value, just use a single + character. When you add the + sign in the default value Panorama will start numbering from 1, no matter what is already in the database. If you want to start with some other value you need to set that either manually (using the File>Database Options dialog), or in code (using the setautonumber statement).

Once the default is set up, share the database to the server (see Creating a Shared Database.

When a database is in single user mode, the next record counter is kept in the database itself. When a database is shared, the counter is kept on the server. Every time a new record is created, the local copy of Panorama will ask the server what the next record number should be (the server then automatically increments the counter for next time). Even if the record is later deleted, the number will never be re-used (unless you reset the counter manually as described below).

To illustrate how automatic numbering works, let鈥檚 assume that Bob Blue and Rudy Red have been hired to do some data entry on this personnel roster. The database starts out looking like this on both Bob and Rudy’s computers:

The first field, EmployeeID, has been set for automatic numbering. So far the employees have been assigned numbers from 1 to 7. Now Bob starts by adding a new record to the database. As you can see, this adds employee number 8 to the database. This screenshot shows what Bob sees as this point.

Before Bob can even enter any data, Rudy gets to work and adds a record himself. The server knows that 8 has already been used, so this is employee number 9. This screenshot shows what Rudy sees as this point.

Meanwhile Bob has finished entering the first record and adds another. This is employee number 10.

As they are adding new records, Bob and Rudy will not see the records added by each other (or anyone else). If they want to see all of the new records, they can simply choose Synchronize Data from the File menu, and the records entered by other users will appear.

If you want Panorama to automatically synchronize each time a new record is added, simply set up a .NewRecord procedure containing a synchronize statement. This procedure will automatically be triggered whenever a new record is added. See Implicitly Triggered Procedures for more information about .NewRecord procedures.

Manually Changing the Record Number Counter

The automatic record number normally increments by one each time a new record is added, but you can manually change the record number counter at any time using the Database Options dialog (in the File menu).

To change the next record ID # simply type in a new number and press Ok. For example, suppose you want the next employee number to be 50. Simply type in 50 and press Ok.

This change takes place immediately, so the next time a record is added, the employee number will be 50.

Keep in mind that this is the next record added anywhere on the network. If someone else adds a record before you do, you鈥檒l see 51, 52, or a higher number on your computer.

Accessing the Next Record Number in a Procedure

It is also possible to access and modify this ID number in a procedure with the GetAutoNumber and SetAutoNumber statements. To access the next record ID # use the GetAutoNumber statement. Here is a simple procedure that displays the next record ID number.

local id
GetAutoNumber id
message "The next record number will be "+str(id)+"."

Keep in mind that since this is a multi-user system someone else may use that number before you get a chance to, even if the addrecord statement is the next statement after the GetAutoNumber statement.

To change the next automatic record number use the SetAutoNumber statement. This one line procedure uses the SetAutoNumber statement to reset the record ID number to 1000.

SetAutoNumber 1000

This is exactly the same as setting the number with the Database Options dialog sheet.

See Also

popupsharingmenu -- pops up a menu for the toolbar sharing icon.
Record Locking and Editing Shared Data -- coordinating data entry across multiple simultaneous users.
setbannericon -- sets the icon on the right side of the toolbar banner.
Shared Database Synchronization -- data synchronization across multiple users.
Sharing Icon & Context Menu -- operation of the sharing icon and pop-up menu (in the toolbar).
Working with Sharing Disruptions -- dealing with network & power outages, disconnections, and other anomalies.
Working With Summary Records in a Shared Database -- summary records are not shared between users.

History

Version	Status	Notes
10.2	New	New in this version.