Duplicating a Found Set of Records

[Vinnyg]

This may be one of those "so obvious it's painful" questions, but here goes. Is there a way to easily duplicate a group of records?

I tried a looping script, but it was very cumbersome. I also tried finding the records, exporting them and importing them back in, but that's slow.

[Reed]

Export/Import is probably going to be your best bet. I haven't really experienced this to be slow, but I guess that's a matter of opinion...

If you don't want another file to be generated, you can create a temp table in the same file, import the found set into that table, and then import back into the original table from the temp table. Should be a pretty fast script.

How many records are you trying to duplicate at once, and how long is it taking? Do you have a lot of records in the system currently with many indexed fields?

[Genx]

I often have to do this when "converting" things within my database.

Freeze Window

Unsort Records

SetField[table::gRecordStore = Get(RecordNumber)

SetField[table::gFoundCount = Get(FoundCount)]

Go to record[ first ]

[color:blue]Loop

SetField[table::gRecordNumber = Get(RecordNumber)]

Duplicate Record

Exit Loop If[table::gRecordNumber = table::gFoundCount]

Go to Record[table::gRecordNumber + 1]

[color:blue]End Loop

Go to Record[table::gRecordStore]

sort records

Make sure gFoundCount and gRecordNumber are both number fields set to global storage.

Then if i have to change the foreign key ill just go to gfoundcount + 1 and loop.

~Genx

Edited February 25, 2006 by Guest
Script Revision

[comment]

Test your suggestions before posting. Shooting from the hip does not help - it only wastes everybody's time.

[Genx]

Ok, i forgot one step --> Go to first, and also go to record should have been without dialogue, but other than that it works fine, i hardly know what your complaining about comment.

~Genx

test.zip

[comment]

Mostly, I am "complaining" about your script not working correctly when records are sorted. There's also a minor point about Get(CurrentRecord) - I don't have this function.

[Genx]

Sigh, recordnumber. lol oops, ive been defining my variables as current record when i do this type of thing, so its turned into a bad habit, i keep trying to call a non-existant function. Sorry about that comment... I never noticed the sort issue because i always work with GTRR to a layout that the user has no access to, can you please check the revised script to see if its correct comment, you are after all a guru

~Genx

[comment]

No it isn't.

[Genx]

Well could you maybe suggest a way to fix it and tell me why it isnt working please? I am just learning and would appreciate some help.

Thanks,

~Genx

[Oldfogey]

1. I am curious, Vinnyg, why you say export/import is slow? I've been using it for ages to duplicate many records and speed has never been an issue.

2. Comment, I have to disagree with you about makng sure a script is correct before publishing provided you say it's only a general approach. Surely the general idea is the thing? Let the inquirer learn a bit more by playing with an incomplete script.

[Genx]

I don't know comment, could you point out the flaw please because if there is one i need to know so i can change my own scripts appropriatley.

Thank you

~Genx

test.zip

[Reed]

It doesn't work when sorted because the placement of the new record is not the same in a sorted found set as compared to an unsorted found set. Use your script debugger and and see the difference.

To fix this you could check whether the found set is sorted using the Get(SortState) function, and then have two different loops (or call one of two different subscripts) depending on the result.

Or of course you could unsort the found set prior to duplicating, or use the import method...

[comment]

I have to disagree with you about makng sure a script is correct before publishing provided you say it's only a general approach.

I don't mind - provided the general approach is correct. Or, if you haven't tested even that, say it's just an idea that might work or not.

[Genx]

I did that already reed before comment said it was wrong the second time, see the "revised script" in my original post...

~Genx

[Genx]

In the mean time comment, point taken about the "general idea", sorry, but even though i forgot to unsort surely my "general approach" was still correct?

~Genx

[comment]

Play with the attached file. Try duplicating a sorted set, then an unsorted set, and see the difference. Add your script to the file, and compare the results in both cases.

duplicateRecords.fp7.zip

[Genx]

Ok, i officially give up trying to help with scripting, i suck. Ill try again in a few months hehe, thanks for the file comment.

~Genx

[Søren Dyhr]

Almost needless to say that's it's one of the few areas where Applescript really makes a point, that not easily have a native pendant...

tell application "Filemaker Pro Advanced"

duplicate every record of document 1

end tell

Due to the identifier "document" is only the found set handled!

I can't recall any other way to make a set beyond 16000 records, fairly easy - than:

tell application "Filemaker Pro Advanced"

create record

repeat 13 times

duplicate every record of document 1

end

end tell

--sd