Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

retaining leading zeros in exported file

Sean O'Donoghue
in Importing & Exporting

Featured Replies

When I export a number field to a comma-delimited text file, the exported file looses its leading zeros

Ie 00312 exports as 312.

I need to retain all five digits - how can I do this

Thanks in anticipation.

Sean O'Donoghue

quote:
Originally posted by Sean O'Donoghue:

When I export a number field to a comma-delimited text file, the exported file looses its leading zeros

Ie 00312 exports as 312.

I need to retain all five digits - how can I do this

Thanks in anticipation.

Sean O'Donoghue

Try defining an unstored calculated text field, e.g:

Number_Export=Right("00000" & NumToText(Number_Field), 5)

and export Number_Export instead

  • Author

Thanks for your help - however it does not work as I want it to do

- it works fine on records created but leaves the existing numbers unexported

Any more suggestions?

Thanks

S O'D

We'll need a little more description of what you are doing. If you have 1) defined the new calculation field and 2) exported it INSTEAD of the existing number field, it should work. This new calculation field has no effect on which records are exported, that is a function of the found set at the time the export is performed.

I'm not quite sure what you mean by "it works fine on records created but leaves existing numbers unexported". Perhaps you can give some further details.

-bd

  • Author

Greetings

Sorry I was not too clear.

What I did was create some new records, starting from 00001 - the other records in my database are in the 28000s - the new records exported fine but the ones which were in the database previously did not export so I got

the following - which is but a sample.

""

""

""

""

""

""

""

""

""

""

"00001"

"00002"

"00003"

"00004"

"00005"

Regards

Sean O'D

quote:
Originally posted by Sean O'Donoghue:

Greetings

What I did was create some new records, starting from 00001 - the other records in my database are in the 28000s - the new records exported fine but the ones which were in the database previously did not export...

I''m not sure exactly what you're trying to accomplish here, but can't you export those records (starting at 28000) again? Just export the calculated text field instead of the number.

Hope this helps!

What exactly do you mean by "did not export". Were the 28000 series records in the found set when you tried to export? How did you perform the export? Manually? Under script control?

You'll need to answer some of the questions we've asked with some elaboration for us to be of further help. It's hard to watch your screen from here! wink.gif" border="0 .

-bd

  • Author

Greetings

Sorry I was not too clear.

What I did was create some new records, starting from 00001 - the other records in my database are in the 28000s - the new records exported fine but the ones which were in the database previously did not export so I got

the following - which is but a sample.

""

""

""

""

""

""

""

""

""

""

"00001"

"00002"

"00003"

"00004"

"00005"

Regards

Sean O'D

Let's go back to the original problem -- the leading zeros not being exported. Has this been solved with the calculation field?

  • Author

Sorry - I had created the Number_EXport field as a Number Field - have now changed it to a text field.

"Number_Export" field is the calculated text field below, with "book_id" being the field whose properties I wish to export, retaining leading zero

book_id=Right("00000" & NumToText(book_id), 5)

I am using FMP 5.5 - you tell me to define an "unstored calculated text field" - the program Help & the book I am using (FMP Companion 5) both suggest that I should have a tick box by a "Do not store calculation results - calculate only when needed" command - I do not appear to have this, certainly on this tab - on the Storage tab for the Options for Field "Number_Export" box, I only have

a) Repeating field with a maximum...

??? Indexing On & Off tick boxes &

c) Automatically turn indexing on if needed

d) default language for indexing etc

And this is the result of my export of the Number_Export field - with lots of expty

""

""

""

""

""

""

""

""

""

"0"

Yours mystified

Sean O'D

quote:
Originally posted by Sean O'Donoghue:

"Number_Export" field is the calculated text field below, with "book_id" being the field whose properties I wish to export, retaining leading zero

book_id=Right("00000" & NumToText(book_id), 5)

These statements seem to contradict one another. If I'm understanding you correctly, field book_id is the original field with values from 00001 to 99999 (or whatever your upper limit happens to be). However, when you try to export this you lose the leading zeros.

My suggestion was to create a new field, Number_Export. This should be a calculation field with a text result.

Number_Export=Right("00000" & NumToText(book_id), 5)

I believe you weren't able to select "Do not store calculation results" because Number_Export was not defined as a calculation field. I apologize for not being more clear before -- that is what I meant by unstored calculated text field. A calculated field with an unstored text result.

Number_Export is the field you want to export, not book_id.

I hope this is more clear.

  • Author

Greetings Again

Yes, Field_export is the field I am exporting - I have now discovered where the "Do not store calculation results" tick box is & have ticked it.

Now I am getting mostly "1" & "O" in my exported txt file, with a couple of total blanks -

Shouldn't the calculated field, Number_Export, contain the calculated result - as no doubt you will have deduced by now, mine only contains 1s & Os

Also, you say "unstored calculated TEXT field" - I am unable to create a text field when I opt for the calculation field -

>>Number_Export is the field you want to export, not book_id.

<<

Regards

Sean O'D

quote:
Originally posted by Sean O'Donoghue:

Also, you say "unstored calculated TEXT field" - I am unable to create a text field when I opt for the calculation field -

There is a drop-down menu immediately below the area in which you define the calculation. The menu is labeled "Calculation results in". From that menu select Text.

I suspect that the calculation is defined to result in a number, which may explain the 1's, 0's and blank values. Otherise, it is also possible that book_id is defined as a Text field. Double-check this in Define Fields. If book_id is indeed a text field, then I suggest removing the NumToText() part of our calculation.

Hope this helps -- I'll be keeping my fingers crossed for you several thousand km away... smile.gif" border="0

[ November 26, 2001: Message edited by: The Bridge ]

  • Author

Got it & many thanks to all for your help

Much appreciated from a demented Irishman in London

REgards

Sean O'Donoghue

Create an account or sign in to comment

https://fmforums.com/topic/1506-retaining-leading-zeros-in-exported-file/
Go to topic listing

Important Information

By using this site, you agree to our Terms of Use.

I accept

Configure browser push notifications
Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.