FileMaker Pro 11: Advanced Tutorials
Delete Duplicates / Global Loop pt. 1
Visitors to VTC.com will be able to view all introductory videos for each training course.
Free Trial Members will gain access to first three chapters for each training course.
Full Access Members have full access to VTC.com�s entire library of video tutorials.
Learn More
Subtitles of the Movie (FileMaker Pro 11: Advanced / Delete Duplicates / Global Loop pt. 1)
The first technique for automating the deletion of duplicate records is actually going to mark duplicates for future deletion. I like to have a human eye take a look at the results since you can't undo a record delete. I'd rather have a person make the decision to delete a marked record just as a safety net. So the basic idea here is we're going to loop through all the records and compare each one. In order to do that we're going to have to sort the records, so we'll do it manually first. We'll come down, go to the Table, find the dup check field and then Sort it. And you'll see I've added a few duplicates here but the basic idea is you want to compare Record 1 to Record 2 and see if it's a duplicate; Record 2 to Record 3, check it, 3 to 4 and keep going down the list and mark which ones are duplicates by putting a value into a Duplicate field. Now before we add the Duplicate field to Manage, Database, realize that when we put a value into a field that causes record locking so if anybody else is on the system that record won't get marked as a duplicate so you really need to run this when nobody's on the system; either that, don't worry that maybe some of the records won't get marked as duplicates, it won't be that big of a deal, but if you're concerned you really need to run it after hours when nobody's on the system. So we'll go into Manage, Database, we'll add a field called duplicate. We'll create that as a Text field, click OK and we'll go into Layout Mode and temporarily add it right here so you can see the results. And we'll go back into Browse Mode and now what we're going to do is actually work with the script so let's go in there and recreate what we're talking about, so we'll make a New Script, call it Mark Duplicates. We'll start off with our Perform Script, Allow User Abort, Set Error Capture. And then the first step we're going to do is Freeze Window. That'll make the script run faster. Any loop that's going through records you can prevent that redraw with the Freeze Window and make it significantly faster. So the next thing we're going to do is Sort them. We need to Sort those records, we'll perform without dialog, dup check is already in there so we're set and then we're going to go to the first record because when you Sort you stay on the Current Record so we need to make sure that we go to the first record so we don't miss any and I'd actually add Sort Records again, we need to do Go to First Record, there we go, First, then we can begin our Loop. So I'll throw the Loop in there and inside our Loop we're going to first Set a Variable, that'll be dollar sign Dup and the calculation will be simply a reference to dup check. So, we're going to set that on one record, then we're going to go to the next record, Next and Exit after last and then we can check since we're now on the, where it's just starting we'd be on the first record, set the value here, we go to the second record and then we can test to see if dollar sign Dup equals dup check. If that's true we're going to set a field, that'll be our duplicate field and you can put just about anything in here but I'm going to make it real simple and just put an x in there. There we go. There's your entire script. So, in other words, it's going to go to the first record which, if you're wondering at this point, the first record can never be a duplicate. It's impossible just based on - when you see the results you'll understand why but, we're marking the second and third, let's say we had three duplicates right in a row. The first one wouldn't be a duplicate, we're deciding to keep that as original, the oldest one by creation and then mark the second and third for deletion, so the first one, based on that whole idea, couldn't be a duplicate, so we go to that first record, put the value in there, go to the next record, the second one, check to see if they're duplicates, maybe mark it maybe not, come back up here, we're on the second record, put that dup check value, go to the third record and go through this whole process over and over again. So let's give it a try here. We'll Save it. It's under the Scripts Menu so we're good. And what you normally do here would be to put a Find by Duplicates, by the exclamation point at the beginning of the script but I want to have it run through a bunch of different records here so I can really show you what's going on. But the idea behind that is, if you search for duplicates in a million-record database it's going to be much faster to go through what it thinks are the duplicates rather than a million records and try to check them that way. For our example here we don't have that many records, we're going to go through all of them and see what happens.
Tutorial Information
| Course: | FileMaker Pro 11: Advanced |
| Author: | John Mark Osborne |
| SKU: | 34140 |
| ISBN: | 1-936334-36-4 |
| Release Date: | 2010-07-09 |
| Duration: | 14.5 hrs / 159 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
