I need a calculation to format a text number (i.e. 123456789 into 1,234,567.89) that will work in FMP.p advanced. Any ideas?? -JIm H
Have a look at this custom function that will format your number with thousand separators for you
FileMaker Custom Function: NumberFormat ( Number ; DecimalPrecision ; ThousandSeparator )
There are other variations on that site, but this is one I have used in the past
I hope this helps
I wrote this for phone numbers but it will work for any number format you want. I just added a format for commas and decimal and removed leading commas and spaces from the output.
/* Specify digits with # and separators you require. #'s are replaced with the numbers specified and separators are retained in their positions. Missing digits are padded with a space and excess digits are cut off from the left. Useful for phone numbers, etc. Example:
Format = (###) ###-####Number = 1234567890Output = (123) 456-7890*/
Let ( [
a = GetAsNumber ( Filter ( Number ; "0123456789" ) ) ; d = If ( IsEmpty ( a ) ; "" ; Let ( [ b = "###,###,###,###,###,###,###,###,###,###.##" ; i = Length ( b ) ; ii = Length ( a ) ; d = Substitute ( 10 ^ i - 1 ; 9 ; "x=Middle("& Quote ( b ) &";i;1);y=(x=\"#\");z=Case(y and ii;Middle("& Quote(a) &";ii;1);y and not ii;Char(32);x)&z;i=i-1;ii=If(y and ii;ii-1;ii);" ) ; d = Evaluate ( "Let([i="&i&";ii="&ii&";z=\"\";"&Left(d;Length(d)-31)&"];z)" ) ] ;
Substitute ( d ; [" ," ; ""] ; [" " ; ""] ))) ] ; d )
123456789 into 1,234,567.89
That's more than just formatting. The number 123456789 would be formatted as "123,456,789.00".
If you need "1,234,567.89" (i.e. you're converting cents into dollars), then you can do =
NumToJText ( Int ( Amount / 100 ) ; 1 ; 0 ) & SerialIncrement ( ".00" ; Mod ( Abs ( Amount ) ; 100 ) )
Another method that may or may not be more complex than the previous answers. It's just a trick that I discovered.
GetLayoutObjectAttribute("nbrFormat"; "content" )
This takes the field contents "12345678.90" (shows in the field as entered "12345678.9") and gives you:
Another method would be to write a tiny micro-service method in a REST micro-service you have handy and use object-oriented NumberFormat classes. There, you define your number format declaratively (no coding). REST services are ridiculously easy to create.
This approach has lots of useful side-effects (including not being FMP only, but working well with FMP). Below is a quick example showing you you just specify, using standard format strings, the format you want, and the micro-service just returns what you want (no coding or other one-off technique).
Let me know if you'd like more info.
I am still struggling with this format issue. The script is delivering a number from a SUMMARY field that is going into a SHOW CUSTOM DIALOG. What is happening is the number (i.e. 123,456.78) is formatting as (i.e. 123456.78). What I need to do is format it as its original state (123,456.78) AND --- hope it not too much to ask --- put a "$" as a prefix AND --- hope it not too much to ask x2 --- make a negative number display in RED......
I am working with FMP 9.0 Advanced, so maybe some of the newer calculations are not available and the old fashion work arounds are needed???
Thanks soooooo much in advance....
jlhfbo wrote: make a negative number display in RED
make a negative number display in RED
You cannot show styled text in a custom dialog.
jlhfbo wrote: What I need to do is format it as its original state (123,456.78) AND --- hope it not too much to ask --- put a "$" as a prefix
What I need to do is format it as its original state (123,456.78) AND --- hope it not too much to ask --- put a "$" as a prefix
If you have the field already on the layout, formatted to display as "$123,456.78" then use Beverly's suggestion. Otherwise you can use =
"$" & NumToJText ( Int ( Amount ) ; 1 ; 0 ) & SerialIncrement ( ".00" ; 100 * Mod ( Abs ( Amount ) ; 1 ) )
(this is assuming the amount is already rounded to two decimal places). All these functions are available since version 7.
If you want the minus sign to precede the currency (i.e. "-$123,456.78"), then use =
a = Abs ( Amount )
If ( Amount < 0 ; "-" ) & "$" & NumToJText ( Int ( a ) ; 1 ; 0 ) & SerialIncrement ( ".00" ; 100 * Mod ( a ; 1 ) )
You can 'format' the data type (summary as number in this case displayed as currency).
The function that I mentioned earlier, GetLayoutObjectAttribute has been around since FMP version 8.5, so may work for you here.
The product documentation for FMP 9 is found here. I would recommend downloading especially the Function Reference to study first.
Do your format of the field on the layout and then "get it" with the function. The (negative) RED may or may not apply with this function. I don't have version 9 to test this.
Yes, the summary field still needs to be named for this to work.
What is the script doing with the number once you would have it?
p.s. I just used the function and the COLOR of the negative did not apply, however, this can be fixed. But again, where does this value need to go? Perhaps you just need to get the value of the summary field and let it be formatted by Inspector wherever it goes?
Retrieving data ...