10 Replies Latest reply on Jan 4, 2013 4:07 PM by j_morris

    Validation Issue

    j_morris

      Title

      Validation Issue

      Post


           I'm having an issue with a validation and wonder if someone can advise.

           Users choose from 2 separate dropdown fields the month and year they are entering data for. I then have a separate field that combines the month and year info via a calculation as a way to verify if data has already been entered for a particular time period.

           I have validation on the time period field, but the field does not exist on the layout. See the attached screen capture for the settings I used.

           So my question is actually 2 parts.

           1. After selecting the month and year, if the user tabs to the next field or clicks in the next field, the validation does not activate. However if the user after entering the month and year then clicks outside the field (like the background) but not in another field, the validation works and the warning message is displayed.

           So how can I get this to work via a tab movement or click into another field?

           2. If the validation fails and the message pops up, the user is offered 2 choices, Revert Record and OK. Regardless of which choice is made, another popup appears saying "Revert all changes to this record since it was last entered?" and the choices are Revert and Cancel.

           Is there a way to avoid the second popup if they clicked OK on the first one so that they can correct the record? The whole revert thing can really get confusing for some users.

           Thanks!

            

            

      Options_for_Field_%E2%80%9Ctime_loc_name%E2%80%9D.jpg

        • 1. Re: Validation Issue
          philmodjunk

               1) clicking the layout background commits (saves) the record and this trips the validation check. An OnObjectSave trigger could perform a script that commits the record, but see my comments on 2)

               2) Validation Rules get a bit "clunky" when they trap a data entry error. You may want to experiment with the OnObjectValidation script trigger to run your own script to check for errors before the validation. This can provide you with much more user friendly custom dialogs and methods for handling the data entry error. (But I keep the validation rule as an "insurance policy" just in case someone finds a way to modify the field without tripping the script trigger.)

          • 2. Re: Validation Issue
            j_morris

                 I've been trying to create a script that will properly check for duplicate values, but am not having any luck. I've tried different things using Count and List but I'm just not getting there.

                  

                 Any advice?

            • 3. Re: Validation Issue
              philmodjunk

                   count is an aggregate funcitons. It requires a relationship before it can count values from multiple records.

                   Say you have Table::Value as the field you want to check for duplicate values.

                   Table::Value = Table 2::Value

                   where Table 2 is a new occurrence of table...

                   Is a relationship you can use with count to check for duplicate values.

                   Count ( Table 2::value ) > 1

                   will be true if there is more than one record with the same value as the current record.

              • 4. Re: Validation Issue
                j_morris

                     I've tried that but it's not working.

                     My table is actually called Values so I have Values and Values 2 which are linked on the primary key, which is just a serialized number.

                     Here's what I have for my script:

                If [Count ( values 2::time_loc_name ) > 1]
                
                Show Custom Dialog ["ERROR"; "Stats have already been entered for that time period"]
                
                Else
                
                Exit Script [Result: False]
                
                End If

                     If I try repeating a time_loc_name that is already in another record, nothing happens and the new record is accepted. If I apply validation on the time_loc_name field itself, I do get a popup message triggered from that, but like we both agreed earlier that's not the best solution.

                • 5. Re: Validation Issue
                  philmodjunk
                       

                            I have Values and Values 2 which are linked on the primary key,

                       And why are they linked by the primary key?--the one field that will never have a duplicate. They should be linked by time_loc_name in this self join relationship, not the primary key.

                  • 6. Re: Validation Issue
                    j_morris

                         Doh! I don't know what I was thinking there. You're right it should be joined on time_loc_name.

                         I changed that but things still aren't working the way they should. I added in some steps to the script to see if I could determine if it was actually running or not. If I have the field time_loc_name set to run the script OnObjectValidate, the script never actually runs and it goes right to the regular field validation.

                         I ended up figuring out a slightly different way to get it to work so the script validation happens before the field validation but the Count function isn't working properly. It's only counting one instance of the time_loc_name value when there are clearly now 2 instances.

                    • 7. Re: Validation Issue
                      philmodjunk

                           OnOBjectValidate is a trigger that must be set on one of the fields being edited, not the field that combines the entered values.

                           What is the calculation being used wth time_loc_name to enter a combined value into it?

                           Is this a field of type calculation or a text field with an auto-enter calculation?

                           If a calculation field, what return type has been specified?

                      • 8. Re: Validation Issue
                        j_morris

                              

                             

                                  OnOBjectValidate is a trigger that must be set on one of the fields being edited, not the field that combines the entered values.

                             That part I figured out.

                              

                             Here's the calculation

                             location::loc_short_name & "_" & timePeriod::calendar_month & "_" & timePeriod::calendar_year which produces something like "BAR_03_2012"

                             The location and timePeriod tables are linked to the Values table on primary/foreign keys.

                             It's a text field with an auto-enter calculation

                             The return type is text.

                             The user picks the location, month and year from 3 separate dropdown lists. Since the calculation isn't actually fully formed until after the user moves on from the Year field, I'm trying an onObjectEnter trigger set on the very next field to run the script.

                        • 9. Re: Validation Issue
                          philmodjunk

                               The script still has to commit the record before you can get a count that is accurate. You can use:

                               Commit Recorrds [skip data entry validation] to get an accurate count while not tripping any other validation errors that might otherwise occur if you commit the record.

                          • 10. Re: Validation Issue
                            j_morris

                                 That did it! Thanks so much for your help on this. It is already helping me understand how to do a few other things.