Define a third field as Text, with auto-entered calculation (replacing existing data) concatenating the two fields. Validate this field as unique, validate always.
I've just started using Filemaker after becoming fed up with MS Access and I'm also struggling with the duplicat record problem. Is there any other way to ensure a unique record other than creating a new field that cocatenates two or more other fields? In Access, all you do is create an index field based on the two (or more) fields and ensure it is unique. Hence, no additional field needs to be created.
There is another way: you can define a self-join relationship matching on two (or more) fields, and validate by counting related records. But I'd rather have one more field than one more relationship. Also, using a field avoids issues with new, yet uncommittted records, which may not be perceived by the relationship.
Thank you. I did as you suggested, and tried to produce the new field but hit another snag. One of the fields is a text field and the other is a date field, so I couldn't work out how to create the calculated field. Does the Date field first have to be converted to another data type? Or is there another way?
If you make the calculation, for example =
Textfield & " | " & Datefield
the date will be automatically converted to text. However, it would be better to use =
Textfield & " | " & GetAsNumber ( Datefield )
Brilliant. I converted the date to a number and it all now works perfectly. Thank you!!
Here's my issue...
- I have a third field that combines First_Name and Last_Name, called Full_Name
- Full_Name is displayed on my Header (for display purposes only, the user doesn't tab to it)
- What I'd like to see is after I enter a value in First_Name AND Last_Name (after I tab out of Last_Name), a pop up dialog box comes up warning the user of a duplicate (without having to click on the Full_Name field).
- The dialog box should give the user the following options:
"This name exists, what would you like to do?"
Options: "Continue with this record" ; "Go to Duplicate"
Any suggestions on a script that would allow me to do this?
NOTE: I'm a complete Newbie when it comes to scripts, and I'm using FMP 10.
Thanks in advance!
Yes, thanks. I'm trying to modify that script:
Set Error Capture [ On ]
If [ Get ( LastError ) ≠ 0 /* validation error */ ]
Show Custom Dialog [ Message: "Combined first and last names must be unique."; Buttons: “OK”, “Cancel” ]
If [ Get ( LastMessageChoice ) = 1 /* OK was clicked */ ]
Go to Field [ NameValidation::Last ]
Exit Script [ Result: False ]
Revert Record/Request [ No dialog ]
But it doesn't seem to do what I need it to (I'm probably modifying it incorrectly). i.e. I need it to validate the "Full_Name" field (which the user doesn't edit, it's just displayed), after the "First_name" and "Last_Name" fields are entered.
So the user enters "First_Name", tabs to "Last Name", and as they tab to the next field the dialog pops up (if Full_Name is a duplicate).
Sorry, like I say, I'm new to scripts.
I'm pulling my hair out.
I don't see how I add these lines to the script:
Go to Field [ NameValidation::Last ] (do I click on "Select Perform" or "Go to target Field"?) Why can't I get the script line to look like this??
Exit Script [ Result: False ] (when I enter this into the calculation box, this just gives me a "The specified table cannot be found" error.)
Sorry, I've been trying to figure it out myself, re-reading Missing Manual, etc. but this has me stumped.