Validating a field by calculation

[Chalkster]

I have a field in which I ave to enter data in an exact format the rules are as follows

Characters are entered as groups of 3 .... first character is either a letter or "/" or"@" second and third characters are always a letter.

Groups are separated by a singlespace... no space permitted afetr last group , in front of first group

Any number of groups permitted up to a total of 40 groups.

The only permissible exception to the above rules is a value of "???" .... in this case this would be the value for the field, and no other groups permitted.

The validation I am currently using is

(Case(

IsEmpty(Stops); 1;

Stops= "???"; 1;

Let( W = WordCount(Stops);

Case( W > 40; 0; Length(Stops) <> 4 * W - 1; 0; PatternCount( Stops; " " ) <> W - 1; 0;

Position( Stops; "#"; 0; 1 ); 0;

Let([ A = "ABCDEFGHIJKLMNOPQRSTUVWXYZ# "; F = Filter( Upper(Substitute( Stops; ["@"; "#"]; ["/"; "#"] )); A )];

Case(

PatternCount( Middle( F; 2; 2 ); "#" ); 0;

W > 1; Case( PatternCount( Middle( F; 6; 2 ); "#" ) or Middle( F; 4; 1) <> " "; 0;

W > 2; Case( PatternCount( Middle( F; 10; 2 ); "#" ) or Middle( F; 8; 1) <> " "; 0;

W > 3; Case( PatternCount( Middle( F; 14; 2 ); "#" ) or Middle( F; 12; 1) <> " "; 0;

W > 4; Case( PatternCount( Middle( F; 18; 2 ); "#" ) or Middle( F; 16; 1) <> " "; 0;

W > 5; Case( PatternCount( Middle( F; 22; 2 ); "#" ) or Middle( F; 20; 1) <> " "; 0;

W > 6; Case( PatternCount( Middle( F; 26; 2 ); "#" ) or Middle( F; 24; 1) <> " "; 0;

W > 7; Case( PatternCount( Middle( F; 30; 2 ); "#" ) or Middle( F; 28; 1) <> " "; 0;

W > 8; Case( PatternCount( Middle( F; 34; 2 ); "#" ) or Middle( F; 32; 1) <> " "; 0;

W > 9; Case( PatternCount( Middle( F; 38; 2 ); "#" ) or Middle( F; 36; 1) <> " "; 0;

W > 10; Case( PatternCount( Middle( F; 42; 2 ); "#" ) or Middle( F; 40; 1) <> " "; 0;

W > 11; Case( PatternCount( Middle( F; 46; 2 ); "#" ) or Middle( F; 44; 1) <> " "; 0;

W > 12; Case( PatternCount( Middle( F; 50; 2 ); "#" ) or Middle( F; 48; 1) <> " "; 0;

W > 13; Case( PatternCount( Middle( F; 54; 2 ); "#" ) or Middle( F; 52; 1) <> " "; 0;

W > 14; Case( PatternCount( Middle( F; 58; 2 ); "#" ) or Middle( F; 56; 1) <> " "; 0;

W > 15; Case( PatternCount( Middle( F; 62; 2 ); "#" ) or Middle( F; 60; 1) <> " "; 0;

W > 16; Case( PatternCount( Middle( F; 66; 2 ); "#" ) or Middle( F; 64; 1) <> " "; 0;

W > 17; Case( PatternCount( Middle( F; 70; 2 ); "#" ) or Middle( F; 68; 1) <> " "; 0;

W > 18; Case( PatternCount( Middle( F; 74; 2 ); "#" ) or Middle( F; 72; 1) <> " "; 0;

W > 19; Case( PatternCount( Middle( F; 78; 2 ); "#" ) or Middle( F; 76; 1) <> " "; 0;

W > 20; Case( PatternCount( Middle( F; 82; 2 ); "#" ) or Middle( F; 80; 1) <> " "; 0;

W > 21; Case( PatternCount( Middle( F; 86; 2 ); "#" ) or Middle( F; 84; 1) <> " "; 0;

W > 22; Case( PatternCount( Middle( F; 90; 2 ); "#" ) or Middle( F; 88; 1) <> " "; 0;

W > 23; Case( PatternCount( Middle( F; 94; 2 ); "#" ) or Middle( F; 92; 1) <> " "; 0;

W > 24; Case( PatternCount( Middle( F; 98; 2 ); "#" ) or Middle( F; 96; 1) <> " "; 0;

W > 25; Case( PatternCount( Middle( F; 102; 2 ); "#" ) or Middle( F; 100; 1) <> " "; 0;

W > 26; Case( PatternCount( Middle( F; 106; 2 ); "#" ) or Middle( F; 104; 1) <> " "; 0;

W > 27; Case( PatternCount( Middle( F; 110; 2 ); "#" ) or Middle( F; 108; 1) <> " "; 0;

W > 28; Case( PatternCount( Middle( F; 114; 2 ); "#" ) or Middle( F; 112; 1) <> " "; 0;

W > 29; Case( PatternCount( Middle( F; 118; 2 ); "#" ) or Middle( F; 116; 1) <> " "; 0;

W > 30; Case( PatternCount( Middle( F; 122; 2 ); "#" ) or Middle( F; 120; 1) <> " "; 0;

W > 31; Case( PatternCount( Middle( F; 126; 2 ); "#" ) or Middle( F; 124; 1) <> " "; 0;

W > 32; Case( PatternCount( Middle( F; 130; 2 ); "#" ) or Middle( F; 128; 1) <> " "; 0;

W > 33; Case( PatternCount( Middle( F; 134; 2 ); "#" ) or Middle( F; 132; 1) <> " "; 0;

W > 34; Case( PatternCount( Middle( F; 138; 2 ); "#" ) or Middle( F; 136; 1) <> " "; 0;

W > 35; Case( PatternCount( Middle( F; 142; 2 ); "#" ) or Middle( F; 140; 1) <> " "; 0;

W > 36; Case( PatternCount( Middle( F; 146; 2 ); "#" ) or Middle( F; 144; 1) <> " "; 0;

W > 37; Case( PatternCount( Middle( F; 150; 2 ); "#" ) or Middle( F; 148; 1) <> " "; 0;

W > 38; Case( PatternCount( Middle( F; 154; 2 ); "#" ) or Middle( F; 152; 1) <> " "; 0;

W > 39; Case( PatternCount( Middle( F; 158; 2 ); "#" ) or Middle( F; 156; 1) <> " "; 0; 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ); 1 ) ) ) ) ))

So far so good.

The field once validated is used as part of a data string that validates flight rules in a flight booking engine. The structure allowable by the engine has now been extended in order to minimize data entry and an alternative group separator has been permitted, which is the character"/".

Using a space separator is equivalent to using "AND" the addition of "/" is as an "OR" statement and can considerably reduce the number of lines required to be entered.... on a very basic example

"NYC MIA LAX"

"NYC MIA DFW"

"NYC SFO LAX"

"NYC SFO DFW" curently takes four lines but can be written as one

"NYC MIA/SFO LAX/DFW" ... on fields with many groups this can reduce data entry drastically (as well as reducing database size)

Can I amend the above validation to allow "/" to be used as both a permissable separator and a permissible leading character in any group of three? Or do i need to stick with the longer data entry?

[Peter1]

IsEmpty ( Stops ) or Length ( Filter ( Stops ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ?@/ ") ) = Length ( Stops )

and Length ( Stops ) <= 159 and

PatternCount ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute ( Substitute

( Substitute ( Substitute ( Substitute ( Substitute (

Case (

Left ( Stops; 1 ) = "/" ; "A" & Middle ( Stops ; 2 ; 999 ) ;

Left ( Stops; 1 ) = "@" ; "A" & Middle ( Stops ; 2 ; 999 );

Stops = "???" ; "AAA" ;

Stops ) & " "

; " /" ; " A" ) ; "//" ; " A" ) ; " @" ; " A" ) ; "/@" ; " A" );

"A" ; 1 ) ; "B" ; 1 ) ; "C" ; 1 ) ; "D" ; 1 ) ; "E" ; 1 ) ; "F" ; 1 ) ; "G" ; 1 ) ; "H" ; 1 ) ; "I" ; 1 ) ; "J" ; 1 ) ; "K" ; 1 ) ; "L" ; 1 ) ; "M" ; 1 ) ; "N" ; 1 ) ; "O" ; 1 ) ; "P" ; 1 ) ; "Q" ; 1 ) ; "R" ; 1 ) ; "S" ; 1 ) ; "T" ; 1 ) ; "U" ; 1 ) ; "V" ; 1 ) ; "W" ; 1 ) ; "X" ; 1 ) ; "Y" ; 1 ) ; "Z" ; 1 ) ; "@" ; 3 ) ; " " ; 2 ) ; "/" ; 2 ) ; "1112") = Length ( Stops & " " ) / 4