Just because you store different policy types in the same table and thus leave a few fields blank in each record doesn't mean your database isn't fully relational. :smileywink:
You should be able to set up validation messages that only pop-up if a field required for a specific policy type is left blank.
Example: Let's say you have a field for a car's VIN number. That's a required field only if the Policy field = "Automobile".
In your validation by calculation, use this expression:
Not IsEmpty(self) And Policy = "Automobile"
Clear the "evalute only if field is modified" option. Now you can enter a validation message: "A VIN number is required for all automobile policies."
If you enter automobile, leave VIN blank and commit the record, you'll get the above validation message. Make sense?
Sometimes validation settings just get too clunky. Another option is to write your own error checking code and set it to be triggered when the record is committed.
I'll give your code a bash in a few minutes. I prefer to have the fields validated as the User enters the enter per field. Leaving it until the record is committed seems to messy because if several fields are invalid teh user will be looped around message displayed, returning to relevant field, recommiting record, so checking again and around we go. If that makes sense.
With a script set to perform when the record commits, you can exit the script on the first error after placing the cursor in the problem field. That way the user only deals with one error at a tiem. You can also use custom formatting to draw attention to empty required fields to help the user avoid leaving the field empty in the first place.
That works fine, thanks.
Have set Cover_type field up as a dropdown value list so values can be selected, but how do I force User input to be in caps? i.e. if a value on the value list is "TP" or "TPF" but User keys "tp" of "tpf" I want the validation to convert all to caps
You can't use the validation, but you can use an auto-entered calculation. Even simpler, just format the field to be Upper case. Select the field in layout mode and use Format text...
If you want to actually convert the data to all caps, use this auto-entered calculation:
Clear the "Do not replace existing value..." check box also.
I believe you could also use a script triggered OnObjectExit to make sure errors are reported one at a time?
That works also, though it's behavior will now be very similar to a validation setting so I would normally use this method only if I need do something that a validation rule with custom validation message can't handle. (The literal text validation message sometimes just doesn't cut it.)
The only minor drawbacks to a separate trigger on each such field is that now you have more layout details to manage and your error checking is split up into multiple scripts which is sometimes a bit more trouble to manage than putting all the error checking code into a single script.