Duplicating in a Record in FileMaker is very easy…. but a found set???? That takes a little bit of thought. Here is how I would approach the task.
Editor’s Note: The use of quadruple question marks is a clear violation of Six Fried Rice editorial guidelines. In this instance, they were left in place so that we could publicly ridicule Jesse. We hear he was planning an entire line of exclamation points, and a few OMG!!!’s in his next post.
## Without the Duplicate
Whenever someone asks me the question *How can I use `Duplicate Records/Requests` to duplicate a found set?*, I typically respond with… **You Don’t.** Seriously, don’t do it unless you are up against a wall and the burninator is coming. I suggest rather then using the `Duplicate Records/Requests` step, use a script, a field and a special relationship. It may seem like a lot of overhead but I think it gives you a lot more control over the process.
##Step 1: Create the Field
We are going to need a field so this whole dealio will work. Create this field in the table where you want to create your duplicate records. Name it something simple like `ORIGINAL_RECORD_ID`.
##Step 2: Create a Relationship
Now that we have our field created we need to create a relationship between our duplicate record and the original. Use the unique id of your table and the field you just created as your keys in this new relationship. You’re going to need a new table occurrence as well, in my case I named it `Original Record`. It should look something like this when you are done:
Once we set the ID of a record in our `ORIGINAL_RECORD_ID` field we will be able to use this relationship to pull the data we want into our new record.
##Step 3: Write your Create Script
The create script is pretty simple. It will require that a script parameter that is the unique `ID` of the record you are trying to duplicate. Just grab that Script Parameter using the `Get( ScriptParameter )` function and set it into the field we created in step 1. Finally, pull over all the data you would like to collect from the original record and set it in the corresponding fields in the new record. Here’s my script when it’s finished:
New Record/Request
Set Field[MY_TABLE::ORIGINAL_RECORD_ID; Get(ScriptParameter)]
Set Field[MY_TABLE::SOME_STUFF; ORIGINAL_RECORD::SOME_STUFF]
Set Field[MY_TABLE::SOME_MORE_STUFF; ORIGINAL_RECORD::SOME_MORE_STUFF]
Notice my script doesn’t include all the fields in my table shown in the image above. I’ve left out the `STUFF_I_DONT_WANT` field. This wasn’t a mistake it’s just that I don’t need the data in that field. When you have a table with 50+ fields the odds are there is a lot of data in those fields that you don’t want, so why bother keeping it? This is that control I was talking about
>Note: Normally my create scripts include a Go To Layout at the top of the script and a Go To Layout[Original Layout] at the bottom of the script to make sure I have the correct layout context. I have left this out for simplicity
>Double Note: So you hit the wall and would just like a quick and dirty duplicate, then just use the Duplicate Record/Request step here instead of the create script. Don’t say I didn’t warn you though
#Final Step: Create your looping script
The create script does all the easy stuff. After all it’s really just copying over a bunch of data. Your looping script makes sure that you don’t mistakenly duplicate records multiple times nor end up in a never ending loop.
The trick with this loop is that when you create a new record it gets added to the end of your found set of records and makes you lose your place in the list. This can be VERY annoying. To combat this petulant behavior you have to arrange your looping script properly. There are many ways to do it, this is just one of my favorites.
The first step in script is `Go To Record/Request[Last]`. This seems counter intuitive but since the new record step will put you at the bottom of the list we might as well start there and work up instead.
>Hint: If you have a particular order you want your records to appear in, sort your records so that the last one is the one you would like to be first in your newly created records
So we’re on the last record now we are going to loop our way back to the top so add your `Loop` step. FileMaker will graciously throw an `End Loop` in there for you for good measure. The steps between those two will get repeated until you tell FileMaker to cut it out… but we’ll get to that part later.
Now we have a loop and need to duplicate some records, so throw that Create script you wrote above in next and *don’t forget* to specify the unique id of the record in the script parameter.
Once your create script executes, there will be a new record at the bottom of the found set and you will be sitting right on it. Well, we don’t want to duplicate that record, we just created it! In truth we really don’t want to duplicate previous record either because we just duplicated that one. So let’s get those pesky records out of our list.
The simple way to do this is to use the `Omit Record` step. This handy script step is aptly named as it removes the current record from your found set. It doesn’t delete the record, just makes it so you don’t have to worry about it anymore. Since we have two records to get ride of we will need use the omit record step twice.
>Note: You can use the `Omit Multiple Records` step as well. Just specify two as the number of records to omit and make sure to click the perform without dialog box or your going to be clicking the Omit button quite a few times.
Our script now has you right were we wanted…. on the next record to duplicate in the list. Just one more thing that you probably don’t want to forget. The `Exit Loop`.
The way our loop works it’s just moving slowly up the list and getting ride of the duplicate records and the original record it comes from. Eventually there should be no more records left in the list. Well to figure out how many records you have left in your list FileMaker provides a function `Get( FoundCount )`. Pretty self explanatory, the function returns the number of records that you have in your list. So we want to exit when `Get( FoundCount ) = 0`. Place that Exit Loop step at the very top of your loop and you’re all set.
>Note: The reason it’s placed at the top of the script is we don’t want to do anything unless there are records to duplicate in the first place
You’re script should now look pretty much like this:
Go forth and duplicate till your hearts content.