12 Replies Latest reply on Mar 27, 2013 2:00 PM by Matty_1

    Conditional Data Validation

    Matty_1

      Title

      Conditional Data Validation

      Post

           Hello,

           I have a table where we enter all our parts and service item numbers to have them automatically populate our maintenance summary reports.  My colleague is asking if I could have data validation to make sure she doesn't enter information twice.  The issue is that often two vendors will have the same part number so I'm looking at putting in a "conditional" data validation and can't seem to make it work.

           Is it possible to have the "unique value" check box checked but have a condition that it only kicks in when the Vendor ID Match Field is the same as another record in the same table?  Otherwise allowing duplicates when the part number is the same as another record but has a different vendor assigned to it.

           Thanks!

        • 1. Re: Conditional Data Validation
          philmodjunk

               Add a text field to your report table. Give it an auto-entered calculation that combines the part and vendor ID's in a calculation such as:

               PartID & " " & VendorID

               Put your unique values validation on this new text field.

          • 2. Re: Conditional Data Validation
            Matty_1

                 of course, very cool.  Thank you Phil!

            • 3. Re: Conditional Data Validation
              Matty_1

                   “Part Number Validation” is defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field.

                   This message kicks in only when I attempt to leave the record and not immediately after entering a duplicate value.  Is this the only way to about this?  Also, I don't fully understand what this message is referring to.

              • 4. Re: Conditional Data Validation
                philmodjunk

                     "Part Number Validation" is the name of the newly added text field, correct?

                     What exact options on the Validation tab did you select?

                     Oh yes, be sure to clear the "do not replace existing values.." check box on this calculation.

                     I would have expected a different validation error message if just the Unique Values check box were selected on this field.

                     

                          This message kicks in only when I attempt to leave the record and not immediately after entering a duplicate value.

                     This validation option (Unique values) cannot be performed until the record is committed. "Leaving the record" is one way to commit the record. Clicking a blank area on your layout is another. You can also use a script trigger to force a commit records sooner (such as just after the second of the two fields (part number and vendor) gets a value.

                     Or you can leave this test in place and add an OnObjectValidate script trigger to check for duplicate values sooner in the data entry process. You can set this trigger on both fields and use a self join relationship to check for the existance of a duplicate. The script trigger is tripped before the record is committed, so you can identify the presence of a duplicate, use a custom dialog to tell the user about the error and use Exit Script [False] to return the user to the data entry screen with the record still uncommitted so that they can fix the error and try again.

                • 5. Re: Conditional Data Validation
                  Matty_1

                        Here's what I did, let me know if these's a more efficient way of doing this.

                       I created a relationship to itself when the Vendor Id and Part Numbers match and created a summary field (Duplicate CHECK) that counts any records in that relationship. I also put the following script tirgger in OnObjectValidate.

                        

                        

                       Commit Record/Requests [Skip data entry validation; No dialog]

                       Loop

                       If [Products DuplicateCHECK::Duplicate CHECK > 1]

                       Show Custom Dialog [CUSTOM DIALOG HERE]

                       Set Selection [Product::PartNumber]

                       Exit Script [Result: False]

                       End If

                       Exit Loop If [Products DuplicateCHECK::Duplicate CHECK ≤ 1]

                       End Loop

                  • 6. Re: Conditional Data Validation
                    Matty_1

                         Ok for some reason I thought that was working and it certainly wasn't.  Not sure where I was at with those loops.  I've changed the script trigger to OnObjectExit and did the following:

                          

                         Commit Record/Requests [Skip data entry validation; No dialog]

                         If [Products DuplicateCHECK::Duplicate CHECK > 1]

                         Show Custom Dialog [CUSTOM DIALOG HERE]

                         Set Selection [Product::PartNumber]

                         Exit Script [Result: False]

                         End If

                         Go to Next Field

                          

                          

                         I added the Go to Next Field to maintain tabbing order and functionality.

                    • 7. Re: Conditional Data Validation
                      philmodjunk

                           You can leave out go to next field. The action that triggers the OnObjectValidate will be processed automatically unless Exist Script [False] is used to cancel it.

                           I suggest:

                           If [Not isEmpty ( Products DuplicateCHECK::AnyNeverEmptyField ) ]

                           Show Custom Dialog [CUSTOM DIALOG HERE]

                           Set Selection [Product::PartNumber]

                           Exit Script [Result: False]

                           End If

                           This will not require committing the record--which gives the user the option to revert the record if they wish.

                      • 8. Re: Conditional Data Validation
                        Matty_1

                             But with the above example, wouln't the relationship forever remain blank until the commit record line is in place?

                        • 9. Re: Conditional Data Validation
                          philmodjunk

                               Good point. (What was I thinking?")

                               I'm now remembering how I've done this previously:

                               Set Error Capture [on]
                               Commit Records []
                               If [ Get ( LastError ) // a validation error was tripped ]
                                  Show Custom Dialog["message goes here"]
                                  Exit Script [False]
                               End If

                          • 10. Re: Conditional Data Validation
                            Matty_1

                                 Sorry with the kick backs but this would require a valiation to be ticked and my problem is I need a two tiered validation.  Unique values but only for the individual vendor in question.

                                  

                                 The above would not suffice correct?

                            • 11. Re: Conditional Data Validation
                              philmodjunk

                                   Yes it would if you use the validation method that I previously specified for the text field that combines the two values.

                              • 12. Re: Conditional Data Validation
                                Matty_1

                                     Ok thank you Phil