Script it with a trigger for OnObjectSave on the field.
Commit Record [ no dialog ]
Set Variable [ $count ; ExecuteSQL("SELECT COUNT(*) FROM yourTable WHERE yourField =?";"";""; yourTable::yourField) ]
If [ $count > 1 ]
Show Custom Dialog [ "VALUE ALREADY IN ANOTHER RECORD" ; "Proceed" ; "Cancel" ]
If [ Get(LastMessageChoice) = 1 ]
Do stuff here, user accepted.
Set Field [ yourTable::yourField ; "" ]
Thank you Mike!
But I´m still starting on FM, could you please explain me a little better this part?:
"Set Variable [ $count ; ExecuteSQL("SELECT COUNT(*) FROM yourTable WHERE yourField =?";"";""; yourTable::yourField) ]"
Thank you for your patience.
With my best wishes,
That creates a variable ( $count ), which is a count of all of the records ( in yourTable, use your table name here ), where the field ( yourField, use your field name here ) matches the value you just entered ( replace yourTable::yourField with the field you are triggering).
On the validation tab of the field options:
- check "unique value"
- leaved checked the option "allow user to override...",
- use "display custom message..." to enter a yes/no question, like "This value already exists! Are you SURE it is correct?"
Because you are allowing override, the custom message will display with yes and no buttons. If they click Yes, the non-unique value will be saved. If they click No, it won't.
Nothing wrong with the other solutions, and they may be appropriate, but have you considered this most basic approach?
I've found from a user perspective (with at least three clients now), the "revert" button on the system validation messages causes more confusion than it's worth.
A valid concern, but I'd still consider the simple approach.
I've had a couple clients who couldn't wrap their minds around the fact that entering "12" in a field displayed as percent gives you 1200%, so I've used auto-enters and other methods to allow them to enter 12 when they mean .12. So, yes, sometimes you have to go further if your users need it. I wouldn't say that percentages as-is don't work, though, or skip over considering just using them as-is.
Like I said, all good options. There's a good chance that one of those more advanced options is more appropriate, too.
Since the OP is "just starting on FM" and has made the blanket assumption that "I should not use the Unique Value", it occurred to me that we might be going straight to more advanced (and arguably, more elegant) solutions, but skipping the obvious.
I use some alternate methods for field validation, too. For a small solution, though, for a few users who either know what "revert" means or can just be taught, "revert field" might not be a problem worth additional work.
One might also include in the custom menu "(click 'Revert Field' to remove the duplicate value)".
Thank you for the good options and explanations you gave me! I will try them .
With my best wishes,