1) clicking the layout background commits (saves) the record and this trips the validation check. An OnObjectSave trigger could perform a script that commits the record, but see my comments on 2)
2) Validation Rules get a bit "clunky" when they trap a data entry error. You may want to experiment with the OnObjectValidation script trigger to run your own script to check for errors before the validation. This can provide you with much more user friendly custom dialogs and methods for handling the data entry error. (But I keep the validation rule as an "insurance policy" just in case someone finds a way to modify the field without tripping the script trigger.)
I've been trying to create a script that will properly check for duplicate values, but am not having any luck. I've tried different things using Count and List but I'm just not getting there.
count is an aggregate funcitons. It requires a relationship before it can count values from multiple records.
Say you have Table::Value as the field you want to check for duplicate values.
Table::Value = Table 2::Value
where Table 2 is a new occurrence of table...
Is a relationship you can use with count to check for duplicate values.
Count ( Table 2::value ) > 1
will be true if there is more than one record with the same value as the current record.
I've tried that but it's not working.
My table is actually called Values so I have Values and Values 2 which are linked on the primary key, which is just a serialized number.
Here's what I have for my script:
If [Count ( values 2::time_loc_name ) > 1] Show Custom Dialog ["ERROR"; "Stats have already been entered for that time period"] Else Exit Script [Result: False] End If
If I try repeating a time_loc_name that is already in another record, nothing happens and the new record is accepted. If I apply validation on the time_loc_name field itself, I do get a popup message triggered from that, but like we both agreed earlier that's not the best solution.
I have Values and Values 2 which are linked on the primary key,
And why are they linked by the primary key?--the one field that will never have a duplicate. They should be linked by time_loc_name in this self join relationship, not the primary key.
Doh! I don't know what I was thinking there. You're right it should be joined on time_loc_name.
I changed that but things still aren't working the way they should. I added in some steps to the script to see if I could determine if it was actually running or not. If I have the field time_loc_name set to run the script OnObjectValidate, the script never actually runs and it goes right to the regular field validation.
I ended up figuring out a slightly different way to get it to work so the script validation happens before the field validation but the Count function isn't working properly. It's only counting one instance of the time_loc_name value when there are clearly now 2 instances.
OnOBjectValidate is a trigger that must be set on one of the fields being edited, not the field that combines the entered values.
What is the calculation being used wth time_loc_name to enter a combined value into it?
Is this a field of type calculation or a text field with an auto-enter calculation?
If a calculation field, what return type has been specified?
That part I figured out.
Here's the calculation
location::loc_short_name & "_" & timePeriod::calendar_month & "_" & timePeriod::calendar_year which produces something like "BAR_03_2012"
The location and timePeriod tables are linked to the Values table on primary/foreign keys.
It's a text field with an auto-enter calculation
The return type is text.
The user picks the location, month and year from 3 separate dropdown lists. Since the calculation isn't actually fully formed until after the user moves on from the Year field, I'm trying an onObjectEnter trigger set on the very next field to run the script.
The script still has to commit the record before you can get a count that is accurate. You can use:
Commit Recorrds [skip data entry validation] to get an accurate count while not tripping any other validation errors that might otherwise occur if you commit the record.
That did it! Thanks so much for your help on this. It is already helping me understand how to do a few other things.