Scripting field validation

[Jdawg]

I've been searching the forum for several days now looking for a way to require a unique value without using the built-in field validation.

The reason I don't want to use the built-in field validation is because it is executed on field exit, which prevents me from triggering my own script and carrying out various commands based on the validation.

For example, I don't want the user to be allowed to go to another layout if they didn't enter a unique value when creating a record.

Users are creating reservation records in the reservations table. The reservation ID is a concatenated text field that combines lab name, date, and timeslot.

I'm trying to do something like this:

If reservation ID IS NOT unique

Show Custom Dialog("Reservation Conflict";"this time slot has already been reserved. Please select another time slot.")

Exit Script

End If

Go to Layout("Home")

Adjust Window(Maximize)

[comment]

You can define a self-join relationship matching on lab name, date and time-slot, and count the related records.

[Jdawg]

Comment,

Your suggestion works perfectly. Thank you so much! I've attached a PDF of the script and a picture of my ERD for anyone else that may benefit from this solution.

Done.pdf

ERD.pdf

[PHS]

Coming from the SQL world, it was frustrating to not have the ability to enforce uniqueness on more than one field. After searching many posts, I came up with a modified version of what was proposed in this post.

1. Create a calculated field concatenating the fields to be checked.

UniqueField = field 1 & field2

2. Setup the self join on the calculated field.

3. Create another field

PKCounter = Count ( UNIQUE_Table::__pkID )

4. Create an Object Name for the last field that is part of the concatenated field that will be inputted into on the layout.

ObjectNameField2 = ONField2

5. Create an Object Name for the next field to be entered after field2.

ObjectNameNxtField = ONNxtField

6. Create a script similar to the one in Done.pdf above, but instead does the following:

Commit Records/Requests[ No dialog ]

If [ STATIC_Table::PKCounter > 1 ]

Show Custom Dialog [ Title: "Unique Check Failed"; Message: "The combination of field1 and field2 must be Unique. Choose Revert to change the record or Delete to delete it."; Buttons: “Revert”, “Delete” ]

If [ Get (LastMessageChoice) = 2 ]

Delete Record/Request

Else

Go to Object [ Object Name: "ONField2" ]

End If

Else

Go to Object [ Object Name: "ONNxtField" ]

End If

Exit Script [ Result: 0 ]

6. On the layout to be used for data input, go to the field2 and put an OnExit Trigger to perform the script.

You do need to take into account any required fields in the table and when they are validated, since the commit will throw validation errors if those other fields are not filled in. That is the reason for the other object named field. It is a required field, but only validated when the user modifies it. When it is set to always validate, you will get an error on the initial commit.