FileMaker Pro 11: Advanced Tutorials
Event Driven Scripting / Validating Duplicates Revisited 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 / Event Driven Scripting / Validating Duplicates Revisited pt. 1)
My favorite of the new script triggers in FileMaker 11 is OnObjectValidate. It allows you to interrupt the standard validation feature and substitute your own scripted logic. In fact, you don't even need traditional validation to make the script trigger work. What we're going to do is modify our Duplicate Validation - it's a feature we put in previously - to use a script trigger instead, so let's remind you about that feature we worked with previously. What we did was and we'll go ahead and add a customer here, we'll put in John Mark Osborne and then 2066 and what's going to happen as soon as we exit out of this it's going to notice that we have a duplicate and say this customer already exists. So we'll revert that record. It's going to get rid of it and delete it. Let's go into Manage, Database and track that down. And if we look at our Name First, Name Last, or Address one they all have the same Validation formula. We'll come in here and look at it. There it is right there. It has this lookup that looks up through a relationship and determines whether it's a duplicate or not. Either it finds something and looks it up and pulls it over or it doesn't and so there's a logic there and actually we're going to use this in our script so I'll copy it. But before we write our script let's look at this relationship right here. So we'll cancel out of that, go over to Relationships and from CUSTOMERS down to CUSTOMERS Dups, right there, double-click on the Relationship line, you can see it says: see if dup check equals dup check, but exclude the current record. We only want to look for records outside itself. So we need to know what dup check does but before we do that let's take a look at this relationship right here, a little bit different one, it's just dup check to dup check which sees both records. So this sees just the record that might be a duplicate outside the current one, this sees both of them. We're going to actually use both of them. So we'll come over here and look at dup check and you'll see it's a simple formula that concatenates name last, name first and the street number, just the first word of the address. So that's how we go ahead and check all that stuff and that's what it's doing on in the background. Now we want to replicate that in our script, so we'll go into Manage Scripts, create a New Script, call it Duplicate Trigger and the first thing we're going to do is an IF statement and in that IF statement we're going to do the same logic as we did in the Validation calculation but just change it slightly, make it not equals, make it work in reverse of what it was doing before and if that's true then we're going to show a Custom Dialog. We'll say: Customer Exists. This customer already exists! Would you like to view the duplicate customers - there we go, question mark. We'll say either Yes, or No. Click OK on that. If they say Yes then we're going to say: If get lastmessagechoice equals 1, if that's true we're going to say Go to Related Record. Actually we're going to choose this one because we want to include the current record in that Go to Related Record but as you see up here we didn't include it. We didn't want it to look for, we didn't want it to locate itself basically. And then we'll say: Go to CUSTOMERS Form and Show only related records, of course. Then what we're going to do is, since we'll be in Form View, let's run the script List View so we can switch to List View for them. That looks good. Perfect. So now we've got this all working, what if they choose No from the Show Custom Dialog up here? Well, we need to put an Else in and we're going to do some steps here, we're going to put in a Go to Object and we're also going to put in an Insert Calculated Result and this is a little peculiar but it has to do with this particular technique we're trying to work on here. The problem is, is that if we don't commit the record up here - and we'll put that in there now, move it up there at the top and then uncheck both these options - if we don't commit it right there, this will not locate the other record properly. It just won't work. It works different than the validation - we don't have to do this with the Validation Calculation because it's a different order of operations. This happens at a different point and so the relationship's not refreshed at this point for the script but it is for the Validation Calculation, so we need to commit the records. Problem is, once you commit the record then you're outside the field and, you know, because we said skip data entry, we don't have it selected anymore, if we go back to the object, or go back to the field, the problem is, is we're going to actually not have validation the next time we exit because we haven't modified it. We've committed and then come back to it. So what we need to do is say go to the object and insert the value back in there. So what we need are a couple of Set Variables. So we'll put the Set Variables up here. This is going to store our data. Dollar sign field and we're going to put: get activelayoutobjectname and then we're also going to go ahead and I probably spelled something wrong here. Yeah, there we go - objectname. OK, I think I got it that time. We're also going to set a value so we can store what value is in it because we're going to write over itself to make it initiate that validation again, you know, to make that field modified and what we're going to put in there is Get ActiveFieldContents. There we go. Click OK. And so we're going to say Go to Object dollar sign Field down here to get back into the field and then Insert Calculated Result we're going to say Insert dollar sign Value. So in order for all this to work - we're actually going to save this real quick because we're not quite done with it - in order for all this to work we're going to have to come in here into Layout Mode and name each one of those fields. So we're going to double-click on this one, Cancel it; we're automatically in here. Go to Position and name this, call it First Name. And we'll go down to last name and we'll call it Last Name, then we'll go to address and call it Address 1. There we go. And while we're here we might as well put the script trigger on here so we'll go into Format, Set Script Trigger, OnObjectValidate, go down to the bottom and find our script, and do the same for each one of these. And I'm going to use the keyboard command here, there we go, Duplicate Trigger and one last one to do, OnObjectValidate, all the way to the bottom here, OK, so now we have that all set up but we're not quite done with our script, so let's come in here. We'll uncheck it from here because we don't want to see it there and edit it.
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
