Add Leading Zeros

[Matt Leach]

We just recently imported all data from our old system into filemaker and have found an issue.

Our old system stored the zip code as a number but displayed it as text. So the zip code would show up as 00802, but was stored as 802.

Now that everything is in filemaker, i want to be able to automatically add the leading zeros so i can print mailings out.

Im not quite sure how to accomplish this as the number of zeros to add would be different.

For example:

802 i would have to add 2 zeros to make it 00802

8802 i would have to add 1 zero to make it 08802

Is there any type of calculation to accomplish this?

Thanks

Edited September 8, 2010 by Guest

[Lee Smith]

Right ( "00000" & yourField; 5 )

[Matt Leach]

Thank you very much for your help, does exactly what i needed.

[serene]

I'm trying to do a similar thing, but with dates.

examples:

1/1/1992 should become 01/01/1992

10/2/2010 should become 10/02/2010

I use Filemaker a lot, but know almost nothing about how to program or write scripts, etc. I have begun doing this manually, but there are 4,000 records with 3 date fields each, so if anyone could help me figure out how to do this, I'd be grateful. I will also continue RTFM and see if I can find what I need.

[comment]

Why do you need this?

[serene]

Our annual report to the feds goes into a web form they give us, and if even one leading zero is missing, they kick it back to us and say the report is not formatted correctly.

[comment]

Our annual report to the feds goes into a web form they give us

Please explain how this works.

[serene]

Sure. We export our annual report's fields from our database to an Excel sheet, then upload it using their web upload. Some magic happens on their end, and they kick back whatever errors they've found (including the leading-zero error, but also other things that are less obvious.) Then we go fix the fields, re-export, re-upload, lather, rinse, repeat until they accept it. Sometimes this takes a full workday or more to complete.

[comment]

Try either one of the following:

1. Quit Filemaker. Open System Preferences/Personal/International/Formats and set your short date format to MM/DD/YYYY (with leading zeros). Open your file. If asked, select "System's settings". Go to File/File Options.../Text and set the file to "Always use current system settings". Try exporting and see what you get.

2. Export a calculation field (result is Text) =

SerialIncrement ( "00" ; Month ( YourDatefield ) ) &

SerialIncrement ( "/00" ; Day ( YourDatefield ) ) & "/" &

Year ( YourDatefield )

[serene]

Thank you; I'll try that.

[lhooq]

I'm trying to do a similar thing, but with dates.

examples:

1/1/1992 should become 01/01/1992

10/2/2010 should become 10/02/2010

I use Filemaker a lot, but know almost nothing about how to program or write scripts, etc. I have begun doing this manually, but there are 4,000 records with 3 date fields each, so if anyone could help me figure out how to do this, I'd be grateful. I will also continue RTFM and see if I can find what I need.

 

I know the last post here dates back a bit, but the subject is still valid and good. Lee Smith has the right answer, but you may want to take it up a notch for the dates and do something like this:

 

Right ( "00" & Year ( "01/01/1992" ) ; 2 ) & "/" & Right ( "00" & Month ( "01/01/1992" ) ; 2 ) & "/" & Right ( "00" & Day ( "01/01/1992" ) ; 2 )

 

You will need to store your new date as text or else it will convert to whatever system settings you have in your system.

[Lee Smith]

Your post here and the one about the Data Viewer are redundant to what has already been provide.

 

BTW, the best answer to this question was the one provided by comment.