Validating a unique value using a script
I have a database that keeps track of book data. For the purposes of this post, there are two main data tables - the Titles and Editions table. Titles holds basic data common to all versions of a book such as the title itself, number of pages, dimensions, publisher and so on. Editions holds data specifically for one version of the book - the hardback, paperback, kindle, etc. edition - such as ISBN, publication date, etc.
They are related by Editions::fkTitleID = Titles::TitleID
There is a portal on my main browse layout (based on the Titles table) which shows all the available editions of the book. When the user clicks on a portal row, the data for that book is displayed in a section of the main browse layout. This is achieved using a second copy of the Editions table in the relationships graph called "SelectedEdition". This is related to the Titles table with Titles::fkSelectedEditionID = SelectedEdition::EditionID
I want to validate the ISBN as it is an important piece of data in the book trade. It must be non-blank, unique, 13 digits long and have a valid check-digit at the end.
I originally set all of this into the Validation bit of the field definition dialog box and it worked up to a point. There are some book which don't have an ISBN and, for them, there isn't any validation. This meant that I had to allow the user to over-ride the error message and force the system to accept the blank value. This might have been OK except that every time you altered anything on the layout and then exited a field, the ISBN-error box would pop up. If you wanted to add the author's details, or the publisher (held in separate tables), the act of going to their layouts would cause the error box to appear and it was getting annoying. Some scripts would fail as they passed through a layout based on the Editions table because the ISBN was invalid.
So, I decided I better do this manually. I wrote a script that is triggered by the OnObjectModify event of the ISBN field to check all the conditions and, at each stage, if it fails, to alert the user and allow them to correct the error or force acceptance of the value. I've set another field to be "Yes" when the ISBN either validates OK or when the user over-rides the validation.
A second script runs on the OnRecordCommit event of the layout itself. This one checks for "Yes" in the ISBN_OK field before allowing the user to proceed to the next record they've selected. If it doesn't find that, it goes through the same process of asking if the value should be used anyway - if so the ISBN_OK field is set to "Yes" and the process continues.
All of this is working OK except for the unique bit.
I set up another copy of the Editions table in the relationships graph called "Editions_SelfJoin" and linked it to the Editions table with Editions_SelfJoin::EditionID = Editions::EditionID. Then, in the script, I'm checking if Count ( Editions_SelfJoin::ISBN ) > 1 to decide if the ISBN is new to the system or not.
This doesn't work.
It seems to be "one step behind". If I put a duplicate ISBN in deliberately it is accepted. If I then move off the record and then back to it and attempt to put the same ISBN in again, it is trapped as a duplicate. If I make it unique, it will still be trapped as a duplicate until I've moved off the record and back onto it again.
I'm wondering if I have to do a record Commit at some stage, but I'm reluctant for two reasons - firstly I don't want to be writing data into the database until I've validated it, and, secondly, if I commit the records in a script, the user will lose the ablility to revert their own changes (at least, I assume they will).
I tried, incidentally, making the self join with the "SelectedEdition" table, but that didn't make any difference.
Can anyone come up with a better way of doing this or see what my problem is?