1 2 Previous Next 17 Replies Latest reply on Oct 30, 2015 3:19 AM by beverly

# Is there a function to pad a text with ceros?

I have a number field and need to represent it in a textual manner so it sorts correctly.

I haven't found a function that allows me to pad a number into a text containing leading ceros.

Is there on in FM14?

Juan

• ###### 1. Re: Is there a function to pad a text with ceros?

Hola, Juan.

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.

• ###### 2. Re: Is there a function to pad a text with ceros?

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:

00 4320.00

Also, how do I deal with negative numbers? I need to sort based on the text version of a number field.

Thanks,

Juan

• ###### 3. Re: Is there a function to pad a text with ceros?

A number will sort correctly when it's a number, otherwise 110 will come before 21.

• ###### 4. Re: Is there a function to pad a text with ceros?

To expand on Mike's formula, try

Let (

prefix = If (number < 0; "-"; "+");

prefix & Right("0000000000" & abs(number); 10)

)

• ###### 5. Re: Is there a function to pad a text with ceros?

Works, except that when certain numbers have decimal values and others don't, it doesn't work.

• ###### 6. Re: Is there a function to pad a text with ceros?

multiply each number by 100 to remove the decimals. Then apply the leading zeros.

• ###### 7. Re: Is there a function to pad a text with ceros?

This all makes sense! And yet, I am getting the following fields ordered incorrectly:

-0000018040

+0000200000

-0007400000

+0022903510

-0659000000

This is the result of a calculation field :

Let([

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?

Thanks!

Juan

• ###### 8. Re: Is there a function to pad a text with ceros?

Try this.

If(Line_dollars<0;"0";"") & (1000000000 + (Line_dollars * 100))

• ###### 9. Re: Is there a function to pad a text with ceros?

When sorting text fields, symbols like - and + do not have any hierarchical value to sort.  These symbols are irrelevant to the sort.

• ###### 10. Re: Is there a function to pad a text with ceros?

rgordon wrote:

When sorting text fields, symbols like - and + do not have any hierarchical value to sort.  These symbols are irrelevant to the sort.

• ###### 11. Re: Is there a function to pad a text with ceros?

Sorted list where num is a text field.  + or - has no effect on the sort.

• ###### 12. Re: Is there a function to pad a text with ceros?

juandent wrote:

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.

Hi Juan,

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:

6.0037

-.04

22

3.99999

?

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.

• ###### 13. Re: Is there a function to pad a text with ceros?

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?

Thanks,

Juan

• ###### 14. Re: Is there a function to pad a text with ceros?

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.

1 2 Previous Next