[Return to contents]
Last updated: 29 Jan 2011
(Created by Johan de Kock in appreciation for the work of Sue Sloan of XpertSS.com)
Forms often include a field to pre-number especially documents, e g invoices, etc. The auto-increment default numbering allows each new record to pre-populate with the next available number. When a user however creates a new record, but then press the "Esc"-button before completing the record, the default number is lost, causing "gaps" in the numbering of these records.
The purpose of this procedure is to bypass the auto-increment default numbering option, and thus avoid the frustrating misuse of the "Esc"-button, when creating new records (without using scripting, or external means to deactivate the use of the "Esc"-button).
I assume that you currently have created one or more forms, in more than one "APR", which use auto-increment default numbering, incrementing all numbers by one, and will proceed with the procedure to replace the current auto-increment default numbering.
The first logical step is to list all uses of the auto-increment default numbering, and group these per APR.
The second step is to create one field called "Variable Serial Number" per APR of the type "Variable" and the subtype "Number".
The third step is to edit the existing macro which takes the user to the specific form: First ensure that the macro includes the instructions "Records: Last Record" and perhaps "Page To: Specific Page". If these are not yet included, add these lines at the particular logical point. If you have used the instruction "Records: New Record", include these lines before the instruction "Records: New Record". You may also want to include the line "Browse" to ensure that the coming "Set"-command works correctly. Now add the line "Set: Variable Serial Number = Current Number + 1" directly after the line "Records: Last Record" (Current Number refers to the numeric field currently used to number the records.). You have to repeat this for all existing macro's which takes the user to the specific form.
The fourth step is optional, but a good help in any problem-solving: Add the "Variable Serial Number"-field temporarily to the particular form. You may want to change the field's properties to Non printing.
The next step is to create the macro "Set Var Serial No + 1_" to each APR: This macro includes only one command, being "Set: Variable Serial Number = Variable Serial Number + 1". You only need one macro per APR.
The next step is to create the macro's "Set Var Serial No + 1 - Form_": This macro also includes only one command, being "Set: Current Number = Variable Serial Number" (Again Current Number refers to the numeric field currently used to number the records.) (Form refers to any indication to the current form, e g "Inv" for invoices.). You need to create a macro for each "Current Number" used.
The next step is to create the macro's "Set Var Serial No + 1 - Form": The name of the macro is similar to the previous macro, but excluding the underscore (Form again refers to any indication to the current form, e g "Inv" for invoices.). This macro includes only two commands, both being the conditional "Run"-commands. The first "Run"-command will execute the earlier "Set Var Serial No + 1 - Form_"-macro on condition that IsBlank("Current Number") = 'Yes'. The second "Run"-command will execute the earlier "Set Var Serial No + 1_"-macro on condition that "Current Number" = "Variable Serial Number". You again need to create a macro for each "Current Number" used.
The next step is to attach the macro's "Set Var Serial No + 1 - Form" to a field on the particular forms: As you want to ensure that the macro's are executed, and executed as soon as possible, you need to attach each macro to a field which will most definitely be changed by the user. The choice of field to be used, will also determine the timing of the macro. Ensure that the user will be in Browse-mode when the macro is executed. Better not attach the macro to the "Current Number"-field itself. You need to repeat this step for each form being used to create a new record.
The next step is to remove the auto-increment default numbering option from each particular field: Simply go to Field Definition, select the particular "Current Number"-fields, and change to default to "None". Ensure that no other option or procedure will add a default value to any "Current Number"-fields. You need to repeat this step for each "Current Number"-field being used.
Now it is time to change to Browse-mode, and test the use of serial numbers in the new records, as well as the bypass for the misuse of the "Esc"-button. Once you are satisfied, you may remove the temporary "Variable Serial Number"-fields from the forms. Should you wish, you can now refine some of the above, e g by resetting serial numbers at specific dates, e g year-ends. Once you are done, go to your home page and Save.