fmp17 Conditinal Formatting Validation

[lutfishah]

Dear All,

I am trying to put a conditional formatting validation which turns Red when the value is entered wrong. Basically all my tries have failed so I would like your help here.

The criteria of entering a value is 

xxxx-xxxx-xxxx-xxxx

1111-2222-3333-4444

1. Length of value is  = 19     OR      this should be a 16 digit number, in groups of 4 separated with dashes

2. Non-Digits characters are not valid.

Please could you help me with this and I will really appreciate. 

Many thanks

Regards

Lutfi

 

[comment]

Try this as the conditional formatting formula:

Substitute ( Yourfield ; [ "1" ; "0" ] ; [ "2" ; "0" ] ; [ "3" ; "0" ] ; [ "4" ; "0" ] ; [ "5" ; "0" ] ; [ "6" ; "0" ] ; [ "7" ; "0" ] ; [ "8" ; "0" ] ; [ "9" ; "0" ] ) ≠ "0000-0000-0000-0000" 

Note:

1. Conditional formatting is not validation.
2. You could make it easier for your users by letting them enter the digits only, and format the result yourself.

Edited October 1, 2019 by comment

[lutfishah]

Thanks for your reply

  On 10/1/2019 at 3:47 PM, comment said:

Try this as the conditional formatting formula:

Substitute ( Yourfield ; [ "1" ; "0" ] ; [ "2" ; "0" ] ; [ "3" ; "0" ] ; [ "4" ; "0" ] ; [ "5" ; "0" ] ; [ "6" ; "0" ] ; [ "7" ; "0" ] ; [ "8" ; "0" ] ; [ "9" ; "0" ] ) = "0000-0000-0000-0000" 

Note:

1. Conditional formatting is not validation.
2. You could make it easier for your users by letting them enter the digits only, and format the result yourself.

Expand  

Correct that Conditional formatting is not a validation but we just are using it for visual validation that if a value is incorrect then it should only highlight it by filling a red colour.

Please see my code below.

Case ( Filter (Left(myfield ; & Middle(myfield ;6; 4) & "-" & Middle(myfield ;11; 4) & "-" & Right(myfield ; 4);  "abcdefghijklmnopqrstuvwxyz_@!#$%&'*+/=?^_`{|}~")  ; 0 ; 1) = 0

or

If (Length ( myfield ) = 19 ; 1 ; 0 ) = 0

and

IsEmpty (myfield )  ≠  1

[comment]

  On 10/1/2019 at 3:59 PM, lutfishah said:

Please see my code below

Expand  

Does it work the way you want?

 

[lutfishah]

Sorry, it didn’t work

[comment]

I am afraid I no longer follow what's going on here.

[OlgerDiekstra]

How about:

Let([
     ~field = Filter( myField; "0123456789" )
    ];
    If( Length( ~field ) = 16 and Left( ~field; 4 ) & "-" & Middle( ~field; 6;4 ) & "-" & Middle( ~field; 11; 4 ) & "-" & Right( ~field; 4 ) = myField; 1; 0 )
   )

 

[lutfishah]

  On 10/1/2019 at 10:45 PM, OlgerDiekstra said:

How about:

Let([
     ~field = Filter( myField; "0123456789" )
    ];
    If( Length( ~field ) = 16 and Left( ~field; 4 ) & "-" & Middle( ~field; 6;4 ) & "-" & Middle( ~field; 11; 4 ) & "-" & Right( ~field; 4 ) = myField; 1; 0 )
   )

 

Expand  

Perfect @OlgerDiekstra you did it

It works. Great thanks

And thanks to @comment as well