12 Replies Latest reply on Aug 28, 2015 9:43 AM by erolst

    Round function, keeping .00

    AndrewJudd

      Hi all, I assume this is the place for new forum posts? Does anyone know how to use the Round function but keep a .00 displayed when using a calculation field?

       

      I am using the below for a sales tax calculation field. If the sales tax is exact (ie $250.00) it only displays $250.

       

      Thank you!

       

      Round (Sales Tax ; 2)

        • 1. Re: Round function, keeping .00
          mikebeargie

          Just set the display options of the field to display currency with 2 decimal places. This will force it to show the two decimal places.

           

          Field display options are under the inspector, in the data tab.

          • 2. Re: Round function, keeping .00
            AndrewJudd

            Thanks Mike, unfortunately the way my invoice layout works is I need the field set to "as entered". This is because the rest of the calculation determines what currency symbol to add before the number (below).

             

            Depending on what boxes are ticked on the layout.

             

            Is there another way?

             

            Thanks!

             

            If (IsEmpty (INVOICES::Change to EURO & INVOICES::Change to UK); "$ "  & Round (Sales Tax ; 2))

             

            & If (not IsEmpty (INVOICES::Change to EURO); "€ "  & Round (Sales Tax ; 2))

             

            & If (not IsEmpty (INVOICES::Change to UK); "£ "  & Round (Sales Tax ; 2))

            • 3. Re: Round function, keeping .00
              mikebeargie

              You can use the "hide object when" to stack multiple formatted currency fields on top of each other, and format each own appropriately. This way all the field formats you don't need are hidden if the "hide object when" calc is filled in.

               

              Alternatively, I seem to recall someone doing something like this as well:

               

              Let ([

                 yourNum = your numeric calculation;

                 str = yourNum & "00"

              ];

                 truncate ( str ; 2 )

              )

               

              That might work for a text string, filemaker natively will trim the trailing zeros off any number though, so I'm not sure it would work.

              • 4. Re: Round function, keeping .00
                AndrewJudd

                thanks Mike, funnily enough that it was I used to have but thought it was better all details - currency included - were all in one box. It makes it easier and neater when objects have to slide up on print. I'll try that let! thanks

                • 5. Re: Round function, keeping .00
                  erolst

                  Another way would be to

                   

                  • calculate the currency symbol separately

                  • create a merge object from that symbol and the calculated number

                  • apply decimal formatting to that object.

                  • 6. Re: Round function, keeping .00
                    AndrewJudd

                    thanks, never used merge objects. how do they work? Doesn't seem to be much online about them...

                    • 7. Re: Round function, keeping .00
                      erolst

                      AndrewJudd wrote:

                      Doesn't seem to be much online about them...

                      You know that Filemaker has a built-in Help system?

                       

                      A merge object is a text object on your layout that can combine hard-coded text and field/variable references.

                       

                      Here's a merge object in Browse and Layout mode, respectively:

                       

                      Screen Shot 2015-08-28 at 15.37.18.png

                       

                      Check out the “Insert” menu in Layout mode.

                       

                      btw, Let() me suggest:

                       

                      Let (

                        currencySymbol =

                        Case (

                          not IsEmpty ( INVOICES::Change to EURO ) ; "€ " ;

                          not IsEmpty ( INVOICES::Change to UK ) : "£ " ;

                          "$ "

                        ) ;

                        currencySymbol & Round ( Sales Tax ; 2 )

                      )


                      But you shouldn't be doing that anyway, because the result type of this calc must be text, and then you cannot apply numeric formatting to that field or merge object. So I suggest you change this calc to just:


                      Case (

                        not IsEmpty ( INVOICES::Change to EURO ) ; "€ " ;

                        not IsEmpty ( INVOICES::Change to UK ) : "£ " ;

                        "$ "

                      )

                       

                      and rename it to, say, cCurrency (but leave it as text result). Then create the merge object by combining that field and your original salesTax field; now there is a numeric field in that object, and FileMaker lets you apply the numeric formatting.

                      • 8. Re: Round function, keeping .00
                        AndrewJudd

                        Cheers Erol, thats great. It's more or less like having a separate currency symbol with hide if set isn;'t it? I guess there is no way for it all to be in one field. Not the end of the world. thank you

                        • 9. Re: Round function, keeping .00
                          AndrewJudd

                          actually, when you say combine the two into an object how do you do that? I appreciate FM has a help but all mine seems to do is point towards there the names of things are in the menus.

                          • 10. Re: Round function, keeping .00
                            okramis

                            AndrewJudd schrieb:

                             

                            Hi all, I assume this is the place for new forum posts? Does anyone know how to use the Round function but keep a .00 displayed when using a calculation field?

                             

                            I am using the below for a sales tax calculation field. If the sales tax is exact (ie $250.00) it only displays $250.

                             

                            Thank you!

                             

                            Round (Sales Tax ; 2)

                             

                            You could use a custom function like this:

                             

                            //ValuesAsMoney ( _valuelist; _currency ) by Otmar Kramis

                            // usage: ValuesAsMoney ( "-0.7¶3.565¶-56.9¶44" ; "$ " )

                            // returns "$ -0.70¶$ 3.56¶$ -56.90¶$ 44.00"

                             

                            Let ( [

                            _n = ValueCount ( _valuelist )

                            ; _value = GetValue ( _valuelist ; 1 )

                            ; _negativ = If ( _value < 0 ; "-" ; "" )

                            ; _newvalue = _currency & _negativ & If ( _value = "" ; 0 ; Abs ( Int ( _value ) ) ) & "." & Middle ( ( Abs ( _value - Int ( _value ) ) ) & "000" ; 2 ; 2 )

                            ] ;

                            _newvalue

                            &

                            Case ( _n > 1 ; ¶ & ValuesAsMoney ( RightValues ( _valuelist ; _n - 1 ) ; _currency ) )

                            )

                            • 11. Re: Round function, keeping .00
                              okramis

                              minor correction for empty value(s):

                               

                              //ValuesAsMoney ( _valuelist; _currency )

                              // usage: ValuesAsMoney ( "-0.78¶3.56¶-56.94¶44" ; "$ " )

                              Let ( [

                              _n = ValueCount ( _valuelist )

                              ; _value = GetAsNumber ( GetValue ( _valuelist ; 1 ) )

                              ; _value = If ( _value = "" ; 0 ; _value )

                              ; _negativ = If ( _value < 0 ; "-" ; "" )

                              ; _newvalue = _currency & _negativ & Abs ( Int ( _value ) ) & "." & Middle ( ( Abs ( _value - Int ( _value ) ) ) & "000" ; 2 ; 2 )

                              ] ;

                              _newvalue

                              &

                              Case ( _n > 1 ; ¶ & ValuesAsMoney ( RightValues ( _valuelist ; _n - 1 ) ; _currency ) )

                              )

                              • 12. Re: Round function, keeping .00
                                erolst

                                AndrewJudd wrote:

                                actually, when you say combine the two into an object how do you do that? I appreciate FM has a help but all mine seems to do is point towards there the names of things are in the menus.

                                 

                                The Help menu has a Search entry that does exactly that; but there should also be a “FileMaker Pro (Advanced) Help” command, which opens the official, built-in manual & documentation (which, sadly, lacks an index …) If you don't see that entry, there is either a Custom Menu activated, or your FM installation is incomplete …

                                 

                                Be that as it may: have a look into the attached sample file; it features a merge text object combined from two field references, with numeric formatting applied.