
1. Re: Set tooltips, formatting numbers
Orlando Mar 19, 2009 2:10 PM (in response to miw)Hi MIW,
The only way I can think would be to use a calculation to convert the number to the required format.
A calculation I use, and you can use this in the Set Tooltip directly or set it up as a Custom Function, will also put thousand separators into the number.
Let ( number = TABLE::Field
; // Start of calculation
"$" &
Case(
Length( Int( number ) ) = 9 , Left( Int( number ) , 3 ) & ","& Middle( Int( number ), 4 , 3 ) & ","& Right( Int( number ) , 3 ) ,
Length( Int( number ) ) = 8 , Left( Int( number ) , 2 ) & ","& Middle( Int( number ), 3 , 3 ) & ","& Right( Int( number ) , 3 ) ,
Length( Int( number ) ) = 7 , Left( Int( number ), 1 ) & ","& Middle( Int( number ), 2 , 3 ) & ","& Right( Int( number ), 3 ) ,
Length( Int( number ) ) = 6 , Left( Int( number ), 3 ) & ","& Right( Int( number ), 3 ) ,
Length( Int( number ) ) = 5 , Left( Int( number ), 2 ) & ","& Right( Int( number ), 3 ) ,
Length( Int( number ) ) = 4 , Left( Int( number ), 1 ) & ","& Right( Int( number ), 3 ) ,
Int( number )
)
& // Calculate decimal
If( number  Int( number ) > 0 ,
If( Length( Round( number  Int( number ) , 2 ) ) = 2 ,
Round( number  Int( number ) , 2 ) & "0",
Round( number  Int( number ) , 2 ))
, ".00" )
) // End of LetI hope this helps

2. Re: Set tooltips, formatting numbers
miw Mar 19, 2009 6:55 PM (in response to miw)Orlando: Thanks for the answer  it's a long formula but it works great 
3. Re: Set tooltips, formatting numbers
raybaudi Mar 20, 2009 1:29 AM (in response to miw)Hi Orlando
Orlando wrote:the only way I can think would be to use a calculation to convert the number to the required format.
A calculation I use, and you can use this in the Set Tooltip directly or set it up as a Custom Function, will also put thousand separators into the number.
How about this one:
Let([
s = If ( Sign ( Self )  1 ; "" ) ;
e = Sign ( Self ) / 10 ^ 3 ;
n = Abs ( Self + e )
];
Case ( n ; "$ " & s & NumToJText ( Int ( n ) ; 1 ; 0 ) & Left ( Mod ( n ; 1 ) ; 3 ) ; 0 )
) 
4. Re: Set tooltips, formatting numbers
Orlando Mar 20, 2009 2:16 AM (in response to miw)Thanks Daniele,
Works much better than mine, think I will use this from now on I think, much appreciated.

5. Re: Set tooltips, formatting numbers
raybaudi Mar 20, 2009 3:23 AM (in response to miw)The real power comes from the seldom unknown NumToJText ( ) native FileMaker function. 
6. Re: Set tooltips, formatting numbers
comment_1 Mar 20, 2009 4:52 AM (in response to miw)What does this part do:
e = Sign ( Self ) / 10 ^ 3 ;

7. Re: Set tooltips, formatting numbers
raybaudi Mar 20, 2009 5:06 AM (in response to miw)Hi comment
that part controls the number of displayed decimals.
To show 2 decimals : 10 ^ 3
To show 3 decimals : 10 ^ 4... and so on.

8. Re: Set tooltips, formatting numbers
comment_1 Mar 20, 2009 5:47 AM (in response to miw)And if the number is 0.999? 
9. Re: Set tooltips, formatting numbers
raybaudi Mar 20, 2009 8:26 AM (in response to miw)Good catch !
I was wrong: that part was supposed to control the number of decimal to add even if there wasn't decimals.
The bug ( that you found ) is that the number was supposed to have max 2 decimals.
If the user enters more than 2 decimals, the result is wrong.This seems to work better:
Let([
s = If ( Sign ( Self )  1 ; "" ) ;
e = Sign ( Self ) / 10 ^ ( Length ( Mod ( Self ; 1 ) ) + 2 ) ;
n = Abs ( Self + e )
];
Case ( n ; "$ " & s & NumToJText ( Int ( n ) ; 1 ; 0 ) & Left ( Mod ( n ; 1 ) ; 3 ) ; 0 )
)BTW: I created that calculation for other problems that we can meet:
1) exporting a number field and its formatting
2) show a formatted number in a custom dialog
3) show more than 1 number ( with different formatting ) into a merge field
4) put the result in a real number field. 
10. Re: Set tooltips, formatting numbers
comment_1 Mar 20, 2009 8:46 AM (in response to miw)It's still wrong, because 0.999 should return "$1.00", not "$ 0.99". Also, I am puzzled why you exclude 0 from being formatted to "$0.00" as one would expect (and as does Filemaker's number formatting).
I would suggest a simple:
Let (
a = Abs ( Round ( Amount ; 2 ) )
;
Case ( Amount < 0 ; "" ) & "$"&
NumToJText ( Int ( a ) ; 1 ; 0 ) & SerialIncrement ( ".00" ; 100 * Mod ( a ; 1 ) )
) 
11. Re: Set tooltips, formatting numbers
raybaudi Mar 20, 2009 9:59 AM (in response to miw)Two minor bugs also into your calc...
1) because 0.19 should return $0.19 ( as does Filemaker's number formatting ) not $0.20
2) because the calc evaluates even if the field is empty giving $.00
Also, to let be the calc more generic ( valid for other countries ), there must be a DecimalSep and a Thousandsep variable.

12. Re: Set tooltips, formatting numbers
comment_1 Mar 20, 2009 10:13 AM (in response to miw)1) I beg your pardon?!!!
2) I never do that in posted formulae, because it is trivial and nonessential to the real issue. If the calc field is set to the default "Do not evaluate…", then it's moot anyway.
As for more generic  it can be done, of course, but if it's not required...

13. Re: Set tooltips, formatting numbers
raybaudi Mar 20, 2009 10:37 AM (in response to miw)
comment wrote:1) I beg your pardon?!!!
Sure ! ;)
comment wrote:2) I never do that in posted formulae, because it is trivial and nonessential to the real issue. If the calc field is set to the default "Do not evaluate…", then it's moot anyway.

14. Re: Set tooltips, formatting numbers
comment_1 Mar 20, 2009 10:56 AM (in response to miw)
raybaudi wrote:
comment wrote:1) I beg your pardon?!!!
Sure ! ;)
I am sorry, but that's not a good answer. The calc as posted returns "$0.19" when Amount is 0.19, NOT "$.20" as you have suggested. I suggest you either back up your claim or retract it explicitly.
As for the rest, I already said it was trivial and nonessential, and I don't see what possible purpose can be there be to continue dwelling on it, other than to get me angry  which you have now achieved.