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

    Set tooltips, formatting numbers

    miw

      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
          Orlando
            

          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
            miw
               Orlando:  Thanks for the answer - it's a long formula but it works great
            • 3. Re: Set tooltips, formatting numbers
              raybaudi
                

              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
                  

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

                    What does this part do:

                     

                    e = Sign ( Self ) / 10 ^ 3 ;

                    • 7. Re: Set tooltips, formatting numbers
                      raybaudi
                        

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

                          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
                              

                            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
                                

                              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
                                  

                                1) I beg your pardon?!!!

                                 

                                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
                                  raybaudi
                                    

                                  comment wrote:

                                   

                                  1) I beg your pardon?!!!


                                  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
                                    comment_1
                                      

                                    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 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