To start, you can define Full Name as a text field and the calculation can be an auto-enter calculation. You can actually change the field type to text, open field options and when you select the calculation option, you'll find that FileMaker automatically has set up your original calculation as the auto-entered calculation. But now you can click the validation tab and select the Unique Values validation option.
This won't quite get you the options you want as a response to duplicate values, but this is a good "insurance" setting to make sure that you catch duplicate entries even if the user finds a way around your layout settings for checking for duplicate values.
To get a custom error message, you'll need to use the OnObjectValidate script trigger and write a script that checks for duplicates and then displays a custom dialog with your specified buttons for what to do when it happens.
This code in that script can identify duplicate values if the above field option was specified:
Set Error Capture [on]
Commit Records [no dialog]
If [ Get ( LastError ) // duplicate was entered ]
Show Custom Dialog ["Error: ...
If [ Get ( LastMessageChoice ) = 1
Get ( LastMesageChoice ) is the get function to use to detect which button was selected in the custom dialog. You can use Exit Script [False] to terminate the script leaving the record uncommitted if the user opts to go back and modify the name that they entered.
Thanks, Phil. This helps a lot. I'm almost there.
I almost have what I need but I inserted a Find in the script with the operator ! for the criteria but it will not find duplicates. No matter what I do, it shows all records. Even if I do a manual Find and insert the ! in the field, when I hit enter, it shows all records. I've seen other posts with people having the same problem. I can't figure out how to make the Find show Duplicates when I use the ! symbol.
Why did you use that method? It's not what i recommended for the script. Get ( LastError ) should detect when a duplicate was entered and you might also use a self join to check for the existance of another record with the same value.
I used the script you wrote with the exception of adding the Find because I wanted the record that was already in the Data Base to show right below the record that was trying to be created. Your script worked great - it just didn't show the attempt and the record that was already there. It is important to show both records at the same time because there is a possibility that the user would want to keep both records. There is a high possibility that a father and a son could be in the Data Base with the same first and last name.
All your script did was notify me that there was a duplicate, which was great, and it got me the right direction, but the user needs to know whether to delete the one they are trying to create because the record is already there - or go ahead and create another record - even though the last and first names are the same.
this works for me...
Hi Sym - Thanks for the script. I'm going to attempt to try this and see what happens.
Hey Sim - Any chance I could get you to send me a FM doc with that script in it?
Ok, Can I back up here for a moment and try to explore the "!" operator in finds?
If it works, it should show all records that are duplicates, not any that are not duplicates. If it fails, then it would go back to the found set it had originally (with an error that it did not find any).
So I'm curious, when you used the "!" in you find, how did it show all records? (Just want to know for future reference.) Thanks!
Good point and one of the reasons why I asked why you where performing a find with the ! operator. It will find more than you want here if you are allowing duplicate names in previous cases.
Instead, copy the name data into a variable or two and use it as your find criteria so that you find records with names that are duplicates of the name data in just your current record.
I found an old reply of yours that said if the ! wasn''t working, then it could be the result of a corrupt index. So, I deleted all the records in my file and started adding new ones. The ! started working again. I put the ! in the Full Name field.
My script now contains 3 buttons when the error for duplicate Full Name pops up.
1) OK - which allows the user to continue adding to the record, even though it's a duplicate.
2) Find - which is supposed to run a script called Find Duplicates. If I run Find Duplicates by itself, it works perfectly. If there are two people with the same first and last names, it pulls them up and displays them side by side so the user can decide what to do.
3) Cancel - which deletes the attempted duplicate record.
This would work really well for me if the 2nd button would run the Find script, but it won't. It acts the same as if you had pushed the OK button.
I have double-checked to make sure the buttons are correct and they are. The buttons are OK; Find; Cancel and all of them are pointing to the correct items in the script.
This is what the script looks like:
I'm not sure why when you push the Find button it acts as the OK.
In any case, I would change your 3 If statements with the Get ( LastMessageChoice ) to one If-Else statement. Ex.
If [ Get ( LastMessageChoice )=1 ]
Else If [ Get ( LastMessageChoice )=2 ]
Else If [ Get ( LastMessageChoice )=3 ]
Set Variable [ $lastMsg; Value:Get ( LastMessageChoice ) ]
If [ $lastMsg=1 ]
Else If [ $lastMsg=2 ]
Else If [ $lastMsg=3 ]
Reason: If your second If statement that is the Find. Runs a script that ends with a "Show Custom Dialog" The third If statement will use the results from that Dialog and not the original one in this script. Hence, it could still run.
However, I do not know what your Find Duplicates script does, so that is only a warning. I doubt that this is the cause of your problem.
I'm also not sure why your Find won't work, unless I see the "Find Duplicates" script.
Also, if your database is corrupt, the best thing to do is make a backup of the file and use the Recover menu item to repair it. (I'm not sure if the same applies with corrupt indexes. Deleting Records and adding new ones, though, may allow for re-indexing.)
Hopefully that helps
Changed my script to do what you suggested above, but it still does the same thing. First screenshot is the script changed the way you suggested; 2nd screenshot is the Find Duplicates script that works perfectly - when I run it by itself.