Name & Parameters: [color:red][big] isNumeric ( value ) [/big]

Description: This function checks if a value is numerical. If it is the case it return true otherwise false.

Sample Input:

"-1234.423"

Results:

True

Recursive: no

Formula:

Let

(

   [

   trimmedValue = Trim (value);

   filteredValue = Filter ( trimmedValue ; "0123456789.-" );

   lengthOk =    If (Length (trimmedValue) = Length (filteredValue);  True;  False);



   dotOccurence = PatternCount (filteredValue; ".");

   dotOk = If (dotOccurence = 0 or dotOccurence = 1; True; False);



   minusIndex = Position (filteredValue; "-"; 1; 1);

   minusOk = If (minusIndex = 0 or minusIndex = 1; True; False)

   ];

   If (lengthOk and dotOk and minusOk;

      True;

   /*else*/

      False

   )

) 

Required Functions:

Author(s) El_Pablo

Date: 07/19/07

Credits:

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

I had trouble compiling any conditional expressions containing 'and' or 'or'. Had to wrap each side with parens to get custom functions to accept.

e.g. Had to rewrite as follows:

minusOk = If ( ( minusIndex = 0 ) or ( minusIndex = 1 ); True; False)

Also the minus check should involve a pattern count, like the dot check.

Like so...

Let (

   [

        trimmedValue = Trim (value);

        filteredValue = Filter ( trimmedValue ; "0123456789.-" );

        lengthOk =    If (Length (trimmedValue) = Length (filteredValue);  True;  False);



        dotOccurrence = PatternCount (filteredValue; ".");

        dotOk = If ( (dotOccurrence = 0 ) or (dotOccurrence = 1); True; False);



        minusOccurrence = PatternCount ( filteredValue ; "-" );

        minusIndex = Position (filteredValue; "-"; 1; 1);

        minusOk = Case ( minusOccurrence = 0 ; True ;

                                     minusOccurrence = 1 and minusIndex = 1 ; True;

                                     False )

   ];

        If ( ( lengthOk ) and ( dotOk ) and ( minusOk) ;

           True;

        /*else*/

           False

        )

)

Edited July 26, 200718 yr by Guest

You don't need the IF statements to return a true or false result.

the function "( minusIndex = 0 ) or ( minusIndex = 1 ) will return boolean true or false just as they are.

Ditto with some of the others.

However, I prefer to work with 1 or 0 rather than true or false. It makes tracking things much easier.

Let (



[

trimmedValue = Trim( value ) ;

filteredValue = Filter( trimmedValue ; "0123456789.-" ) ;

lengthOk =  Length(trimmedValue ) = Length( filteredValue ) ;



dotOccurrence = PatternCount( filteredValue ; "." ) ;

dotOk = ( dotOccurrence = 0 ) or ( dotOccurrence = 1 ) ;



minusOccurrence = PatternCount( filteredValue ; "-" ) ;

minusIndex = Position(filteredValue ; "-" ; 1 ; 1) ;

minusOk = Case( 

          minusOccurrence = 0 ; 1 ; 

          minusOccurrence = 1 and minusIndex = 1 ; 1 ;

          0 )

] ;



lengthOk and dotOk and minusOk



)

I'm sure some clever person will work out how to simplify the Case function with Choose().

Thanx!

I didn't know that the "if" statement was optional. Since I always code in real programming language such as C#, I always put more lines in my custom function to make it easier to compare and read.