4 Replies Latest reply on Jul 3, 2012 9:58 AM by philmodjunk

    Field Validation



      Field Validation




      how do I immediately validate a field, where users can input data consisting of a sequence of 8 numbers delimited by a space, e.g. "12345678 87654321 01283746 00000000"

      What would a formula look like, if the user would wrongly enter a comma for instance?

      Thanks in advance,





        • 1. Re: Field Validation

          Starting idea, not conclusive:

          you could filter for "1234567890 ". The last digit is the space. This will remove all characters except those you want.

          Now you would want to check to see if each string is 8 characters: Length(thestring) = 8

          Each group could become one line of a value list which would make verification each inside a loop, for instance. 

          Here you would use substitute to create the value list within a calculated field or a variable: substitute(thefield; " " ; PP ) with PP being the Pilcrow.


          length(thestring[nbr]) = 8

          end loop

          And so on. You'll need to use some of the text functions for value lists.

          Personally I would use a script that is triggered when the user tabs out of the field or it is saved, etc. Then as things develop or change I can easily modify the script without having to modifify any calculated field, etc.

          However, some heavy duty custom function guy may take this outline and produce a working concept.

          • 2. Re: Field Validation

            There's a difference between validating input and trying to correct it automatically--which isn't always possible.

            Borrowing form Jack's post, you could use this validation rule:

            Length ( filter ( self ; "1234567890 " ) ) = 8 AND ( Middle ( self ; 9 ; 1 ) = " " ) AND Middle ( self ; 18 ; 1 ) = " " )

            This assumes only and always entering three 8 digit numbers separated by spaces.

            An alternative approach to consider would for users to enter these numbers into 3 separate fields which, if necessary can be combined via calculation into the 3 number groups delimmitted with spaces provided by the calculation instead of by the user.

            • 3. Re: Field Validation

              Thanks to you both!

              While I would prefer to go with the second approach, (validation through field calculation) this seems to bear problems. First of all

              Length ( filter ( self ; "1234567890 " ) ) = 8

              doesn't work, since Length addresses the complete field, which can be multifolds of 8 (or 9 if we include the space).


              For some reason 

              filter ( self ; "1234567890 " )

              doesn't validate, because it seems also to accept comma, points and so on.

              • 4. Re: Field Validation

                Are the "numbers" entered always a fixed length?

                If so, Length ( Filter ( Self ; "1234567890 " ) ) = 26

                would be correct for 3 such digit strings separated by single spaces.

                Self = Filter ( Self ; "1234567890 " )

                might be another option, but both leave a few loop holes, such as spaces in the wrong place.

                I strongly recommend using one field for each digit string as a simpler way to get error free data entry.

                You can also set up an auto-enter calculation that uses such a filter expression to exclude all characters except those that you choose to permit.