FileMaker Pro 11: Advanced Tutorials
Delete Duplicates / Compare & Contrast
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 / Compare & Contrast)
I want to spend a few minutes comparing and contrasting all of the duplicate record techniques. It's not that we didn't do it while we were working on these techniques. Really, the point I want to make is that this is so important and I don't want to gloss over this. I want to make sure you understand, I based my entire 20-year career on the Compare and Contrast method. You want to make sure that you understand how it works and that you implement it because it'll make your solutions much better. So, you may think this is boring or not important, you want to get into, do more cool calculations, well, you have to have meaning and understanding and thought behind your calculations and your scripts and this is what gives it to them. You have to think about what's the best way to implement it. So, bear with me. Let's go over this, really listen to this, take it in, you know, imbibe what I'm saying here. So there's a bunch of Duplicate Record techniques that we're going to go over and let's go to the first one. The first one is the Global Loop and we really didn't program this, we just mentioned it. We really did the Variable Loop, but we mentioned and I want to go over the positives of it and the negatives. It's compatible with mixed environments, 7, 8, 8.5, 9, 10 and 11. It's easy to implement and I think that maybe because I've done it so many times, I've done so many record loops with Global fields and things like that that it's just, it's almost part of my DNA. However, it is slow. Putting values in a Globals over many records is slow and it does require a Global field which clutters up Manage Database, so I think the best thing about it is, if you need something compatible across all versions of FileMaker, the .fp7 format, then this would be one of your choices because the next one, the Variable Loop, even though it's much faster than a Global Loop, especially with many records, it did come in in Version 8 so it's not compatible in a mixed environment of all versions or if you have a commercial version of your solution that you're putting out on the Internet for people to download. So it is faster, much faster, it eliminates the Global field so you don't clutter things up and it's just as easy, in my opinion, as the Global technique to implement. Then we have the GetNthRecord Loop. It reduces the number of script steps and, in other words, it's essentially the same as the Variable and Global Loop it's just that we don't need a Global or a Variable, we do everything inside the IF statement. We say look at the previous record. So, it's a little bit easier to read and faster, as long as you're comfortable with GetNthRecord, otherwise it may not be easier to read and faster. Well, it could be faster, it's possible, because you have one less script step, but it's not as easy to implement but once you get used to GetNthRecord it probably is as easy to implement. It's up to you. It's, you know, kind of a back and forth thing. And it's also not compatible in mixed environments because it came in in Version 8 as well. Then we have the Replace Field Contents version 1, the user relationship. Now generally Replace Field Contents is faster than a loop, generally twice as fast and your script is only one step long and Replace also stays on the same record so you don't have to open a new window, loop and then close the window. And you don't need any sorting, that's because it will do all of its work through a relationship, but the negatives that are really related to those positives are it requires a relationship and relationships generally are slower processing than the current table or current record or fields on that current record and it also ignores the Found Set, so that could be a positive or a negative, it all depends on how you look at it. Then we have Replace Field Contents 2. Now we sped it up by removing that relationship so it will definitely be faster than the Looping script. It's only one step - well, actually, it's two steps, you have the sort, so that does slow things back down when you have sort, but the loop has a sort also, but the Replace Field Contents 1 with the relationship doesn't need a sort so you've got to juggle that. It stays on the same record just like Replace Field Contents 1. It doesn't require relationships, you don't clutter things up, but you do need sorting, so. And then we have the Export by Group and the only reason I included it inside here is because it's cool. I wanted to show you how the Group Subsummary feature on Export works. I wanted you to see how it works and show it in, maybe not just OK, here's how you do it and here's how you open it up in Excel, I wanted to show it by implementing, you know, a cool feature because you can actually use this feature not just for getting data into Excel but for actually creating features. So, it's cool, but would I use it? Probably not. You have to export the entire table, you have to delete all records - there's all kinds of hassles going on there. It could be very slow because of that export and the import, so it's really, really not a viable solution in my opinion. And then we have our last technique which doesn't delete duplicates, it prevents them and that may be a bonus for you. You don't have to go through and worry about deleting them. It doesn't require script, it does require a relationship although it may be, you know, negative because you may already have this relationship and the duplicate record is not displayed. Well, that's a negative but we'll actually correct this later on when we cover Script Triggers, so don't worry about that. Even though we can't go say, well, let me look at that duplicate record, maybe it's not really a duplicate, maybe there's something slightly different about it, you can't really go visit it. And, in general, Validation is annoying. When you're doing data entry validation messages just pop up. People are just trying to get their job done and maybe in this case this is an exception. You know, I always say Validation is annoying just to say that only pick it when you really need it. So, again, what you want to do is make sure you always compare and contrast the different techniques. Have a lot of tools on your belt, determine which tool is best for the job at the moment.
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
