11 Replies Latest reply on Nov 23, 2015 3:43 AM by Stu412

    Right aligned numbers with parentheses for negatives

    Stu412

      Hi there

       

      I'm still struggling with number alignment where negative (parentheses) are involved and trying to align all whole numbers to the thousand separator.

       

      I've had a look at this thread here: alignment of negative numbers . Using FileMaker Pro . Forum . FileMaker Forums

      and have has some suggestions previously around tab stops but noting I've tried seems to be working.

       

      What I need to happen is for 100,000 and (100,000), as whole numbers, to align perfectly by the thousand separator.  I already have many calculations behind the numbers on the reports and many reports as well, so ideally would like to avoid reprogramming each one.  It's the right alignment which is important, as I've attempted to illustrate below - the numbers should be aligned, the parantheses can be stuck out.

       

           ,000

           ,000)

       

      Thanks in advance

        • 1. Re: Right aligned numbers with parentheses for negatives
          Extensitech

          First, let me say that if I were you, I might just consider a different way of displaying negatives, such as red, or "-", or both.

          Second, let me confess that this sounded pretty easy to me at first... until I thought, "Oh, let me just whip up a simple example file..."

           

          What I found:

          • There really isn't an "alignment" option for this. Your options are left, right, center or justified, and none do what you want.
          • Excel really does do this better. See the simple Excel file I attached. In Excel, when you say that negatives are expressed with parentheses, positive numbers move over just a little to line up. This may be a good feature request.
          • Oddly enough, a tab that aligns on commas wouldn't work, because you may have more than one comma. Instead, I ended up using right tabs
          • To use tabs, you have to make the field (or in this case, the calculation field) text
          • In order to get to a tab, you need to insert a tab character. (For my solution, I had to insert two.)
          • Oddly, the right tabs are only going to work correctly if you left justify your field
          • Since the field is text, you can't use any of FM's number formatting tools on the inspector, so you have to insert your own parentheses, commas, etc.
          • When setting tab stops on a field, the tools certainly haven't gotten any better lately, and I could hardly blame anyone for not bothering to fight with them
          • Also when setting tab stops, keep in mind that the left and right padding play a role in where your tab stops should go, and whether you can even get to them.


          So, in short, not an easy thing. (I still think the parentheses of negative numbers may simply not be worth the trouble in FM).

           

          Still, I think I've gotten it to work in the attached FM file.

           

          I did use a custom function to put commas into the number string. Without that, this could probably still be done (I recall using a clunky, non-recursive calc in FM5), but it'd be even clunkier than it is now.

           

          In sum:

          • It can be done
          • I personally don't think it's worth it, at least with the current FM tool set
          • There's a potential feature request to be had here

           

          I'd kind of like someone else to find a more elegant solution, although I'm sure that when someone like BruceRobertson inevitably does, I'm going to feel kind of stupid (again).

           

          HTH

          Chris Cain

          Extenstiech

          • 2. Re: Right aligned numbers with parentheses for negatives
            keywords

            Re: "a tab that aligns on commas wouldn't work, because you may have more than one comma."

             

            You are correct, that doesn't work if there are multiple commas.

            It also doesn't work if there are NO commas (numbers from 0 to 999)

            BUT it does work if you can be sure there is always one, and only one (numbers from 1,000 to 999,999)

            • 3. Re: Right aligned numbers with parentheses for negatives
              siplus

              You can do the following:

               

              have a calculation field that adds a ( in front of the number, but white if the number is positive and black if not, then the number, then the same thing with a ). All values will have () but you won't see the ones belonging to positive numbers.

              • 4. Re: Right aligned numbers with parentheses for negatives
                siplus

                Another solution: have the field twice on the layout, each having a hide condition, self ≥ 0 and self < 0. Shift slightly the positive one to the left, by the size of a ).

                • 5. Re: Right aligned numbers with parentheses for negatives
                  alquimby

                  siplus,

                   

                       Doesn't this only work if the calculation has a text result? Then you can't "Use thousands separator." You would have to use Left, Middle, Right and maybe Position functions to insert commas where they belong depending on the length of the number.

                   

                  Al Quimby

                  • 6. Re: Right aligned numbers with parentheses for negatives
                    user19752

                    There is a function to insert commas (if you need every 3 digit)

                    NumToJText(num;1;0)

                    • 7. Re: Right aligned numbers with parentheses for negatives
                      siplus

                      You can always modify a custom function from Brian Dunning to do what I suggested, like this one. Actually I posted the idea just because it reflects my attempts to "thinking out of the box", and came up with another one which does not need a CF.

                      • 8. Re: Right aligned numbers with parentheses for negatives
                        Extensitech

                        Very interesting. You learn something every day, and I just met my quota.

                         

                        Never really expected to find this in a function for Japanese characters.

                         

                        Chris Cain

                        Extensitech

                        • 9. Re: Right aligned numbers with parentheses for negatives
                          alquimby

                          Using everyone's input here, this is the only way I could get it to work in the attached:

                           

                          (1) Use Geoffrey Gerhard's custom function on Brian Dunning's website to create a text calc that adds a leading "(" to negative numbers. (If you add a trailing ")" you get the same problem that started this thread.)

                           

                          (2) Create a second text calc that displays ")" if the number field is < 0.

                           

                          (3) Slide field number 2 (aligned right) behind field number 1 on the layout.

                           

                          (4) Use conditional formatting to change fields 1 and 2 to red if number is < 0.

                           

                          Everything looks good in Preview mode.

                           

                          Al Quimby

                          • 10. Re: Right aligned numbers with parentheses for negatives
                            siplus

                            All is OK when it ends OK

                            • 11. Re: Right aligned numbers with parentheses for negatives
                              Stu412

                              To everybody who helped on this, a massive thank you.

                               

                              Have managed to get the parentheses where I need them, which is great as the reports we're putting together require these rather than a minus sign which would have been so much easier.  But hey, if this was easy, everybody would be doing it!