3 Replies Latest reply on Jul 7, 2010 4:19 PM by philmodjunk

    Using two independent auto-generated serials in one table



      Using two independent auto-generated serials in one table


      Hi all

       My situation is this:

      Records in my solution will not be deleted after being committed, instead rather than deleted, they will be archived. This is for auditing purposes.

      I want to use an auto-generated serial for my primary key that is generated on record creation or request eg kp_customerId. This will be used for defining relationships only and not viewed by the user.

      I would also like another serial that has meaning in the 'real world' such as CustomerID eg CUST000001 to be created on record commit. This will be sequential with no gaps i.e if I have ten customers, they will have ID's CUST000001 through to CUST000001. I hope this makes sense.

      Is it possible to have two auto-generated serials to running parallel in one table?

      If not I am thinking of using a global variable to hold the last used CustomerID that is incremented when a newly created record is comitted. This value will be auto-entered in the CustomerID field again when a newly created record is committed.

      Anyone got any ideas on how I go about this using the simplest solution?

      I am using Filemaker Pro Advanced 10.

      Thanks in advance



        • 1. Re: Using two independent auto-generated serials in one table

          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.

          • 2. Re: Using two independent auto-generated serials in one table

            Thanks Phil

            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.

            Thanks again



            • 3. Re: Using two independent auto-generated serials in one table

              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.