Assigning unique serial numbers to multiple records

[Kenyon Reg]

Need a little more help. Was wondering if anyone out there knows how to assign a serial number to multiple records using a script? Ideally, I's like to search for records with identical criterial and click a button and give those records the same serial number. Any thoughts please?

[Fenton]

The Replace command, in the Records menu (or script step), can put the same value into a field for all records in the found set. The default choice of the value to use is whatever is in the field in the record where you click into it. That is often convenient. It can also use a calculation, another field, or related field.

However, remember that: 1. It will do the whole found set, usually faster than you could stop it., and 2. There is no "Undo" for Replace.

Also (semantics), if you're putting the same value into multiple records, it is not really a "serial number", at least it is not in that table, though it may have been one in its original table.

[Kenyon Reg]

Thanks for the reply Fenton. But when I do another search for a another group of records, I want that grouping to have the sequential number thats next in line. Any thoughts on how to do that?

[Fenton]

You mean next in line to those you did last. Well, there are a few ways to do that.

1. Create a constant or Cartesian self-relationship, which will the same for all records. Sort the relationship descending by your number. The relationship will return the highest number in that field. Add 1. This will work reliably, but will get slower if you have many thousands of entries.

Be sure to commit records before doing it again, if scripted.

2. Create another table just for these numbers, with an auto-enter serial number field. Each time you do the routine, create a record in the table, and get the serial number. This would undoubtedly be the fastest method, but requires that you don't manually bypass the system and neglect to update the little table.

Probably number 2 is only needed if you have hundreds of thousands or millions of records.

[Vaughan]

Option 2 is quite easy to implement and I'd suggest is the preferred option. It has the most chance of not breaking in multi-user environments.

[bdam]

What do you guys think of this idea. It's sort of extrapolated from Fenton's #2

Rather than create a record in the new table each time you increment simply have one record and do the increment yourself. I have a hidden layout with just the project id shown. In the script I go to that layout, increment the project id, commit the record, and set a variable the new project id. I then create my new record in the other table.

The major concern here would be if you had a great deal of users creating records creating a race condition. You also have to make sure they always use the script. However, neither of these are different than from creating a new record.

[comment]

Actually, there IS a difference - IF you create the new record through a relationship.

Let's say you have a group of records in table Data, with a common value in field Category. You define a relationship to a Numerator table:

Data::Category = Numerator::Category

with record creation allowed on the Numerator side.

The Numerator table has two fields: Category and SerialID.

The script is simply:

Set Field [ Numerator::Category ; Data::Category ]

This creates a new record in Numerator, issuing a new SerialID to all Data records sharing this Category. If this category already has a serial number, the script does nothing.