Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Validating a field by calculation

Chalkster
in The Left Brain

Featured Replies

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?

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

Create an account or sign in to comment

https://fmforums.com/topic/29781-validating-a-field-by-calculation/
Go to topic listing

Important Information

By using this site, you agree to our Terms of Use.

I accept

Configure browser push notifications
Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.