FileMaker Pro 11: Advanced Tutorials
Delete Duplicates / Improving the Loop
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 / Improving the Loop)
Let's investigate methods for improving the script that marks duplicate records for deletion and what we're going to do is take a look at which record it's marked. Is it the newest or the oldest? Well, if we look at the record we're on, which is not marked, it has a date of 2-12-2010 and the one that's marked for deletion is 4-28-2010. Well, what if you want it to be the reverse? What if you want this one to have an x and this one to have nothing in it? In other words, keep the newest one rather than keeping the oldest one. It's just a different way of looking at things. You may, depending on your situation, want one thing or the other. I want to show you different ways to look at this script so we can exercise your FileMaker muscle. See ways to solve the problem in different ways, slightly different ways, modify the script so you really get better at FileMaker. Not that you necessarily want it any different than the original way we did it, but we want to do this to exercise your FileMaker muscles so you get better. That's all it simply is. You need to try things out in order to get better at FileMaker, so that's what we're doing here. And let's see how we can approach this. Let's go into Manage Scripts, find our Mark Duplicates and what we're going to do is change the Sort. We're going to go into Specify Sort, we're going to move in our timestamp create field and we're going to Sort in Descending order. So what this is going to do is, within each Dup Check category any duplicate records, it's going to sort them in descending order so that it goes through them in a different order. Now the newest one's going to be at the top rather than at the end, so we'll click OK. Save this. Let's go ahead and use a record that has nothing in the Duplicate field and do the Replace so we can get rid of all these different values on here. Now all the x's are gone, now we can try running our script again. We'll go back up to the top. Look at this one, we can see this one's 4-28 and this one's 2-12, because it sorted in a different direction it's actually going to mark the oldest one for deletion and keep the newest one. And there's another way to approach this. If you don't have a timestamp create you can also use a serial number because serial numbers are assigned in order and so you can sort by that serial number and have them come out in order of creation or in descending order of creation so you can have them exactly in this order that we have here. So we're going to go ahead and get rid of these x's again, so we'll do a Replace Field Contents, and let's approach this a different way. There's always more than one way to do something. I want you to see these different ways to really get your brain really firing on all pistons here. So what we're going to do is take that Sort off and we'll come in here and we're going to try Go to Previous Record. Why not? You probably wonder why it's there. Well, you can use it. We also have say, Go to Last Record. So we're going to walk through them in reverse order now and so, since they're going to be in creation order according to their categories for Dup Check that means that we'll go through, the first one will actually be the latest one added and we'll keep the most recent one and then it's really going to accomplish the same thing as what we just did. Let's see how it works. All our x's are gone. We'll try marking the duplicates again and we'll come up here and check it. So you can see that this is the oldest one, it's marked for deletion and then this one right here it's the newest one and it's going to be kept. So that's cool. We've got another way of looking at this. Let's go back into Manage Scripts and let's modify it one more way. First we're going to set it back to the way it was, so we're going to go back to First and then go to Next. Now what we're going to do is try to remove some script steps. We're going to take the Set Variable out. It's always a good idea, in my opinion, to remove script steps. It doesn't always work out perfectly. It can sometimes make the script more complicated. Generally it does make it faster, but generally even if it's more complicated there's less steps to look at. It all depends on a lot of factors, but in general, it makes it faster and easier to read. So what we're going to do, now that we don't have the Variables, we're going to actually substitute this for something else. We're going to use our friend GetNthRecord and GetNthRecord, if you remember, can look at any record in the Found Set, so we're going to look at our dup check field and we're going to look at a particular record, we're going to look at the previous record. So we're going to say: get recordnumber minus 1 and then close paren. So, if we're on record 2, it's going to look at record 1. If we're on record 3 it's going to look at record 2. It's always going to look at the previous record to what we're currently on, which is this value. Now, if we're on record 1 it's going to look at record 0, which is going to be blank so it'll work out fine as well. So let's try this out. We'll save this. Make sure to get rid of all our x's and we'll try running that script again, make sure it works. So let's take a look at it. Here's Billy Adlington. We can see that that's 2-12-2010 and we'll see this one is 4-28-2010, so it's gone back to marking the newest one for deletion but it has worked without needing that variable. So that's kind of cool. I like that. So, the whole point of this exercise was there's not vast improvements here, but they give you different ways of thinking so that you can approach another problem that you might have in a different way and actually come up with a better solution. It's about having all these tools on your belt so you can pull the right one out at the right time. If you've ever seen a carpenter with 50 years experience he has all kinds of tools and he uses the right one at the right time. He probably has 5 different types of hammers and things like that. We're trying to give you these 5 different types of hammers here so you can use the right one for the right job.
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
