adding a serial number to a field

[walkerjk]

Here is what I would like to do:

I have an existing record, and I want make a duplicate of it.

But I want the duplicate to add a serial number to a particular field when I click the "Duplicate" button.

Let's say the original record contains a control number "AAA123_001". When I click the "Duplicate" button I would like for the control number field in the new record to display "ABC123_002" and so on, everytime I make a duplicate of that record (003,004,005, etc.).

Can I do this?

[Chuck]

When you said that the new control number should be "ABC123_002", did you mean it should say "AAA123_002"? This is totally possible.

Create a field called Control_Number_Root which is a text calculation set to:

Left( Control_Number, 6 )

For the above, this would be "AAA123". Create a self-join relationship using Control_Number_Root as the match field on both sides.

Now create the following script for duplicating a record:

Duplicate Record/Request

Set Field [ Control_Number, Control_Number_Root & "_" & Right( "00" & Count( Self_Join::Control_Number_Root ), 3 ) ]

Chuck

[walkerjk]

I guess I should edit my posts more carefully...

Yes, you are exactly right. It should be "AAA...", not "ABC...".

Sounds like what I'm looking for, and I'm about to try it.

Thanks!

[walkerjk]

OK, I followed your instructions, but after I enter the "Set Field..." line it tells me it cannot find the field "Control_Number".

What am I doing wrong?

[walkerjk]

Chuck:

Scratch that last post of mine.

It works, EXCEPT that when I click to duplicate a record, let's say "aaa123_001", it gives the duplicate the number "aaa123_000". If I continue duplicating the record, it continues on with "...001, 002, 003, etc."

In fact, it gives the duplicate the existing number minus 1; i.e., the duplicate of "aaa123_005" is "aaa123_004"

If I duplicate "aaa123_005", the new record should be "aaa123_006", not "aaa123_004".

How do I make it count up correctly from the existing record?

Maybe I misinterpreted your solution, but I think I followed your instructions.

Any ideas?

[ June 14, 2001: Message edited by: Key ]

[ June 14, 2001: Message edited by: Key ]

[Chuck]

Just add two to the count.

Set Field [ Control_Number, Control_Number_Root & "_" & Right( "00" & Count( Self_Join::Control_Number_Root ) + 2, 3 ) ]

Chuck

[walkerjk]

That works, but it's dropping the "00" now. "aaa123_001" duplicated returns "aaa123_2".

The script is exactly as you have suggested.

[walkerjk]

Again, scratch my previous post.

I added parentheses before "Count" and after "+2", and works exactly like you said it would!

You cannot know how great a help you've been!

Thanks a million.