Jump to content

Assigning unique serial numbers to multiple records


This topic is 5608 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5608 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.