Validating data entry

[Chalkster]

A little while ago I wrote in with a problem

"I have a couple of fields that must have data entered in a perticular format and would like to find a way of validating the data.

basicall the data must be entered as groups of 3 letters with a space separation between groups

eg LAX NYC MIA

There can be no spaces at the start of the field, there must be only one space between each group and the groups must consist of 3 letters, or a specific symbol (@ or / followed by two letters).

The number or groups entered in one field can be anything from 1 to 40.

I can subdivide the field into 40 data entry fields but since 95% of the time there are less than 5 groups entered it seems a waste of space .

Any way to write a calculation check and validate a full string or will I have to split the data into multiple fields? "

The solution ... courtesy of all the help I received was

(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 ) ) ) ) ))

Which solved the issue

However the program that uses the data (an internet flight booking engine) has been extended and now can accept a slightly different format to reduce the number of lines per record.

The original data format uses the thre letter groupings as codes for cities/airports and the space symbolizes "and".

All the above rules still apply but as well as using a space separator between the three letter groupd a "/" can be used to denote "or"

So where originally multiple strings had to be produced to cover possible permutaions a single string could now be entered. A simple example

NYC LAX MIA

NYC LAX IAH

originally two lines could be entered as NYC LAX MIA/IAH

Is it possible to extend the validation to allow for the use of a / between the groups as well as a space? If I'm asking too much feel free to tell me:)

[Peter1]

If I understand your query users can only enter alphabetical values and the "@" symbol in 3-letter sequences. A possible validation calculation could also be:

Left ( Stops; 1 ) <> " " and Left ( Stops; 1 ) <> "/" and Right ( Stops ; 1 ) <> " " and Right ( Stops ; 1 ) <> "/"

and

PatternCount ( Stops ; " " ) = 0 and PatternCount ( Stops ; "//" ) = 0

and

Length ( Filter ( Stops ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz@/ ") ) = Length ( Stops )

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 ( Stops ; "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 ) ; "@" ; 1 ) ; "111" ; "X" ) ; "XX" ) = 0

and

GetAsNumber ( 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 ( Stops ; "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 ) ; "@" ; 1 ) ; "111" ; "X" ) ) = 0

This does not set any limit on the number of sequences but this could easily be fixed by adding an additional line:

Length (Stops) <= 159

Peter

[Peter1]

A simpler alternative:

Length ( Filter ( Stops ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz@/ ") ) = Length ( Stops )

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 ( Stops & " " ; "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 ) ; "@" ; 1 ) ; " " ; 2 ) ; "/" ; 2 ) ; "1112") = Length ( Stops & " " ) / 4

Peter

[Chalkster]

No... thanks for the try but the rules are very precise..... as originally the three letter groups can start with characters A-Z or @ or/, the remaining two characters must be a letter A-Z, the @ and / characters are not permitted as second or third chacters in the groups

Separators can be space or "/"

There is no leading separator and no separator after the final group of 3.... only one separator permitted between each group and a max of 40 groups.

Any extra separators or invalid characters means the entire string is discarded since this represents only one element of the complete string (which involves approx 18 different data fields.

[Peter1]

OK, what about:

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 ) & " "

; " /" ; " 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

[-Queue-]

I don't have time to get back into this beast right now--I might later--but I would suggest using the new Substitute syntax to simplify the calculation, if it works. For example,

Substitute ( Substitute ( Substitute ( Substitute (

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

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

=>

Substitute(

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

[" /"; " A"]; ["//"; " A"]; [" @"; " A"]; ["/@"; " A"] )

[Ugo DI LUCA]

Hi,

a while back I didn't posted this as yours and the one from Murphy solved the case, but I've made a few tries just now and adjusted it, and may be this might work.

Let([

M= Upper (input) & " ";

Filter= Filter(M;"ABCDEFGHIJKLMNOPQRSTUVWXYZ?/@ ");

Mass=Substitute(Filter;["A";"1"];["B";"1"];["C";"1"];["D";"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"];["@";"111"];

["/11 ";"111 "];["/";" "])];

(WordCount (Mass)*3 = PatternCount(Mass;"1")) and (PatternCount(Mass;" ")=WordCount (Mass))

or Mass="??? ")

This method changes each string of letters, or the "@", or a "/xx" to a "111" structure, and makes sure the final numbers of "111" correspond to the number of spaces, or that the converted formula reads as "???"

A "/" is then simply changed to a space to accomodate the ultimate need, and it can't be confused with a "/xx" because we matches a "/xx " (note the last space here) in the substitution.

In order to catch a final "/xx" in the string, we added a space in the first M parameter.

So at the end

ASD FRT/GHT YHU//ER is accepted now.

I wouldn't bet it is reliablke at 100% though.