10 Replies Latest reply on Jun 28, 2011 9:51 AM by sportyguy209

    Newbie Trailing Zero and Rounding Question

    sportyguy209

      Title

      Newbie Trailing Zero and Rounding Question

      Post

      I'd like to have a field where I could type in a numeric value and if the number does not have a decimal place to give me one with a trailing 0, if it has a decimal to keep it, and if it is more than one decimal to round it to one.

      I.E. I'd like to be able to type in a number field "24" and get "24.0" (one decimal place) to show and if I type "24.5" for it to show 24.5" and if I type "24.55" for it to show "24.6" without using layout formatting. So, the unformatted data result would be "24.0", "24.5", and 24.6" respectively.

      What do I need to do to get this to work?

      TIA

        • 1. Re: Newbie Trailing Zero and Rounding Question
          philmodjunk

          It can be done with an auto-entered calculation, but why not just use the formatting on the field which is much simpler?

          Let ( [ d = Round ( Self ; 1 ) ; i = Int ( d ) ] ; If ( i  = d ; d & ".0" ; d ) )

          • 2. Re: Newbie Trailing Zero and Rounding Question
            sportyguy209

            PhilModJunk

            Thanks so much, your formula worked!

            You are so right that formatting the field would be much simpler, but going that route doesn't work.

            I'll try to simplify what I'm doing so if anyone else is in my situation needs to they can figure it out too.

            Basically I have field A filled with text, field B is the number, field C is text, and field D combines A, B, & C.

            A = (See Jane run)

            B = (25.0)

            C= (yards.)

            D= (A+B+C)

            With the formatting for field B set to decimal, one place, if the field had "25" in it, it would display as "25.0". However, in my combined field (D), the actual value would be, "25".

            So, I'd get "See Jane run 25 yards" instead of "See Jane run 25.0 yards".

            I'm guessing that when you take data from a formatted field, the formatting gets dropped. Is this why it wasn't working for me?

            • 3. Re: Newbie Trailing Zero and Rounding Question
              sportyguy209

              PhilModJunk -

              I'm trying to figure out how your formula works. I tried modifying the formula for two decimal places and it didn't seem to work. If I had a number that already had no decimal places or two, it worked, but if I had a number that had one decimal place, it didn't add the second 0.

              Let ( [ d = Round ( Self ; 2 ) ; i = Int ( d ) ] ; If ( i  = d ; d & ".00" ; d ) )

              What do I need to add?

              TIA

              • 4. Re: Newbie Trailing Zero and Rounding Question
                philmodjunk

                Let me ask another question, just in case you haven't considered the alternative: Why do you need to combine the text and number in this way? Can you do it with merge fields? If so, you can apply decimal formatting to the merge text as long as all the numbers in the merge text use identical formatting.

                I realize that there are cases where that isn't a practical option.

                Let ( [ d = Round ( Self ; 2 ) ; i = Int ( d ) ; f = ( d - i ) * 100 ] ; i & "." & Left ( f & "0" ; 2 ) )

                • 5. Re: Newbie Trailing Zero and Rounding Question
                  sportyguy209

                  PhilModJunk:

                  Brilliant, it worked! Thanks!

                  I hadn't considered using merge fields as I kept losing the formatting from the field every time I tried outputting it. Didn't realize that a merge field would maintain the formatting while other ways wouldn't. Seems a bit confusing...:-(

                  My solution is now working with your formula, so I'm good.:-)

                  • 6. Re: Newbie Trailing Zero and Rounding Question
                    philmodjunk

                    Didn't realize that a merge field would maintain the formatting while other ways wouldn't.

                    What other ways are there?

                    • 7. Re: Newbie Trailing Zero and Rounding Question
                      sportyguy209

                      Perhaps I wasn't being clear in my comment. The formatting never "stuck" for me when I tried to use the number(s) in a calculation field (adding numeric and non-numeric values) as previously mentioned.

                      A = (See Jane run)

                      B = (25.0)

                      C= (yards.)

                      D= (A+B+C)

                      When I would "add" them together, the formatting for the number in B wouldn't be correct. It would come in as 25 instead of 25.0.

                      I also could never import the values in the formatting I wanted, even if they were formatted the way I wanted them to begin with. FileMaker would change them when I imported them. For example, 2.0 would change to 2. FileMaker would also give me some very weird numeric values upon import for some numbers. For example, 8.7 might change to 8.666678 or something like that.

                      • 8. Re: Newbie Trailing Zero and Rounding Question
                        philmodjunk

                        Yes, rounding for export purposes may be necessary, though an XML based output might have an option that would round the numbers if you set up the correct 'grammer'.

                        And if you are combining text and number fields such as A & B & C, then you can setup merge fields on your layout as <<A>> <<B>> <<C>>, then select the entire block of text and specify your number formatting just like it was a number field. (I think you know this, but I'm spelling it out for others who might read this.)

                        • 9. Re: Newbie Trailing Zero and Rounding Question
                          sportyguy209

                          Actually, now that you mention it, I don't think merge fields would have worked for me because my calculation fields require numbers to be formatted differently (one decimal or two decimal places). Then, based on the calculation, different numbers would need to be used in the output. So, if I formatted them in the actual merge, I wouldn't get the result I was looking for.

                          For example, B might equal 4.5 or it might equal 45.70. If I used the merge field to format the numbers, they would all have to be the same (all one decimal place or all two decimal places.

                          • 10. Re: Newbie Trailing Zero and Rounding Question
                            sportyguy209

                            PhilModJunk I'm hoping that you're still out there or someone equally smart with formulas. I have been using this formula, which most times seems to work well for my goal of forcing all numbers to have two decimal places (only).

                            Let ( [ d = Round ( Self ; 2 ) ; i = Int ( d ) ; f = ( d - i ) * 100 ] ; i & "." & Left ( f & "0" ; 2 ) )

                            However, when I type in the following number "22.08", the result shows "22.80." A similar error occurs when there is any number with a zero in the first decimal place. What do I need to do to get the result to be accurate?

                            TIA