Typically, you would do this with a function like this:
Right ( "0000000000" & yourNumber ; 10 )
where you use a number of "0" characters and a number (in this case, 10) equal to the maximum length you're expecting in the string.
I tried this in a number field which is formatted as currency (it appears like ¢4,320.00) yet what I get from your sample Right function is:
Also, how do I deal with negative numbers? I need to sort based on the text version of a number field.
A number will sort correctly when it's a number, otherwise 110 will come before 21.
To expand on Mike's formula, try
prefix = If (number < 0; "-"; "+");
prefix & Right("0000000000" & abs(number); 10)
Works, except that when certain numbers have decimal values and others don't, it doesn't work.
multiply each number by 100 to remove the decimals. Then apply the leading zeros.
This all makes sense! And yet, I am getting the following fields ordered incorrectly:
This is the result of a calculation field :
N = LINE_DOLLARS;
$n = N * 100;
$prefix = If ( N < 0; "-"; "+")];
$prefix & Right( "00000000000000" & Abs( $n); 10)
LINE_DOLLARS is a number field in that same table..
How can I test the ordering?
If(Line_dollars<0;"0";"") & (1000000000 + (Line_dollars * 100))
When sorting text fields, symbols like - and + do not have any hierarchical value to sort. These symbols are irrelevant to the sort.
I have a number field and need to represent it in a textual manner so it sorts correctly. I need to sort based on the text version of a number field.
You haven't indicated what you plan to do with this result. Is it for display (if so, how)? Print? Are you looking for fixed-width results for export? If so, how many decimal places out will you need? And if so, do you wish to truncate or round if any decimals extend further than this 'decimal limit' you impose? If you have the following numeric records, how should the results end up:
If this is all within FileMaker, format a text calculation to pad as indicated Mike Mitchell but just sort by your number field.
We simply don't have enough information to properly pin down your request.
Ok, first of all: THANKS for all your help! IT has been very useful.
To clear things up, what I am trying to do is to sort a column of a portal when the user clicks its header. It's that simple.
However, there might be something under the covers happening. The main table, call it A, has a relationship to a child table B which has a relation to a child table C. In the layout I have detailed fields of A, but then a portal for C. Is this allowed? Will I see all rows on C that are related to each row in B which is related to the current record in A?
here is a possible padding and an idea for sorting (padding or not).
Remember anyway that the field's language influences the sort; sometimes setting it to Unicode changes things.
NumastextAndsort.fmp12.zip 65.8 K