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.
- 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).
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)
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.
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 ).
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.
There is a function to insert commas (if you need every 3 digit)
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.
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.
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.
align_test.fmp12.zip 69.6 K
All is OK when it ends OK
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!