Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

Number_Value ( _var )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.6  (25 votes)
  Discuss this Custom Function

Rewolfer, -
-

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

Sample Input:
".445"
Sample Output:
0,445


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Description:

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

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 )
)
)
)

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

This is my Custom Function and I want to edit it

Discuss:

What is the difference between this CF and GetAsNumber( var ) from plain FileMaker ?

Gilles Plante, Québec
May 03, 2018 11:31am

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.

Rewolfer, Berlin
May 03, 2018 3:36pm

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 9 + 1 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. BinToFloat32IEEE ( binary )
  (Wed, Jun 27, 11:01pm)
2. match_WindowName_List ( Window_Names_List )
  (Sun, Jun 24, 8:15pm)
3. match_LayoutName_List ( LayoutNames_List )
  (Sun, Jun 24, 8:09pm)
4. SQL_Creator_Select ( wrapped ; table_name ; layout_name ; using_field_from ; list_fields_only ; use_distinct ; key_field_list ; separator_for_records ; separator_for_fields )
  (Sun, Jun 24, 5:38pm)
5. SortList ( theList )
  (Sat, Jun 23, 8:45am)
6. HexUUIDToNum_cf (_uuid)
  (Sat, Jun 16, 1:55pm)
7. httpResponseCode ( responseHeaders )
  (Tue, Jun 12, 10:40pm)
8. FindDuplicateCharacters ( string )
  (Fri, Jun 08, 5:01pm)

RSS Feed of Custom Functions