Syntax

value = getcounter (<fdname>, '<counter_name>')

Arguments

value: integer

counter_name: text

fdname: FACS database name

Meaning

This function offers an alternative, advanced approach to automatically numbered fields. The difference is that an automatically numbered field must be set up in the relevant field definition in the data dictionary (specifying the format string and the automatic assignment moment), while getcounter uses named counters which do not need to be set up anywhere, of which the retrieved value must be formatted by the adapl, and the assignment moment is determined by the moment the adapl is carried out.
Note that the counter name doesn't need to be a literal value between quotes per se: a text variable or tag containing the desired name works as well.

The first time a specific counter is called, Collections will create it for you and set its value to 0 before retrieving it, and increment it with 1 in memory after retrieval, but will only store the incremented number (as in memory) in the database when a record is actually being written. You may call the getcounter function repeatedly for the same named counter if needed, without any records being written, like you would in a program loop, to increment the counter in memory only, for examle if you're looking to find an object number that hasn't been used yet. For example: let's say the current value of a counter named eventACcounter in the dbo.auto_numbering table in the SQL database has the value 30. Then the getcounter function is called in an adapl, e.g.:
EVENT_eventno = 'AC-' + str$(getcounter(EVENT, 'eventACcounter') , '######')
Then EVENT_eventno gets the value AC-000030, while in memory the eventACcounter is incremented to 31. If no FACS record is written in the adapl, the eventACcounter in the dbo.auto_numbering table will remain 30, while if a FACS record is written indeed, the eventACcounter will become 31.
There is no (user-friendly) way to reset a named counter, to set it to a different value, or to delete it, so take care when testing your new functionality: you could use a differently named counter for test purposes, for instance. Also remember to never call getcounter in your adapl unless all your conditions for correct record entry have been met, to prevent “losing” sequential numbers.
Even though you can't manipulate the value getcounter produces, you can manipulate the value that will be written to the desired field in the Collections record, by means of the adapl. Not only can you insert any text in front of the value and behind it, you could also add some fixed number to each retrieved getcounter number to establish an initial offset if you don't want your numbers to start at 0.

Named counters are global counters - Although you have to provide a FACS name in this function, this does not mean that the specified counter is associated with this FACS database only. Named counters are actually server based and the FACS name you submit is only used to identify the SQL server which contains the relevant database. Therefore, there exists only one instance of any named counter for all the tables together in a Collections SQL database. You must explicitly open your FACS database, before you can use getcounter.

Applying the newly retrieved number - The new sequential number which you retrieve with the current function can be applied in different ways: you can specify conditions under which the automatic number must be assigned, for example the condition that certain fields must be empty or filled with a particular value, or the condition that the current storage adapl is called from within a specific dataset. You can then format the number any way you like before writing the value to a database field.

The ADAPL reserved variable &E catches any errors produced by getcounter.

Example

Suppose you have an existing storage adapl, associated with the collect database. Such an adapl will be executed every time you save a record in this database. As an example, we would now like to create an automatically numbered counter for object numbers, although the target field is not that relevant*.
In this adapl you would then declare a FACS database which points to the collect database, and call it OBJECTS, for example. Next you have to open the FACS database, using: open OBJECTS. Further, we would like our object number counter to be called objno, for example; you don’t need to declare this identifier anywhere before using it in getcounter. All you have to do now to create an automatic counter in your SQL database, is call getcounter. If you have declared an integer variable called newno, for example, the call would look like:

newno = getcounter (OBJECTS, 'objno')

Result

The newno variable will be 0 if objno is called for the first time ever. If objno is called for the second time, newno will be 1, etc.

* If the automatic number has be to entered into a field which is a mandatory field, then take into account that the assignment moment of the value is not there when the record is saved, because Collections will check if mandatory fields have been filled in, before a storage adapl is executed. If the automatic number really must be assigned at the moment the record is stored, and the target field is currently still mandatory (like the object number field), then it may be worth considering making the field non-mandatory, and using the storage adapl to make sure that the field never remains empty.