On the Automatic numbering tab, which is present when you have selected a new or existing data dictionary field in a database and set the Type of this field to Automatic numbering field on the Field properties tab, you set up the routine that automatically calculates a number for this field in a new record.

Click here for information on how to edit properties in general. On the current tab you'll find the following settings:

Autonumbering type

Select the type of the desired autonumbering:

Assignment moment

The Assignment moment specifies when the automatic number should be calculated. You can choose from:

When the automatic number is generated is not trivial. This is because once generated numbers cannot be assigned to a record again. On the one hand this means that when you remove a record with an automatically generated number from the database, the associated number will never again be assigned automatically to another record. On the other hand this means that when you let Collections calculate a new number during edit or input, so that you can see the new number as soon as you open a new record, that the number will be "lost" if you decide not to save the new record. If you subsequently open another new record, the associated number is incremented again.
If it is undesirable that an automatically generated number is lost when you do not save a new record, then let numbers be calculated before storage. However, during input or editing no number will then be visible yet: this will appear only after storage. (But when deleting an already saved record, the associated number will still be lost.)

Automatic assignment

Select this option if users may not write in, or change this field. Every automatic number for this field is unique.

Allow manual input

Select this option if users may enter numbers themselves, or change automatically assigned numbers, and the number may differ from the format string. The field must be given a unique index, as double numbers are not wanted. (If numbers are assigned manually, there is always a chance that the number already exists in another record; this is prevented by the unique index.)

Prefix string

Enter a fixed text (e.g. a general reference code) that is pasted in front of the automatically assigned number.
This option is only editable for the Using format string automatic numbering type.

Start value

Choose the first value (an integer) of the automatically assigned numbers; so auto-numbering does not necessarily have to start at 1.
Automatic numbering generates 32- bit numbers, so that these numbers can run as high as record numbers (2,147,483,647).
This option is only editable for the Using format string automatic numbering type.

Increment value

Choose the value with which the automatic number must be incremented for each new record (e.g. 1).
This option is only editable for the Using format string automatic numbering type.

Postfix string

This is a fixed text that is pasted behind the automatically assigned number.
This option is only editable for the Using format string automatic numbering type.

Number format string

A number format string is used to layout the automatically assigned number. See the ADAPL function str$() in the ADAPL reference, for the syntax of this string.

Counter

In this option, the next "free" number value for this field, as kept in the auto_numbering table in the SQL database, is displayed. You can change this value manually, and then click the Apply button to set the upcoming value differently. This is handy when you've deleted one or more most recent records and want the automatic numbering to continue from the lower highest number, for instance. The automatically set next "free" number is simply calculated from the above settings without checking in the SQL table for this automatic numbering field if that number has actually never been assigned before, so it is not guaranteed that the visible number will be assigned. Only when the automatic number is about to be assigned will Collections check if it has never been used before. And if it has, it will just increment the value repeatedly until it finds one that has actually never been used before.
This option is only editable for the Using format string automatic numbering type.

Field content options

This option is only relevant for the From field content automatic numbering type (available from Designer 7.8, for Collections 1.14 and up). This advanced way of autonumbering uses the Assignment moment, the Automatic/Manual choice, the Number format string and the Field content options list at the bottom. The existing options have no different meaning here: the Number format string still formats the automatically incremented number (always incremented by 1 in this case), for example. However, that number is only the last part of the entire value that will be put in the field automatically. That is because you can now prefix that number with the contents of one or more fields in the edited record: simply list the desired fields from top to bottom in the order in which you'd like to have them appear in the complete compound value.

So with the settings from the screenshot below for example (for an object number field in this case), a user editing a record would be allowed manual input, but if he or she would leave the field empty, then before storage, this functionality would look up any institution code in the current record, an object category and an acquisition number, calculate a new unique automatic number with up to four leading zeros (because the format string has 5 characters), paste them all together using the dash as separator between the value parts and put that value in the field. So in a particular record with an institution code AXI, an object category paintings and an acquisition number BD29377, this could result in an automatically assigned value like AXI-paintings-BD29377-00001 for a first record with automatic number.

Make sure the Number format string is long enough to be able to display all future automatic numbers. For example, if you expect the number of records never to exceed 100,000, then 6 characters is plenty, while if you think that number can be exceeded, then make the format string 7 or 8 characters long for example. (If the automatic number exceeds the format string length anyway, the automatic number inserted in the record will become a string of asterisks...)

 

You can add a new field part in the Field content options list by clicking the Add button to the right of the list. The new row will always be inserted at the bottom, so enter the fields in the correct order. Click the grey ... button in a row to select a field of choice (although you can also type a field tag or field name yourself). Then in the Separator property enter any desired separator character(s) to separate that part of the compound value from the next. It doesn't need to be a dash and you can also leave it empty as well.
Of course you can delete any row by putting the cursor in it and clicking the Delete button.

You'll have noticed the checkboxes in front of the fields in this list. This is somewhat more complicated. First understand that all the fields in the list (regardless the state of those checkboxes) will end up in the compound value in the automatically numbered field in the record if those fields have been filled in. However, by marking one or more fields via their checkbox, you indicate that only the combined value from those fields will determine if the automatic numbering of the last part of the compound value should continue where it left off (if the combined value has appeared in some record before) or if it should start anew at 1 again because the combined value is new. In other words: with the settings in the screenshot above we're actually saying that every unique combination of institution code and object category will get its own counter (with the relevant combined field contents as it name) in the dbo.auto_numbering table in the SQL database and that the acquisition number is irrelevant although it will still be part of the value that is entered in the field in the record. So to continue with the AXI-paintings example: everytime a user creates a record with this institution code and object category, will the automatic numbering continue where it left off a previous time. And for a record with different values in those fields simply a new counter will be started: a first new record with institution code AXI and object category drawings for example, will get the automatic number AXI-drawings-00001 if no acquisition number has been filled in and any future record with institution code AXI and object category drawings and maybe an acquisition number DB843 that time, will get automatic number AXI-drawings-DB843-00002. So the deselected acquisition number field in this example setup is irrelevant to the counters.

It was already mentioned that all the fields in the list will end up in the compound value in the automatically numbered field in the record if those fields have been filled in. But none of those fields need to have been made mandatory (on the screen or otherwise) per se. You can make one or more fields mandatory of course, if you really want users to always fill in a value in that field, but for this functionality it's not required. So that means that if none of the fields are mandatory, they can be left empty by the user. In that case only the automatic number will be filled in.

All in all, this makes for very flexible autonumbering functionality. Before setting it up though, do consider carefully which values should determine the counter increment, which fields should combine into a value in the autonumbering field and which of those fields should be mandatory. Also ask yourself how users will have to search on the combined values, considering how truncated searching works in Collections and if records in a result set can be sorted on the autonumbering field in a logical and/or satisfying way: it'll possibly prevent a frustrating user experience later on.