Brian Dunning's FileMaker Custom Functions

Number_Value ( _var )

return a valid number according to regional setting or nothing if invalid

  Average rating: 4.6 (25 votes) Log in to vote

Rewolfer   Rewolfer - Show more from this author
http://rewolfer.de

Share on Facebook Share on Twitter

  Sample input:
".445"
  Sample output:
0,445

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*===============================================

FORMAT:
Number_Value ( _var )

PURPOSE:
returns a valid number according to regional setting/ global var $$NumberFormatAccepted or nothing

PARAMETER:
_var type: unknown

RESULT:
valid number in regional setting or nothing if invalid

DEPENDENCY:
-

NOTE:
assuming numbers (leading and trailing zeros/ spaces are ignored)

EXAMPLE:
Number_Value ( ".445" ) => 0,445 (in a FMP-file with German settings)

HISTORY:
2011-10-04 [000000] 1.20 tseidler original number function
2012-04-08 [095125] 1.21 TSW added number-exception ("00003" is not a number)
2012-04-09 [122301] 1.22 TSW added comma-separation
2012-04-11 [225457] 1.23 TSW removed zero-padding again
2012-04-18 [210556] 1.30 TSW switched to _delimiter
2012-04-19 [120433] 2.00 TSW aligned to other Is-functions: IsNumber returns value if valid or nothing if false
2012-04-19 [235920] 2.10 TSW caches file settings in $$IsNumberFormatAccepted on first run
2012-04-22 [101156] 2.11 TSW fixed converting bug
2012-09-18 [083745] 2.20 TSW eliminated one evaluation
2015-08-31 [092700] 3.00 TSW renamed to Number_Value
2015-10-21 [121200] 4.00 TSW complete rewrite
2018-02-01 [105700] 4.10 TSW "+" return debugged

DEVELOPER:
TS Wolf
=================================================*/

Let ([
// first run sets $$NumberFormatAccepted to either "." or ","
_tmp = Case ( IsEmpty ( $$NumberFormatAccepted ) ; Let ( $$NumberFormatAccepted = Case ( GetAsNumber ( "1,2" ) = 12 ; "." ; "," ) ; "" )) ;
_var = Case ( $$NumberFormatAccepted = "," ; Substitute ( _var ; "." ; "," ); Substitute ( _var ; "," ; "." )) ;
_cleanExp = Filter ( _var ; "-+eE0123456789 " & $$NumberFormatAccepted ) ;
_cleanNum = Filter ( _cleanExp ; "-+0123456789" & $$NumberFormatAccepted ) ;
_cleanDigit = Filter ( _cleanNum ; "0123456789" ) ;
_countE = PatternCount ( _cleanExp ; "e" ) ;
_countSign = PatternCount ( _cleanExp ; "-" ) ;
_countDec = PatternCount ( _cleanNum ; $$NumberFormatAccepted )

];

Case (
_var = "True" ; 1 ;
_var = "False" ; 0 ;
IsEmpty ( _cleanDigit ) ; "" ;

// simple number
_countE = 0 and _var = _cleanNum and _countDec < 2 and Case ( _countSign = 0 ; True ; _countSign = 1 and Left ( _cleanNum ; 1 ) = "-" and Length ( _cleanNum ) > 1 and Right ( _cleanNum ; 1 ) <> 0 ; True ; False ) ; _var ;

// exponential
_countE = 1 and _var = _cleanExp and _countDec < 2 ;

Let ([
_split = Substitute ( _var ; "e" ; ¶ ) ;
_splitCount = ValueCount ( _split ) ;
_base = Trim ( GetValue ( _split ; 1 )) ;
_baseSignCount = PatternCount ( _base ; "-" ) ;
_exponent = Trim ( GetValue ( _split ; 2 )) ;
_exponentSignCount = Length ( Filter ( _exponent ; "+-" ))

];

Case (
_splitCount = 2 and
Case ( _baseSignCount = 0 ; True ; _baseSignCount = 1 and Left ( _base ; 1 ) = "-" ; True ; False ) and
Case ( _exponentSignCount = 0 ; True ; _exponentSignCount = 1 and ( Left ( _exponent ; 1 ) = "-" or Left ( _exponent ; 1 ) = "+" ) ; True ; False ) ;
//result
_base * 10 ^ _exponent )
)
)
)

 

Comments

Gilles Plante   Gilles Plante
May 3, 2018
What is the difference between this CF and GetAsNumber( var ) from plain FileMaker ?
 
Rewolfer   Rewolfer
May 3, 2018
GetAsNumber ( "1,2" ) will result in nothing on an American system. But obviously it is a metric number.
So with this function you can define your target seperator (otherwise it will be checked with local system settings) and convert to this target system setting. I needed this function to import numbers from various regions of the world with no knowledge what seperator was used.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: