duplicate record and sibling records

[wouters]

Hi there,

I'm quite new to fm so this might be a noob question. I think I need to do some scripting but I'm a little stuck...

I have a table (companies) with an unique id. Then i have a sub-table (departments) that is related to the first table and displays various departments per company. Now I want to make a button that can duplicate a company (duplicating all data except for the unique id), but along with this I want to copy all the departments. So I need to copy all rows from the depparments that belong to this company too.

Any tips??

Thanx!!!

Wouter

[mr_vodka]

Hello and welcome.

Perhaps you are looking for this?

http://www.fmforums.com/forum/showtopic.php?tid/184237/

If you have a subscription to the magazine then you can read it here.

http://my.advisor.com/articles.nsf/aid/18694

[bcooney]

That technique's a bit dated. Here's a demo.

DupParentChild.fp7.zip

[wouters]

Wow guys! Thanks alot! I got it to work in a few minutes!!!

thanx!

Wouter

[comment]

This doesn't work correctly - it always duplicates the last parent's children, no matter from which record you start.

[bcooney]

omg-really. I'll check it out and post a fix. You're supposed to test your stuff?!

[bcooney]

Here's the fix. Michael, would you test it, please?

DupParentChild.fp7.zip

[Fitch]

One thing you have to watch out for with FileMaker 10 is that sorted records will maintain their sort order when you add new records.

Your file works fine as is, but if the parent-child relationship happens to be sorted in a certain way (e.g. just for demo purposes try sorting by ID descending) it will fail to duplicate all the child records.

[mr_vodka]

Can you elaborate on this a little further Tom?

From my understanding, when using a scripted process as such ( not manual ), the resorting would be done AFTER the script had finished; not during the loop.

[Bill_misc_IT]

Why do you need the self relationship Parent to Parent2? The first step of the script is to go to related record from Parent2. Is this step necessary? I tried the script w/o and it seemed to work fine. What am I missing?

Thanks.

[bcooney]

It isolates the Parent record, so that I know after I duplicate it and omit the duplicate, I'm back at the original parent.

[Bill_misc_IT]

Ok, thanks.

One more for ya...I have two child databases that are related to the parent. How can I duplicate the children from the second related database? I tried copying the script steps, but it does not work since I'm not starting from the correct parent record.

Thanks in advance.

[Fitch]

I spoke to soon. This is not something new to 10, it's a behavior that FileMaker has had for quite a while:

When your found set is sorted, then duplicating a record puts the new record directly below the original, whereas when the set is unsorted the new record goes to the bottom.

Yet another reason I tend to avoid sorted relationships.

[Fitch]

In this example file, your script ends up on the new record, not the original parent. I could see where you might want to do that in some cases, but Bill is correct, the script works with or without the Parent2 relationship and corresponding script step.

[Fitch]

Well that's a perfect example Bill of why you *would* want to isolate the original parent. Then all you'd have to do at the end is select window or go to layout (depending on whether your tables and layouts are in separate file) and you're back on the original record.

However, you'd want to remove the script step at the end that goes to the *new* parent record.

Another approach would be to capture the original parent ID in a variable at the top of the script and then use that to search for it after duplicating. I'm likely to do it that way to avoid an extra TO on the graph, but Barbara's way might be a little faster to implement.

[Søren Dyhr]

How can I duplicate the children from the second related database?

Since you're on a mac, might it be you're so lucky the solution only are going to have mac clients as well?

if so...:

http://tokerud.typepad.com/filemaker/2006/06/new_record_with.html

Which will require a layout extra, where none of the calc'fields nor autoenters are visible.

--sd

[Søren Dyhr]

It isolates the Parent record, so that I know after I duplicate it and omit the duplicate, I'm back at the original parent.

Isn't it more a question when you perform the GTRR(SO) ... Look above!

--sd

[Bill_misc_IT]

So, how do I return to the original parent in the script, after the first #Check for Children sequence, so that I can start a new #Check for Children script for the additional table?

"Go to related record" sends me to the New Parent, since the kf_ParentID=$newParentID. I need to return to the original parent.

[Fitch]

I mentioned one way to return to the original record (via a Find), and Barbara already showed you another way (isolate via GTRR and Omit the dupe). I also mentioned that you'd then want to remove that last script step.

Søren showed a different approach, where you go to your related children first and then back to your original parent, so you don't need to go back to it after creating the dupe. However, this leaves you on the dupe record, not the original.

I decided to demo yet another way, which is to create the dupe in a new window.

Download the attached file and you'll see how it works.

DupParentChild.zip

Edited February 25, 2009 by Guest

[Søren Dyhr]

The scripting can get more tightness, by using the new:

http://fmhelp.filemaker.com/fmphelp_10/en/html/scripts_ref1.36.40.html

...turning both loops into on single "wrapper"

--sd

[bcooney]

I never meant this to be the ultimate example, I did it up in 5 mins! The best approach is the New Window idea, as it doesn't disrupt the user's found set.

I was going to post a new demo, but you beat me to it. :

[Søren Dyhr]

On mac is another way to maintain the found set - as above shown, provided the autoenters are set to prevent modification, as in Barbaras template.

The embedded applescript comprises only 3 lines:

set aVar to current record

go to (create new record)

set current record to aVar

The really neat thing is that this script deals with layouts providing even several portals as long as each portal has a scroll bar attached, otherwise will the copied be truncated to the visible stuff in the portals.

--sd