You can't have two auto-entered serial numbers in the same field operating independently from each other as each will increment automatically when the record is either created or committed. You can have a second related table with an auto-entered serial number field in that table. You can then control when a serial number is generated by when you create a record in this related table--that sometimes works for these situations, but I see a few problems with your current scheme.
You've indicated that you want the second serial number not to have any gaps created when you remove a record by archiving it. So you have three records Cust01, Cust02, Cust03 and then remove Cust02. What happens to the ID for Cust03? Do you change it to Cust02? If so, that will require scripting to update the number for all existing records that were created after Cust02.
On the other hand, there's a function Get ( RecordNumber ) that returns the number of the record in it's current sort order in the current found set. If you only need to see this serial number in a found set of all records sorted in ascending order by your primary key serial number, you could use this function to provide the "serial" (actually sequence) number with no gaps.
In your example if I archive Cust02, Cust03 stays as is.
I was using a field auto-entered by calculation:
SerialIncrement ("Cust000000"; Serial#)
As you say everytime a record is created or commited the serial is incremented.
First I used the auto-generate on creation; if If the user decides not to commit the record and clicks a button "cancel" which deletes the current record, the serial will have been incremented and therefore my meaningful CustID field would have a gap next time a new record is created.
I then tried to auto-generate the serial on commit. This posed a problem that on my layout I have fields from another table (say Addresses) which allows creation of record via relationship, a new record could not be created as no primary key (the serial) had been generated at that point in the original customer record.
What I did then was revert back to auto-generate serial on creation, and used a script that was triggered when the "cancel" button had been pressed. This script then decremented the serial to reset it back to its state before the deleted record had been created.
Although this resulted in a solution of what I wanted to achieve, I decided it's not a wise idea to start decrementing serials that are used as a primary key. Maybe you could advise me on this?
The steps in the script for cancelling was something like this:
SetNextSerialValue [Customer::Serial#; Customer::Serial#]
Delete Record/Request[No dialog]
I am quite happy though to use a purposely made related record's Serial, as you suggested, as a solution to hold the meaningful CustID.
In a single user environment, the set next serial value would seem OK, but might cause problems in multi-user systems. The related table approach avoids this issue as far as I've been able to analyze it. It works like this:
Define a related table, Sequence, linked by your main table's primary key serial number. Enable creation of related records for the related table in this relationship. Define a serial number field, Seq, in this table.
Now use a single script step to trigger the assignement of this secondary serial number:
Set Field [Sequence::ForeignKey; MainTable::PrimaryKey]
Before, this step is performed, Sequence::Seq will be empty. Afterwords, it will contain the next number in the series.