FileMaker Pro 11: Advanced Tutorials
Delete Duplicates / Validating Duplicates
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 / Validating Duplicates)
All the previous duplicate record techniques attempted to delete duplicate records after they were created. This technique will prevent them from ever being entered in the first place. To accomplish this we'll create a Validation Calculation based on our Dup Check Calculation and a relationship. We actually already have that relationship. Let's take a look at it. Go to Manage, Database, go to Relationships and this is a relationship from CUSTOMERS to CUSTOMERS and we'll double-click on this CUSTOMERS Dups Portal. And the relationship between them is kp customer ID does not equal kp customer ID and dup check equals dup check. This eliminates the current record from that relationship. Now we can't use this one because it just goes dup check to dup check. That would look at itself and we could get an error when we're looking at ourself, it might locate itself, so we need to make sure we use this relationship down here. So we're going to go over to the Field section, find our Name First field and start writing our calculation. We're going to go to the Validation section, Validate by calculation and we're going to start off with a Case statement. Case open paren, hit a couple returns and now you might think that we could go through and check through Dups Portal and simply double-click on something to check through that relationship. And the problem here is that when you do Validation with the relationship it doesn't update properly, it doesn't find those duplicates in time, so we actually have to use a Lookup here. So we're going to come down here and find the Lookups, remind you about them and Lookups can be done inside of a Calculation so we'll do: Lookup open paren and then we're going to go to CUSTOMERS Dups Portal and look at the name first, because I know there's going to be something entered in there. So, name first, we'll look at that, pull that across if we find, you know, something that matches, otherwise result in a zero. That will be our default result if nothing's found. And then we'll check to see if this is equal to zero. If it's equal to zero then we're going to result in a 1, meaning it's true. In other words, if we didn't find any, if no name got pulled over, a zero got pulled over and it equals zero, then it's true. And I made a mistake here. That should be a closed parenthesis. So that's what we want. When we find nothing then we allow this entry to happen, we don't have a validation message. So it's that simple. Once you have the relationship and the dup check Calculation all you have to do is make the simple calculation. So we'll actually copy this to the Clipboard, click OK and we're also going to display a Custom message: This customer already exists, put an exclamation point there. And we're going to uncheck this so they can't override it and it's important to know that if you have any of these other options checked that you'll want to go ahead and include them in your custom message. So we'll click OK, go to name last, go into Options and paste in the same formula. There we go. Then we'll come in here and do the same for address1, paste that in there and I don't want to forget to uncheck that, come back to our name first, go into Options, get that custom message, go back to name last, make sure we uncheck that, Display a custom message, Paste and then go back to address1 and paste that message in there. So that's all there is to it. Let's try it out. What we're going to do is make a duplicate of my record so we're going to add a customer, type in John Mark, make sure you get it exactly the same, 2066 Rancho Hills Drive - that's all we need. That's all we're duplicate checking on. Click out of here. It says this already exists. We'll click OK. Oh, yeah, that's John Edward and once we change one of these items it accepts it just fine. If we come back and try to change it again it's still going to give us this error message, you know, there's no way to go ahead and override that Validation because we haven't allowed them to. So I like this feature because it's very different than the other features we've programmed for deleting duplicates in that it doesn't delete duplicates, it prevents them. So it's kind of a neat little feature and you can include anything in your calculation that's used as the keys for your relationship so you can have it check on anything just like the other duplicate record techniques. So, if you like this better it'll prevent the duplicates from ever being entered in the first place.
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
