You didn't say, but there are three possibilities of what you want to accomplish:
1. You want the same related records in the portal of the original record to appear in both the original prmiary record and the new duplicated record; or
2. You want the same related records in the portal of the original record to appear in just the new duplicated record (but disappear from the original primary record); or
3. You want the related records in the portal of the original record to remain intact, and not appear in the new duplicated record.
Here are your answers:
1. SCRIPT REQUIRED + NEW JOIN TABLE- This requirement will probably either require a join table (an intermediary table that allows a many-to-many relationship between your primary table and the related records in the portal), assuming you might want more than one duplicated record, or if you will never have more than one duplicated record (i.e. two identical records at the max), then you'll need to add a second "foreign key" to your related record table (the records that appear in the portal. In either case, this is the most amount of work of all three options. It requires an additional join table, and a restructuring of your existing relationships (and updating all your data) between your two tables through that join table. Then when you're ready to duplicate your primary table, you'll dulplicate the primary table (within a script!), save the new primary key of the new duplcate record into a temp variable, then duplicate your join table's record, then update the foreign key field of that new join table record with the temp variable of your primary key's value. This is not a trivial task for a beginner (and I'm guessing this is why you asked). An experienced FM developer can probably do this in 10-15 minutes. Give yourself an hour or two if you've never done this before (primarily for learning about 'join tables')
2. SCRIPT REQUIRED - This option isn't quite as hard for a beginner. Experienced FM person can do this in about 5 minutes. Plan about 30 minutes to 1 hour for beginner. Write a script that does the following:
- Duplicate primary record
- Save your primary key (_pk) of the new duplicate record into a temp variable
- Go back to the original record
- Loop through all the related records (several techniques to do this)
- Inside the loop, jump to the related record and change the foreign key to the new duplicate record's primary key (stored in your temp variable)
- Go to the next record until you've updated all the related records.
- Jump to your new duplcate record when your'e finished with the loop
3. NO SCRIPT REQUIRED - This is simple (and I doubt if this was your option, or you woudn't have even asked!!) Simply duplicate the record (or hit Command-D (Mac) or Cntrl-D (Win)) or select the "Duplicate Record" menu item.
You might need to research many excellent FM Join Table tutorials if you need to acoomplish option #1 above.
Hope this info helps!!
- Gary Amstutz
Re option #1 : Why so complicated?
Here's a far simpler method to duplicate a parent record including its children (the portal records).
Study the script, it's extensively commented, and sports some basic precautionary measures.
There should be some threads dealing with this topic, but reinventing the wheel can be fun once in a while (actually, I had some spare wheels flying around…)
Thanks for the input.
This is basically what I need, exept i need to ba able to copy the same record more than once if needed. This is for an invoice system, and I would like to pick an old invoice and copy it if the job done is the same.
But does it really need to be so complicated as your description? (even though it seems simple enough) I found this old script from 2001, which seems more simple, but this is if the DB is in seperate files. Could this be transformen to the current filemaker?
The only thing you need to add to your system is a global field in the child file which I'll call gNew_Parent_ID.
In my example scripts below, Parent_ID is a field in both files that the relationship is based on and Child is the name of the relationship in the Parent file.
In the child file, create the following script:
Go to Record/Request/Page [ First ]
Set Field [ Parent_ID, gNew_Parent_ID ]
Go to Record/Request/Page [ First ]
Exit Loop If [ "Parent_ID = gNew_Parent_ID" ]
In the parent file, create the following script:
Go to Related Record [ Show, Child ]
Set Field [ Child::gNew_Parent_ID, Parent_ID ]
Perform Script [ Sub-scripts, External: "Child" (Duplicate Found Set) ]
Thanks for the quick response. I am sorry I did not make my request clear enough.
What I need is solution 1, but I would like to calculate the result. What I meant with "I am not very strong in calculations" is that i problably will have problems making my own calculation based on a description. For my level I need something more specific and the study this to get smarter :-)
I have worked with FileMaker for 5-6 years, but never made big calculations.
Anyway... Please see my answer to Erolst below...
I'm not entirely sure what calculations have to do with this, but never mind…
There's no need to transform your ancient script into modern FileMaker; it does essentially the same as mine, except it uses a global field rather than a variable (this being from the pre-FM7 era), employs a different method to exit the loop, and of course needs two scripts instead of one (from when one file = exactly one table).
Anyway, here's a less “complicated” version that allows you to duplicate the same record again; it only checks on the presence of child records.
replaced attachment with bug-free version using proper rel. structure
This is it. Simple enough for my the understand and gets the job done! Perfect.
I thought this should be calculated, not scripted thats why I kept rambling about that. Sorry for mixing the terms.
Thanks for you help!
You asked, "Option 1 - Why so complicated?"
My original option one was assuming Kasper wanted to keep the original child records intact (and not duplicate them), but only duplicate the parent record. Then have that new duplicate parent record show a relationship to the same original child records. (This wasn't clear in the OP, so that's why I asked for clarification). This would require a join table to allow multiple parent records to have the same children, and multiple children to have many parents (i.e. a many-to-many relationship)) That's why it was so 'complicated'.
However, your suggestion appears to simply make copies of not only the parent records, but copies of the child records, too. My experience has been that this is rarely desired in the 'real world'. Usually if someone wants to make a copy of a parent record with a portal, they don't want copies of all the child records (i.e. duplicate copies of the related records, too) in the related table. I was assuming he wanted to setup a relationship with those existing related records to the new duplicate record. Correct me if I'm wrong, but if that is indeed what Kasper wanted, my suggestion would have been one of the simpler approaches, I believe. What you offered was truly an Option #4 (duplicate the parent records, then duplicate all the child records), which I've rarely had a need to do, since this results in duplicate copies of the related records. But had I originally known this was for an accounting system, I might have guessed that this would be what Kasper wanted (instead of, say, duplicating a department or 'team' record, and not desiring to duplicate all the records of the employees in that department or all the members of a team. In this latter case, you'd need a join table, which is what I was describing in my original response)
By the way, I tried your FM file you posted, went to the first record, and hit "Duplicate". It appeared to Duplicate the last record, and not the one that was the current record. I looked at your code and it goes to the "previous record" in order to duplicate the child records. If the user is on any record besides the last record, then this will duplicate the children of the 'wrong' parent record. (Kasper needs to be aware if this if he's duplicating your logic). Instead, you should track where your original record was (or it's primary key), and ensure you go back to that original record before copying all the child records if you indeed want option #4.
if someone doesn't want duplicates of the related records (e.g. “a department or team record”), then a simple cmd-/ctrl-D would be sufficient. Since Kasper mentioned “portal”, for me it was a given that he wanted to duplicate the portal rows as well.
Our perceptions of ”real world“ seem to differ a bit; I'd be hard-pressed to think of a reason why you want to sever the existing ties between a parent and the children.
Creating new transactions or state information based on old one doesn't mean you just throw away those old ones.
A new order based on an old one doesn't mean you want to invalidate your old order; I have a movie database where I record which regular actors (and crew) appear in specific series episodes, so I duplicate an old episode and adjust it a bit (different guest stars, other director etc.); doesn't mean I want to lose my old cast information. You mentioned a sports team; good example: recent match, next match, with an identical (or almost) identical lineup.
btw, my approach is not number 4: you wrote in your post:
“1. You want the same related records in the portal of the original record to appear in both the original prmiary record and the new duplicated record” […]
which sounds to me an awful lot like duplicating a parent record and all its children.
And I still don't understand why you need a new table or alter the existing relationship; from the perspective of the parent record, it doesn't matter one bit if the children are in a “child table” or a “join table”, i.e. if it is a one-to-many or a many-to-many relationship. If the latter is needed but not implemented (i.e. the children are not represented as keys, but as redundant textual data), that may be a regrettable structural flaw, but has nothing to do with the task at hand.
#1. Duplicate the parent and its children: needs nothing more than the parent table and the child table, as you can see in my example.
#2. Duplicate parent record only and re-assign existing children to the new copy: needs nothing more than the parent table and the child table.
#3. Duplicate parent record only. Needs …well …
#4. No such approach.
So I still think that your descriptions and approaches are not “complicated”, but complicated.
BTW, you may want to read the code (and the comments) in the sample file more carefully: I incorporated two ways to go about the duplicating business in the parent record. In the first one, the original record (the current one!) is isolated, then duplicated, then the script goes to the previous record (the original) to do a GTRR, then duplicates the children, returns to the parent table and goes to the last record (the copy).
I believe you didn't understand my post. When I said "You want the same related records in the portal of the original record to appear in both the original primary record and the new duplicated record”, I meant that literally. In your case they were not the EXACT same child records, but you made copies of them. In my case, the EXACT same child records pointed to both the original and new duplicate parent record. There's a big difference.
You stated you couldn't understand why anyone would want to do this. Let's say a political campaign had 5 teams of volunteers, each with 20 volunteers. So the parent record is a 'team' table, and each volunteer is a record in a volunteer table. Now let's say the user of your system wanted to make another different volunteer team that had 90% of the same volunteers on the new team. Why manually create a new team record, then manually add 18 of the volunteers to that new team? Instead, simply duplicate the team record, and point the same voulunteer records to the new team (instead of duplicating all the volunteer records, which I'm sure you would NOT want to do). My solution was based on this example, which is very common in my world of FM development. Even in accounting, this would often be the case (duplicating a check, you wouldn't want to duplicate all the accounts that contributed to that check's balance). In the latter case, a join table would be required, too!
And regarding your code, as I mentioned, I only looked at your second code example. It did NOT have two cases in it (after reading this, I went back and perused your first example, and that one did). Your second example, which is what I was afraid Kasper might use, made no mention of the fact that your code requires the user to be on the last parent record for it to work correctly. Here's the code (with no comments pointing this out). If Kasper is new to coding, this could be a time consuming head-scratcher for him. I was just trying to help him avoid wasting time on that potential bug...
We're obviously at a stand off here. I'm not looking for a fight. I'm simply wanting to help Kasper.
If Kasper is wanting to duplicate not only his parent record, but to have duplicate copies of his child records, then your first posted example should work nicely for him. And I sincerely thank you for pointing out that new option (which wasn't included in my origal post). Since he's wanting to duplicate an invoice (which he didn't mention in his original post), then I do believe duplicating all the child records (i.e. duplicate the line items for the invoice as well as the invoice record) is his simplest and best option. But if other readers visting this post might want to have the same child records point to a new parent record (and not copies of those child records), then they need a relationship that is a many-to-many relationship, and therefore a join table will be necessary. My option #1 might be those people's best option (unless anyone else has a better solution). Using a join-table, if you built a movie database, and had another table of actors/directors/other cast members, using your technique, only your movie table would be useful in a standalone layout. Using a join-table with a many-to-many relationship (as I outlined would be required in my Option #1), then each record in the Cast / Directors table would be very valuable in that it could show (in a related portal) all the movies that a director directed (or an actor played a role in) simply be entering the cast into all the movie records (i.e. very much like IMDB works!) If IMDB duplicated all the cast members every time someone created a new movie, imagine how useless the cast table would be!
I think Kasper has the answers he needs (if not, Kasper, please let us know!) :-)
- Gary Amstutz
I guess I actually misunderstood your first post, because you suggested implementing a join table (without knowing if there wasn't one in place already), which I now take it was a recommendation for sound database design, while I read it as a prerequisite for this entire duplicating business.
If you transfer my simplified example to your scenario, then the parent table would be Campaigns, and the child table (the portal records) would be the join table between Campaigns and Volunteers. Of course you don't copy the volunteer records, you copy their occurrences in this join table (and re-assign the copies, because you still would want to know who partook in the previous campaign, and there might be payments and other data tied directly to those records).
So you're right to assume that there is an approach number #4 only if you also assume that is is taken by someone without any clue about RDB design.
Maybe using children_kp in the child table was misleading; it should also have read children_fk, because this was meant to duplicate records in a join table. Using children as abstract name for a structural component and as concrete table name is not a good idea! My next sample file will use Campaigns and Volunteers!
You're correct about the error in the second sample file; but it doesn't require the user to be on the last record by design, rather I mistakenly deleted the necessary call to the Isolate subscript when preparing a simplified version. Shit happens when you have no beta tester handy (and no decent IDE). Well, Kasper has the first version of the file to compare it to; this should help him sharpen his scripting wits (and he is free to complain and demand a bug-free version and/or his money back…).