7 Replies Latest reply on Aug 21, 2009 10:08 AM by RickWhitelaw

# Fixing Number lengths - adding 0's to the start of a number

### Title

Fixing Number lengths - adding 0's to the start of a number

### Post

I need to export some data so all the numbers are the same length (6 digits),

i.e. 8.348 beomes 008.348,

79.648 becomes 079.648,

and 148.432 stays at 148.432

Does Anyone know a way of doing this without scripting? (its 201,596,725 records!)

many Thanks!

• ###### 1. Re: Fixing Number lengths - adding 0's to the start of a number

Create a calculation (result is text) and export the calculation instead.  It might look like:

SerialIncrement ( "000" ; Div ( num ; 1 ) )  & Mod (  num ; 1 )

• ###### 2. Re: Fixing Number lengths - adding 0's to the start of a number

Or:

Right( "0000000000000000000000000000000000000000000000000000000000000000000000000000" & number; digits)

You can obviously use fewer zeroes!

• ###### 3. Re: Fixing Number lengths - adding 0's to the start of a number
thanks guys, how much faster is a calculation compared to a script? is there a known way to do this just with number formating etc? as Doing this calculation will still take quite a while.
• ###### 4. Re: Fixing Number lengths - adding 0's to the start of a number

Yoou cannot do this using Fiemaker's number formatting. A calculation will be faster than a script, I believe. You could also make the calculation unstored - this will take virtually zero time during the implementation, but the export itself will be slower.

There's also another option which requires neither a script nor a calculation:
http://fmforums.com/forum/showtopic.php?tid/207241/

BTW, all of your examples have 3 decimal digits - but what if the number is "123.45"?

• ###### 5. Re: Fixing Number lengths - adding 0's to the start of a number
Having just checked my suggestion {Right( "0000000000000000000000000000000000000000000000000000000000000000000000000000" & number; digits)} I've discovered the decimal counts as a digit. Further calculation is needed if this isn't appropriate.
• ###### 6. Re: Fixing Number lengths - adding 0's to the start of a number

Nah, you could just change it to 7 instead of 6 although the extra zeros was a bit of overkill. :smileywink:

The reason I went with Mod() was because of the decimal issue (which Comment mentioned) and I probably over-protected from possible shortage on decimals.

UPDATE:  But I would still like to correct my calc to:

SerialIncrement ( "000" ; Int ( num )  )  &  Mod ( num ; 1 )

UPDATE: But you really should consider Comment's suggestion here and do a few speeds tests to find out which might work the best for you. :smileyhappy:

• ###### 7. Re: Fixing Number lengths - adding 0's to the start of a number

This works well too:

Create a text field "test" and use as an "input" field. Then define a field "test2" as an unstored calculation with the formula below. This assumes a six digit number plus decimal points is wanted. Of course, use as many or few zeroes as necessary. The advantage is that the formula will evaluate text such as "21.1.1" as well (evaluates as 0021.1.1).

Right( "000000000000000" &  test ; If ( Length ( test )   ≠   Filter ( test ; "0123456789");6+Abs ( Length ( test )- Length ( Filter ( test ; "0123456789") ) );6 ) )