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?

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

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!

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?

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 ]

Just add two to the count.

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

Chuck

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

The script is exactly as you have suggested.

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.