Filtering the content of a field

[Daniel Paquin]

OK, I am now jumping in using FM native functions. First one, verify for length of field work and second one for specified characters does not work.

Can someone tell me why?

Best regards!

Daniel

Manage database

Go to Field

Select field "Order1" + Options + Validation + Validated by calculation + specify

SPECIFY CALCULATION WINDOW

Validation for field "Order1"=

Length (Order1) = 4

or

Length (Order1) = 9

or

Length (Order1) =14

....

THIS CALCULATED FUNCTION WORKS FINE









Manage database

Go to Field

Select field "Order2" + Options + Validation + Validated by calculation + specify



SPECIFY CALCULATION WINDOW

Validation for field "Order2"=

Filter (Order2;"1234567890 ")

THIS CALCULATED FUNCTION DOES NOT WORK!





I am going with small steps at a time.  In the end what I was thinking of having was for order1 and order2 was something like showed below. However, I still have to resolve the Filter problem.:





SPECIFY CALCULATION WINDOW

Validation for field "Order1"=

(Length (Order1) = 4

or

Length (Order1) = 9

or

Length (Order1=14)

and

Filter (Order1;"123456789)0 ")

Validation for field "Order2"=

(Length (Order2) = 4

or

Length (Order2) = 9

or

Length (Order2=14)

and

(Filter (Order2;"1234567890 "))

[Daniel Wood]

Hi Daniel,

Just to clarify what the filter function does:

Filter ( textToFilter ; filterText )

The result will be just the contents of textToFilter that appears in filterText, ie:

Filter ( "ABC123DEF" ; "01234567890 " ) will return just "123", all characters not in filterText are removed.

So, in your validation calculation above:

Filter (Order1;"123456789)0 ")

The result depends on the contents of order1. If Order1 contains any number, closing angle bracket, or space, then the result of that calculation will be some kind of string containing those characters. If Order1 contains none of them, the result is empty string.

NOW, the key here is that the validation calculation actually needs to return a boolean value, either 1/0, true/false etc. The result of the filter calculation will actually act as the boolean test. Any value of field will be converted into a number, and the number will act as the boolean check. Numbers that are not 0 will return as true, 0 as false, and any non-numeric strings evaluate to false, ie:

Filter ( "abc" ; "123456789)0 " ) = "" = False

Filter ( "123" ; "123456789)0 " ) = "123" = True

Filter ( "1( )" ; "123456789)0 " ) = "1( " = True (because all non-numeric characters are stripped during validation, leaving just 1 which is true)

The issue here might be what the result of your filter calculation is returning depending upon the value in your field.

Hope this helps give you some ideas as to what may be happening here.

[comment]

Two notes:

1. Filter (Order2;"1234567890 ") returns the filtered value of Order2. If Order2 contains "0123xyz456" the result will be "0123456". This result evaluates as True, because any number other than zero is True.

What you want is a False result when the filter finds characters that need to be removed, e.g.

Exact ( Order2 ; Filter ( Order2 ; "1234567890 ")

2. The AND operator is evaluated before OR.

[bruceR]

Filter (Order2;"1234567890 ")

THIS CALCULATED FUNCTION DOES NOT WORK!

Well, that's not true. Of course it works.

The question is, what is it that you expect it to do?

See above explanations.

Since this problem was already solved in the example script I provided, why are you trying to do it differently?

Note that if you are going to keep your existing length-test calc, it can be simplified as:

Mod(length(Order)+1);5)=0

Also, the existence of fields Order1, Order2, etc is suspicious. Usually this is a sign of a non-normalized design.

Can you post your file here?