FormatNumber (FileMaker Pro Custom Function)

FormatNumber ( number, format ):

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

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

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. Placement of minus sign, credit and delta symbols can be placed 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

Leave a Reply Cancel reply

Recent Posts

Recent Comments

Archives

Categories

Administer