6 Replies Latest reply on Mar 15, 2016 4:58 AM by m.swanston

    Validation calculation for one of two input values

    m.swanston

      So following on from a previous question, I have an input that I'd like to validate with a calculation, but have been told that users can enter the value in one of two different formats:

      NN.NN or NN.NN.NN, ie 01.04 or 01.04.01

      I took some validation from another field I'd had some help with and extended it, but it's not working (and I understand why, just not sure how best to write it to work for how I need).

      Currently, the calculation (which is not enabled) is set to list the logic I need:

      Case (
        Length ( Self ) ≠ 5; 0;
        Length ( Self ) ≠ 8; 0;
        Middle ( Self; 3; 1 ) ≠ "."; 0;
        Middle ( Self; 6; 1 ) ≠ "."; 0;
        Length ( Filter ( Left ( Self; 2 ); "0123456789" ) ) ≠ 2; 0;
        Length ( Filter ( Middle ( Self; 4; 2 ); "0123456789" ) ) ≠ 2; 0;
        Length ( Filter ( Right ( Self; 2 ); "0123456789" ) ) ≠ 2; 0;
        1
      )
      

      But, in my head, what I need to do is to validate for either scenario, something like:

      If Length( Self ) = 5;
      Case (
        Middle ( Self; 3; 1 ) ≠ "."; 0;
        Length ( Filter ( Left ( Self; 2 ); "0123456789" ) ) ≠ 2; 0;
        Length ( Filter ( Right ( Self; 2 ); "0123456789" ) ) ≠ 2; 0;
        1
      );
      If Length( Self ) = 8;
      Case (
      Middle ( Self; 3; 1 ) ≠ "."; 0;
        Middle ( Self; 6; 1 ) ≠ "."; 0;
        Length ( Filter ( Left ( Self; 2 ); "0123456789" ) ) ≠ 2; 0;
        Length ( Filter ( Middle ( Self; 4; 2 ); "0123456789" ) ) ≠ 2; 0;
        Length ( Filter ( Right ( Self; 2 ); "0123456789" ) ) ≠ 2; 0;
        1
      )))
      

      Whilst I could try it out, I just want to be sure that it's the best way to code it, am not sure the nested If is a good method, but be good to get some feedback on how to code this in the neatest syntax.

      Many thanks

      Martin

        • 1. Re: Validation calculation for one of two input values
          Mike_Mitchell

          I think you should be able to do something like this:

           

          Let ( [

               inputFiltered = Filter ( Self ; "0123456789." ) ;

               inputList = Substitute ( inputFiltered ; "." ; "¶" )

          ] ;

           

          Case (

           

               Length ( GetValue ( inputList ; 1 )) ≠ 2 ; 0 ;

               Length ( GetValue ( inputList ; 2 )) ≠ 2 ; 0 ;

               Length ( GetValue ( inputList ; 3 )) ≠ 2 and ValueCount ( inputList ) > 2 ; 0 ;

               1

           

          )

          )

           

          Realize, of course, that if someone puts in characters other than a numeral or ".", this will still validate as true. I based it on your calculation above, so I assumed that was the right answer.

           

          HTH

           

          Mike

          • 2. Re: Validation calculation for one of two input values
            m.swanston

            Thanks Mike.

            So what you are doing is removing any characters that aren't numeric or a full-stop, creating a list of items using the full-stop as a delimiter, and then verifying that each list item is a 2 digit long numeric value (and only check the third list item if it's present).

            The requirement is that they can only use a period/full-stop so it's important that the validation stops them from using anything else. My original code does work, but seems a long-winded way to go about it.

            So it would validate as false if, say, someone entered 00,00.00, as the filtering would reduce that to 0000.00 and then the first item would be 4 digits long, therefore failing the test.

            Can I ask how would it validate as true if someone enters characters other than numbers or a '.'?

            Thanks again

            Martin

            • 3. Re: Validation calculation for one of two input values
              Mike_Mitchell

              See attached.

               

              The reason it will validate as true if you use alpha characters is that the Filter ( ) function removes them as part of the validation. If that's not right, you need to add another condition:

               

                   Filter ( input ; "0123456789." ) = input

               

              If I may ask: It looks like these are date values. Is there a reason you can't just use a date field? These validate automatically to require valid dates.

              • 4. Re: Validation calculation for one of two input values
                m.swanston

                Hi Mike

                 

                OK, I think I misunderstood when you said it would validate as true as to whether that was something I might not anticipate? As I said, users can only enter the input as NN.NN or NN.NN.NN, so numbers and a '.' are the only allowed characters.

                 

                And no it's not a date, it's a field called task code which is part of the company billing data. I'm pretty sure either are within date limits but couldn't guarantee that it won't change so need to continue to validate as text in a specific format (kind of like a mask I guess).

                 

                Thanks again

                 

                Martin

                • 6. Re: Validation calculation for one of two input values
                  m.swanston

                  Many thanks Mike, that's great. The additional line in the calculation makes perfect sense to check that the user didn't enter one or more invalid characters.

                  Thanks again

                  Martin