13 Replies Latest reply on Sep 11, 2012 10:43 AM by AlastairMcInnes

    Validating a unique value using a script

    AlastairMcInnes

      Title

      Validating a unique value using a script

      Post

           Hello,

           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?

           Thanks,
           Alastair

        • 1. Re: Validating a unique value using a script
          philmodjunk

               Why not stick with your original validation calculation, but include this added detail?

               IsEmpty ( self ) Or ( /* put your current validation calculation here */ )

               For your script, OnObjectModify doesn't seem to be the right trigger. As you type data into a field, OnObjectModify will be tripped each time you press a key on your keyboard that modifies the data.

               

                    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.

               You could just select the unique values validation option. Leaving the field empty won't trip that particular validation rule. But if you go ahead with the scripted method, you need to match records by the ISBN field not by the EditionID field.

          • 2. Re: Validating a unique value using a script
            AlastairMcInnes

                 Thanks Phil.

                 There are a couple of corrections to my original post.

                 The first script runs on the OnObjectValidate event and the connection to the SelfJoin table is made via the ISBN field. Sorry about that.

                 I don't want to build in the allowance for the field being empty because I want the user to be asked to confirm that there really isn't an ISBN. Of course, I can't stop them just saying that it's OK, but I'd like to make the point anyway...

                 And then, if FM wants the field to be "Not Empty" it points it out every time you go to the record or import anything that affects the record. Which is really annoying for the user. 

                 FM does almost all the validation reasonably well except for this bit. It's nice that the script can say why the ISBN isn't valid but it's not that big a deal - it's probably obvious.

                  

            • 3. Re: Validating a unique value using a script
              philmodjunk

                   At the OnObjectValidate point, the record hasn't yet been committed and that is probably why you aren't getting the expected count. OnObjectExit might work or you might have to insert a commit records step before you make this check.

                   Returning to the validation rule method, you might try this approach:

                   NoISBNOK = "No ISBN" or ( Yourcalculation goes here )

                   This causes the system to complain that there's a missing ISBN, unless the user specifically marks a field to indicate that it is Ok not to have one. This added field will then keep the repeated validation messages from popping up telling the user that the field is empty once they set that value in the NoISBNOK field.

                   I usually take a careful look at both methods and often employ both for the same issue. The validation rule applies everywhere in my database so it makes sure that the data is correct even if the user finds a way to alter the data without tripping a script trigger--which can be done. The script trigger then is put in place to catch the errors earlier and to provide a better diagnostic message as to what error was made. The validation rule then becomes my "insurance policy" that I hope never to actually see kick in.

              • 4. Re: Validating a unique value using a script
                AlastairMcInnes

                     I've tried most of the triggers - OnObjectExit had a problem but I can't remember what it was now! It was to do with the fact that that trigger always runs even if you haven't changed anything but I can't, now, think why that would be a problem.

                     As I said in the opening post, I did wonder about putting in a Commit Records - probably it wouldn't be such a problem in reality.

                     I have also considered an extra field to say that a particular book doesn't have an ISBN but it seems a bit clumsy. Perhaps no more clumsy than having it rejected the first time - essentially my script is setting just such a field.

                     One problem with using OnExit is that it runs after the FM validation, I think. It has also occurred to me that the FM validation always applies whereas mine will only apply on the layout on which I've set it. There is only one other layout based on this table so I'm reasonably relaxed about it.

                     Thanks, Phil, as always. I'll play with the OnExit trigger again and a Commit Records and see what happens.

                • 5. Re: Validating a unique value using a script
                  philmodjunk

                       OnObjectSave will only trip if the data has been edited--so that may also be worth a look.

                  • 6. Re: Validating a unique value using a script
                    AlastairMcInnes

                         I think I'm going to have to sit down with the users and decide just whether we're going to get upset about the blank ISBNs or not. One huge advantage of using the built in validation is that it works during an import whereas my version wouldn't unless I scripted the import and ran it on each record, which I don't fancy doing.

                         Although most imports come direct from the publishers, there have been times when we've been sent an invalid ISBN - it's nice to be able to catch them and get confirmation.

                         And a blank ISBN is pretty obvious whereas an invalid one isn't.

                         Thanks again.

                    • 7. Re: Validating a unique value using a script
                      philmodjunk

                           Note that Validation Errors exclude records duing import--if these are records you need, but need the data to be corrected, then you may not find such validation helpful as you may not have any simple way to tell which records were not imported.

                           On the other hand some validation scripts using Replace Field Contents to set a value in a field if the data is or isn't valid and then using a find to pull up the imported reocrds that fail this test would be pretty easy to set up.

                      • 8. Re: Validating a unique value using a script
                        AlastairMcInnes

                             I see what you mean.

                             But to import a file of book data other than with Import Records would mean trying to work through the file a record at a time - can FM let me do that?

                             I wonder if the OnCommit event fires when a record is created and committed during an import.

                        • 9. Re: Validating a unique value using a script
                          philmodjunk

                               Import records does not trip script triggers.

                               But the validation rules will only kick in if you specify validate always.

                               What you can do is import your records and then use Replace Field Contents to use your validation calculation to set a flag in a field to mark which of the newly imported records have valid data. Then you can perform a find specifying criteria in this flag field to find all the records with invalid data so that they can be updated to have correct data. This entire process can be scripted.

                          • 10. Re: Validating a unique value using a script
                            AlastairMcInnes

                                 Good idea, Phil.

                                 It seems like I'm going to have to venture into the shady world of custom functions to avoid having to rewrite the ISBN check-digit calculation all over the place. Well, it had to happen sooner or later...

                                 Thanks again,
                                 Alastair

                            • 11. Re: Validating a unique value using a script
                              AlastairMcInnes

                                   No, but calculating and verifying the check digit of an ISBN is a bit tedious. I moved it to a custom function to save having the same long chunk of code in several places. 

                                   It wasn't as scary as I was imagining...

                              • 12. Re: Validating a unique value using a script
                                philmodjunk

                                     Sorry, spotted that after I posted and couldn't hit the delete link quickly enough...

                                • 13. Re: Validating a unique value using a script
                                  AlastairMcInnes

                                       Absolutely no need for you to apologise. Given the amount of your time you've devoted to making my project as much of a success as it is, you can say pretty much what you like...