I am currently designing a simple product database and my quandry is this. There is a item code that should count up from 1 to a number anywhere from 5 to 95. I then should be able to set the number back to one and the subsequent new records should start counting up again. I know about the increment ability in the Auto-enter options but it is a bit of a pain to go into define fields everytime I want to start at 1 again.

Is there a calculation I can use like Max(field) + 1 that won't throw a circulation error. The product items are in groups so I can query just the group I'm after and find the last (and highest) record of this field.

Please let me know if you need more details.

Much appreciated.

Chris.

There are a few ways to do this. But you're using FMPv5.5 or above, the simplest solution would probably be to define a script, with the following step in it:

Set Next Serial Value ["YourSerialField", " "1" "]

and attach it to a button with an appropriate label.

I'm using Filemaker Pro v6. Can you quickly step me through adding this script. I can't find anywhere to paste this. Also haven't been able to find the functions "Set Next Serial Value" in any of the script options.

To take a guess, do I make a new script then select 'set field' then copy this script into the calculation area?

Thanks for your help.

Chris.

haven't been able to find the functions "Set Next Serial Value" in any of the script options

The "Set Next Serial Value" script step is under "Miscellaneous" if you're viewing by category.

Hi Chris,

Set Next Serial Value is a script command. It's like Set Field, but not the same. It appears seventh from the bottom of the list in FMPv5 (in the miscellaneous section, as mentioned above).

To get it set up, you should first make a back-up of your file, then:

1. Go into ScriptMaker from the Scripts menu, enter a name for a new script in the text box at the bottom and click the button labelled 'Create' at the right-hand side.

2. Scroll the list of commands at the left of the Script definition dialog down to the bottom until you can see the Miscellaneous group of commands. Double click on the 'Set Next Serial Value'. It should now appear at the toof the script definition at the top right.

3. Click on the Set Next Serial Value [ ] command in the pane at top right to select it. Then click on the button labelled 'Specify Field...' at the lower left and select your serial numbered field.

4. With the Set Next Serial Value [ ] command still selected, click on the button lablled 'Specify...' at the lower left. A calculation dialog will appear. In the calc area, enter a 1 in quotes: "1"

5. Click OK to accept the settings and save the script, and done to back out of ScriptMaker.

6. Go into layout mode and place a button on in an appropriate place. In the 'Specify Button...' dialog, select the 'Perform Script' action, and choose the script name you entered (at step 1 above) from the list of scripts at the right of the dialog.

7. Name the script (eg 'Restart serial numbering').

Once you've completed the above changes, some testing would be in order. Make sure you know what the next serial number is to begin with (so you can reset it later) and then click on your button. Then go into define fields and make sure that the next serial value for your field is now showing 1.

Set it back to what it was to begin with and relax - you're done!