1 2 Previous Next 15 Replies Latest reply on Mar 20, 2009 11:23 AM by raybaudi

Set tooltips, formatting numbers

Title

Set tooltips, formatting numbers

Post

I have a "set tooltip" for field in my layout which is a calculated result along with some text.  Is there a way to format the part that is the calculated result in the tooltip to show in "\$000.00" format?

• 1. Re: Set tooltips, formatting numbers

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 Let

I hope this helps

• 2. Re: Set tooltips, formatting numbers
Orlando:  Thanks for the answer - it's a long formula but it works great
• 3. Re: Set tooltips, formatting numbers

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.

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

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
The real power comes from the seldom unknown NumToJText ( ) native FileMaker function.
• 6. Re: Set tooltips, formatting numbers

What does this part do:

e = Sign ( Self ) / 10 ^ 3 ;

• 7. Re: Set tooltips, formatting numbers

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
And if the number is 0.999?
• 9. Re: Set tooltips, formatting numbers

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

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

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

2) I never do that in posted formulae, because it is trivial and non-essential 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

comment wrote:

Sure ! ;)

comment wrote:

2) I never do that in posted formulae, because it is trivial and non-essential to the real issue. If the calc field is set to the default  "Do not evaluate…", then it's moot anyway.

This calc isn't for a field, but for a ToolTip ( which evaluates always )

• 14. Re: Set tooltips, formatting numbers

raybaudi wrote:

comment wrote:

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 non-essential, 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.

1 2 Previous Next