• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Calculation Fields > Updating Auto-Entry Serial Numbers

Updating Auto-Entry Serial Numbers

When you delete a range of records in a database that uses an auto-enter serial number, you might like to be able to update the value of the next remaining serial number so there won't be a gap. With the Set Next Serial Value script step, FileMaker provides a delightfully simple way to do this. (If you are new to scripts, see Chapter 7, “Creating Simple Scripts” for an introduction to the concept.)

In this script, we'll use a field called ID Number that contains the auto-enter serial number option. Since this script uses a Sort command, make sure that your serial number field is specified as a number field. A field defined as text will not sort serial numbers properly, because it uses the alphabet as its sort criteria.

To update serial numbers

Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S) (Figure 4.16).

Figure 4.16. ScriptMaker allows you to combine actions and replay them at any time.

When the Define Scripts dialog box appears, click New.

When the Edit Script dialog box appears, type Update Serial Number for the script name.

In the step list on the left, double-click Show All Records in the Found Sets section to add it to the script-assembly list.

You want to Show All Records to make sure that your records include the one with the highest serial number.

In the step list on the left, double-click Sort Records.

In the Script Step Options section, check the “Perform without dialog” option (Figure 4.17), then click Specify.

Figure 4.17. When you select “Perform without dialog,” you prevent the script from pausing for Sort criteria.

When the Sort Records dialog box appears, click Clear All to remove any previous Sort fields. Double-click the ID Number field in the field list on the left. Click OK (Figure 4.18).

Figure 4.18. The database will be sorted by the ID Number field when the script is run.

When you return to the Edit Script dialog box, the Sort Records step displays the Restore option.

In the Navigation section of the step list on the left, double-click Go to Record/Request/Page. In the Script Step Options area, choose Last from the Specify drop-down list (Figure 4.19).

Figure 4.19. Since the database is sorted by the ID Number field, the last record contains the highest serial number.

In the Fields section of the step list, double-click Set Next Serial Value.

In Script Step Options, check the Specify target field option. When the Specify Field dialog box appears, choose ID Number.

In Script Step Options, click the Calculated result: Specify button (Figure 4.20).

Figure 4.20. Set Next Serial Value has two script options that let you create a calculation and specify the field that you'll use to hold the calculation result.

When the Specify Calculation dialog box appears, double-click ID Number in the field list on the left.

Click the + operator button and then type 1 (Figure 4.21).

Figure 4.21. Add one to the highest serial number in the database to get the next value.

This calculation tells FileMaker to add one to whatever is currently the highest serial number.

Click OK three times to finish.

Whenever you delete multiple records, you can run this script to ensure that the serial number is updated.

✓ Tips

  • This method won't fill in gaps in the serial number sequence—something you should never try to do. Related records in other files might be mistakenly connected to a new record with the old serial number.

  • If there is a Sort in use when you create the script, that order will be shown when you choose the Sort script step.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint