Shopping Cart

Free Newsletter
Signup

Contact

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

FormatNumberComplete ( number, format )

Rate this function:

Average rating: 3.7 (7 votes)
Discuss this Custom Function

Lewis C. Lorenz, Lorenz Companies
www.LorenzCom.com/software

Returns text string of number in the requested format. Function allows complete FileMaker Pro number formatting using an intuitive format structure.

Sample Input:

FormatNumberComplete ( 14725.3675, “$,.2 US” )
FormatNumberComplete ( -14725.3675, “$(,.2) US\r0\” )
FormatNumberComplete ( -14725.3675, “#,.\c255\” )
FormatNumberComplete ( 1, “Yes\/No” ) =

Sample Output:

$14,725.37 US
$(14,725.36) US [rounding off]
#-14,725.3675 [text color is red]
Yes

Let ( [ number = GetAsNumber ( number ); neg = number < 0; bool = PatternCount ( format; "\/" ); signs = "-¶-¶<¶>¶(¶)¶cr¶cr¶∆¶∆¶"; cmds = ",¶.¶1¶0¶1¶¶¶¶¶¶¶"; //OPTIONS cmds = If ( not bool; If ( PatternCount ( format; "\T" ); Middle ( format; Position ( format; "\T"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\T"; 1; 1 ); 2 ) - ( Position ( format; "\T"; 1; 1) + 2 ) ); "," ) & "¶" & If ( PatternCount ( format; "\D" ); Middle ( format; Position ( format; "\D"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\D"; 1; 1 ); 2 ) - ( Position ( format; "\D"; 1; 1) + 2 ) ); "." ) & "¶" & If ( PatternCount ( format; "\R" ); Middle ( format; Position ( format; "\R"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\R"; 1; 1 ); 2 ) - ( Position ( format; "\R"; 1; 1) + 2 ) ); "1" ) & "¶" & If ( PatternCount ( format; "\Z" ); Middle ( format; Position ( format; "\Z"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\Z"; 1; 1 ); 2 ) - ( Position ( format; "\Z"; 1; 1) + 2 ) ); "1" ) & "¶" & If ( PatternCount ( format; "\F" ); Middle ( format; Position ( format; "\F"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\F"; 1; 1 ); 2 ) - ( Position ( format; "\F"; 1; 1) + 2 ) ); "1" ) & "¶" & If ( PatternCount ( format; "\SL" ); Middle ( format; Position ( format; "\SL"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\SL"; 1; 1 ); 2 ) - ( Position ( format; "\SL"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\SR" ); Middle ( format; Position ( format; "\SR"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\SR"; 1; 1 ); 2 ) - ( Position ( format; "\SR"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\NL" ); Middle ( format; Position ( format; "\NL"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\NL"; 1; 1 ); 2 ) - ( Position ( format; "\NL"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\NR" ); Middle ( format; Position ( format; "\NR"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\NR"; 1; 1 ); 2 ) - ( Position ( format; "\NR"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\PL" ); Middle ( format; Position ( format; "\PL"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\PL"; 1; 1 ); 2 ) - ( Position ( format; "\PL"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\PR" ); Middle ( format; Position ( format; "\PR"; 1; 1) + 3; Position ( format; "\\"; Position ( format; "\PR"; 1; 1 ); 2 ) - ( Position ( format; "\PR"; 1; 1) + 3 ) ); "" ) & "¶" & If ( PatternCount ( format; "\C" ); Middle ( format; Position ( format; "\C"; 1; 1) + 2; Position ( format; "\\"; Position ( format; "\C"; 1; 1 ); 2 ) - ( Position ( format; "\C"; 1; 1) + 2 ) ); "" ); $cmds ); format = If ( not bool and PatternCount ( format; "\\" ); Substitute ( format ; ["\t"; "\T" ]; ["\d"; "\D" ]; ["\r"; "\R" ]; ["\z"; "\Z" ]; ["\f"; "\F" ]; ["\s"; "\S" ]; ["\n"; "\N" ]; ["\p"; "\P" ]; ["\c"; "\C" ]; ["\Sl"; "\SL" ]; ["\Sr"; "\SR" ]; ["\Nl"; "\NL" ]; ["\Nr"; "\NR" ]; ["\Pl"; "\PL" ]; ["\Pr"; "\PR" ]; ["\T" & GetValue ( cmds; 1 ) & "\\"; ""]; ["\D" & GetValue ( cmds; 2 ) & "\\"; ""]; ["\R" & GetValue ( cmds; 3 ) & "\\"; ""]; ["\Z" & GetValue ( cmds; 4 ) & "\\"; ""]; ["\F" & GetValue ( cmds; 5 ) & "\\"; ""]; ["\SL" & GetValue ( cmds; 6 ) & "\\"; ""]; ["\SR" & GetValue ( cmds; 7 ) & "\\"; ""]; ["\NL" & GetValue ( cmds; 8 ) & "\\"; ""]; ["\NR" & GetValue ( cmds; 9 ) & "\\"; ""]; ["\PL" & GetValue ( cmds; 10 ) & "\\"; ""]; ["\PR" & GetValue ( cmds; 11 ) & "\\"; ""]; ["\C" & GetValue ( cmds; 12 ) & "\\"; ""] ); format ); precision = Abs ( GetAsNumber ( Substitute ( format; [ GetValue ( cmds; 1 ); ""]; [ GetValue ( cmds; 2 ); ""] ) ) ); number = If ( PatternCount (format; "%" ); Abs ( number ) * 100; Abs ( Number ) ); number = If ( GetValue ( cmds; 3 ) and precision > 0; Round ( number ; precision ); number ); numbers = Substitute ( If ( PatternCount ( format; GetValue ( cmds; 1 ) ); NumToJText ( Int (number ); 1; 0 ); Int ( number ) ); ","; GetValue ( cmds; 1 ) ) & "¶" & Substitute ( GetAsText ( Mod ( number ; Int ( number ) ) ); GetValue ( cmds; 2 ); "" ); // NOTATIONS cmds = If ( IsEmpty ( GetValue ( cmds; 8 ) ) and IsEmpty ( GetValue ( cmds; 9 ) ); MiddleValues ( cmds ; 1 ; 7 ) & Substitute ( If ( PatternCount ( format; GetValue ( cmds; 2) ); Left ( format; GetAsNumber ( Position (format; GetValue ( cmds; 2); 1; 1 ) ) - 1 ); Substitute ( format; If ( PatternCount (format; "%"); "%"; "" ); "" ) ) & "¶" & If ( PatternCount ( format; GetValue ( cmds; 2) ); Middle ( format; GetAsNumber ( Position (format; GetValue ( cmds; 2); 1; 1 ) ) + 1; Length ( format ) ); If ( PatternCount (format; "%"); "%"; "" ) ); [GetValue ( cmds; 1 ); ""]; [ GetAsText ( precision ); ""] ) & "¶" & MiddleValues ( cmds ; 10 ; 3 ); cmds ); // NEGATIVE SIGN INDEX ndx = If ( neg; Case ( PatternCount ( MiddleValues ( cmds; 8; 2) ; "-" ); 1; PatternCount ( MiddleValues ( cmds; 8; 2); "<" ) or PatternCount ( MiddleValues ( cmds; 8; 2); ">" ); 3; PatternCount ( MiddleValues ( cmds; 8; 2); "(" ) or PatternCount ( MiddleValues ( cmds; 8; 2); ")" ); 5; PatternCount ( MiddleValues ( cmds; 8; 2); "cr" ); 7; PatternCount ( MiddleValues ( cmds; 8; 2); "∆" ); 9; 1 ); // Defaults to minus sign. "" ); // REMOVE UNUSED SIGNS cmds = If ( neg; MiddleValues ( cmds ; 1 ; 7 ) & Substitute ( MiddleValues ( cmds ; 8 ; 2 ) ; [ If ( ndx ≠ 1; GetValue ( signs; 1 ); "" ); "" ]; [ If ( ndx ≠ 1; GetValue ( signs; 2 ); "" ); "" ]; [ If ( ndx ≠ 3; GetValue ( signs; 3 ); "" ); "" ]; [ If ( ndx ≠ 3; GetValue ( signs; 4 ); "" ); "" ]; [ If ( ndx ≠ 5; GetValue ( signs; 5 ); "" ); "" ]; [ If ( ndx ≠ 5; GetValue ( signs; 6 ); "" ); "" ]; [ If ( ndx ≠ 7; GetValue ( signs; 7 ); "" ); "" ]; [ If ( ndx ≠ 7; GetValue ( signs; 8 ); "" ); "" ]; [ If ( ndx ≠ 9; GetValue ( signs; 9 ); "" ); "" ]; [ If ( ndx ≠ 9; GetValue ( signs; 10 ); "" ); "" ] ) & MiddleValues ( cmds ; 10 ; 3 ); cmds ); // PROXIMITY cmds = If ( neg and IsEmpty ( GetValue ( cmds; 10 ) ) and IsEmpty ( GetValue ( cmds; 11 ) ); MiddleValues ( cmds; 1; 9 ) & If ( ndx = 1 and not PatternCount ( format; GetValue ( signs; ndx ) ); "1¶1"; GetAsText ( Position ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ); 1; 1 ) = (Length ( GetValue ( cmds; 8 ) ) + 1) - Length ( GetValue ( signs; ndx ) ) ) & "¶" & GetAsText ( Position ( GetValue ( cmds; 9 ); GetValue ( signs; ndx + 1 ); 1; 1 ) = 1) ) & "¶" & MiddleValues ( cmds; 12; 1 ); cmds ); // Leave unchanged. // SIGNS cmds = If ( neg and IsEmpty ( GetValue ( cmds; 6 ) ) and IsEmpty ( GetValue ( cmds; 7 ) ); MiddleValues ( cmds; 1; 5 ) & Case ( ndx = 1; If ( PatternCount ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ) ) or not PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx ) ); GetValue ( signs; ndx ); "" ); // Left or default left. ndx = 3 or ndx = 5; GetValue ( signs; ndx ); ndx = 7; If ( PatternCount ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ) ) and PatternCount ( format; GetValue ( cmds; 2 ) ); "CR "; "" ); ndx = 9; If ( PatternCount ( GetValue ( cmds; 8 ); GetValue ( signs; ndx ) ) ; "▲"; "" ); "" ) & "¶" & Case ( ndx = 1; If ( PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx +1 ) ); GetValue ( signs; ndx +1 ); "" ); ndx = 3 or ndx = 5; GetValue ( signs; ndx +1 ); ndx = 7; If ( PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx +1 ) ) or not PatternCount ( format; GetValue ( cmds; 2 ) ); " CR"; "" ); ndx = 9; If ( PatternCount ( GetValue ( cmds; 9 ); GetValue ( signs; ndx +1 ) ); "▲"; "" ); "" ) & "¶" & MiddleValues ( cmds; 8; 5 ); cmds ); // Leave unchanged. // REMOVE SIGNS FROM NOTATIONS cmds = MiddleValues ( cmds ; 1 ; 7 ) & Substitute ( MiddleValues ( cmds ; 8 ; 2 ); ["<"; ""]; [">"; ""]; ["("; ""]; [")"; ""]; ["cr"; ""]; ["∆"; ""]; ["-"; ""] ) & MiddleValues ( cmds ; 10 ; 3 ); // RESULT NUMBER r = If ( not GetAsBoolean ( GetValue ( cmds; 4 ) ) and GetAsNumber ( GetValue ( numbers; 1 ) ) = 0 and GetAsNumber ( GetValue ( numbers; 2 ) ) = 0; ""; GetValue ( numbers; 1 ) & If ( PatternCount ( format; GetValue ( cmds; 2 ) ) ; Case ( IsEmpty ( precision ); GetValue ( cmds; 2 ) & GetValue ( numbers; 2 ); // Show full unaltered decimal. precision ≥ 1; GetValue ( cmds; 2 ) & Left ( GetValue ( numbers; 2 ); precision ) & If ( GetAsBoolean ( GetValue ( cmds; 5 ) ); Right ( GetAsText ( 10 ^ precision ); precision - Length ( GetValue ( numbers; 2 ) ) ); "" ); "" ); // Show no decimal if precision is 0. "" ) ); // Show no decimal if not requested ) Hide zero number end ). // RESULT NOTATIONS AND SIGNS r = If ( IsEmpty ( r ); ""; Case ( bool; If ( GetAsBoolean ( number ); Left ( format; Position ( format; "\/"; 1; 1 ) - 1); Middle ( format; Position ( format; "\/"; 1; 1 ) + 2; Length ( format ) ) ); neg; If ( GetAsBoolean ( GetValue ( cmds; 10 ) ); GetValue ( cmds; 8 ) & GetValue ( cmds; 6 ) ; GetValue ( cmds; 6 ) & GetValue ( cmds; 8 ) ) & r & If ( GetAsBoolean ( GetValue ( cmds; 11 ) ); GetValue ( cmds; 7 ) & GetValue ( cmds; 9 ); GetValue ( cmds; 9 ) & GetValue ( cmds; 7 ) ); // End of is negative. GetValue ( cmds; 8 ) & r & GetValue ( cmds; 9 ) ) ); // Default . // COLOR RGB PREPARATION cmds = If ( neg and not IsEmpty ( GetValue ( cmds; 12 ) ); MiddleValues ( cmds ; 1 ; 11 ) & Substitute ( MiddleValues ( cmds ; 12 ; 1 ); ","; "¶" ); cmds ); // Leave unchanged. // RESULT COLOR r = If ( IsEmpty ( r ); ""; If ( neg and ( not IsEmpty ( GetValue ( cmds; 12 ) ) or not IsEmpty ( GetValue ( cmds; 13 ) ) or not IsEmpty ( GetValue ( cmds; 14 ) ) ); TextColor ( r; RGB ( GetAsNumber ( GetValue ( cmds; 12 ) ); GetAsNumber ( GetValue ( cmds; 13 ) ); GetAsNumber ( GetValue ( cmds; 14 ) ) ) ); r ) ) ]; r // Return result. ) /* FormatNumber ( number, format ) Returns text string of number in the requested format. by Lewis C. Lorenz 11-11-2011 Created 11-15-2011 Modified number = number to format. format = format string. Function allows complete FileMaker Pro number formatting using an intuitive format structure. Inclusion, and placement, of the following characters in the format string controls the resulting formatted number string. Number formatting. , (comma/thousands separator) = integer portion of the number is "thousands separated". . (period/decimal point) = controls display of the number decimal when used in conjunction with included, or omitted, # (number). # (number) = controls the count of digits of the number decimal to display (also specifies the rounding precision): 0 = Number decimal and decimal point is not displayed. Enables positioning of negative signs and notations. 1 ... n = Truncation and rounding precision of the number decimal. "" = Decimal point with no number displays the unaltered number decimal. Negative signs control the formatted result of negative numbers. The signs listed below are in order of precedence. Any lower-precedence signs also in the format string are ignored during processing and do not appear in the formatted result. Minus sign, credit and delta symbols can be displayed on the left or right of the formatted number by placing them to the left or right of an included decimal point in the format string. Negative signs will be placed at their default location when no decimal point is included. - = Minus sign (default). Default position is at the left. <> = Angle brackets. Surrounds the number. () = Parentheses. Surrounds the number. CR = Credit symbol. Default position is at the right. ∆ = Delta symbol. Default position is at the left. (Darkened triangle is used.) Boolean formatting. \/ = escaped fore-slash divides boolean results. Left side is returned if number is true, right side is returned if number is false. Percent, currency and other notations can be placed on either side of the formatted number depending on whether you place them at the left or right of an included decimal point in the format string. Notations will be placed at their default position when no decimal point is included. % = Percent sign. Inclusion in format string forces multiplication of the number by 100 during processing. Default position is at the right. $, #, etc. = Dollar and pound signs, and other characters, can be included in your formatted number. Default position is at the left. Escaped characters can be used to override defaults and add formatting. Enclose each passed option in an ending escape (backslash). \T = thousands separator. Default is , (comma). \D = decimal point. Default is . (period). \R = Round to passed precision (default is true). \Z = Show number if zero (default is true). \F = Fill decimal with zeros to length of passed precision (default is true). \SL = Negative sign to appear on left side of number (default is minus sign). \SR = Negative sign to appear on right side of number. \NL = Notation to appear on left side of number. \NR = Notation sign to appear on right side of number. \PL = Proximity of negative sign on left side of number (default is next to number). \PR = Proximity of negative sign on right side of number (default is next to number). \C = Comma separated RGB number string to return colored negative number. Examples: FormatNumber ( 14725.3675, "$,.2 US" ) = $14,725.37 US FormatNumber ( -14725.3675, "$(,.2) US\r0\" ) = $(14,725.36) US [rounding off] FormatNumber ( -14725.3675, "#,.\c255\" ) = #-14,725.3675 [text color is red] FormatNumber ( 1, "Yes\/No" ) = Yes */
Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

FormatNumberComplete ( number, format )

Returns text string of number in the requested format.

by Lewis C. Lorenz

11-11-2011 Created
11-15-2011 Modified

number = number to format.
format = format string.

Function allows complete FileMaker Pro number formatting using an intuitive format structure. Inclusion, and placement, of the following characters in the format string controls the resulting formatted number string.

Number formatting.
, (comma/thousands separator) = integer portion of the number is "thousands separated".
. (period/decimal point) = controls display of the number decimal when used in conjunction with included, or omitted, # (number).
# (number) = controls the count of digits of the number decimal to display (also specifies the rounding precision):
0 = Number decimal and decimal point is not displayed. Enables positioning of negative signs and notations.
1 ... n = Truncation and rounding precision of the number decimal.
"" = Decimal point with no number displays the unaltered number decimal.

Negative signs control the formatted result of negative numbers. The signs listed below are in order of precedence. Any lower-precedence signs also in the format string are ignored during processing and do not appear in the formatted result. Minus sign, credit and delta symbols can be displayed on the left or right of the formatted number by placing them to the left or right of an included decimal point in the format string. Negative signs will be placed at their default location when no decimal point is included.
- = Minus sign (default). Default position is at the left.
<> = Angle brackets. Surrounds the number.
() = Parentheses. Surrounds the number.
CR = Credit symbol. Default position is at the right.
∆ = Delta symbol. Default position is at the left. (Darkened triangle is used.)

Boolean formatting.
\/ = escaped fore-slash divides boolean results. Left side is returned if number is true, right side is returned if number is false.

Percent, currency and other notations can be placed on either side of the formatted number depending on whether you place them at the left or right of an included decimal point in the format string. Notations will be placed at their default position when no decimal point is included.
% = Percent sign. Inclusion in format string forces multiplication of the number by 100 during processing. Default position is at the right.
$, #, etc. = Dollar and pound signs, and other characters, can be included in your formatted number. Default position is at the left.

Escaped characters can be used to override defaults and add formatting. Enclose each passed option in an ending escape (backslash).
\T = thousands separator. Default is , (comma).
\D = decimal point. Default is . (period).
\R = Round to passed precision (default is true).
\Z = Show number if zero (default is true).
\F = Fill decimal with zeros to length of passed precision (default is true).
\SL = Negative sign to appear on left side of number (default is minus sign).
\SR = Negative sign to appear on right side of number.
\NL = Notation to appear on left side of number.
\NR = Notation sign to appear on right side of number.
\PL = Proximity of negative sign on left side of number (default is next to number).
\PR = Proximity of negative sign on right side of number (default is next to number).
\C = Comma separated RGB number string to return colored negative number.

Examples:
FormatNumberComplete ( 14725.3675, "$,.2 US" ) = $14,725.37 US
FormatNumberComplete ( -14725.3675, "$(,.2) US\r0\" ) = $(14,725.36) US [rounding off]
FormatNumberComplete ( -14725.3675, "#,.\c255\" ) = #-14,725.3675 [text color is red]
FormatNumberComplete ( 1, "Yes\/No" ) = Yes

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:

There are no comments yet. Be the first to post a comment about this Custom Function! Please try to keep it brief & to the point. Anyone can post:

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

Newest Custom Functions:

1.	FilteredPortalList ( fieldObjectName ; portalRow )
	(Tue, May 21, 5:33pm)
2.	ListToggle ( InList ; InValue )
	(Thu, May 16, 6:59pm)
3.	VatFormat ( vat )
	(Tue, May 14, 2:45am)
4.	CheckVatBE ( vat )
	(Tue, May 14, 2:32am)
5.	CheckIBAN (IBAN )
	(Tue, May 14, 2:24am)
6.	OrcidCheckDigit ( Orcid_id ; Result ; Iterations )
	(Wed, May 08, 1:44am)
7.	DateRangeBySpan ( Begin ; End ; Term)
	(Mon, May 06, 6:53am)
8.	Convert_Arabic_to_Chinese( number )
	(Thu, May 02, 6:10am)


			[ Close Window ]
	The class that teaches you how to successfully sell your FileMaker Pro solution as a professional shrinkwrapped software application.
	Let's Meet in Person Sunday, August 13 2006, Orlando FL Courtyard Orlando International Drive
	Don't miss this most important day of the Developer Conference. Please click here for complete details and online registration.

FileMaker Pro Custom Functions

Description:

Discuss:

Let's Meet in Person