Excel to FileMaker Pro 11 Import Issues

[saraheagle]

Hi,

I'm a newbie whose been using File Maker Pro 11 for about two or three months. Overall, everything has been going really well, except for this one rather large problem. I would really appreciate anyone’s help on the subject and thank you in advance for your time. Also, I apologize if this post is rather long.

I’ve just started using FMP as a means of organizing a private photography collection and, as all of my work from the past 6 months is in excel, I'm beginning to move it over to the new database. The numerical system the collection uses to differentiate one photograph from another is year.number of pieces bought that year. So, 2010.0001 is the first photograph bought in 2010 or 85.0203 is the 203rd photograph purchased in 1985.

Also, the boxes also have a numerical system in which a four digit number is followed by a .1, .2 or .3 which signifies the size of the box

The problem that I'm running into is that FileMaker starts tacking on all of these random decimals to the end of my acquisition and box numbers, usually something like 2010.0010999999999 instead of 2010.0001 or 85.020289999999999999 instead of 85.0203. It’s a similar issue with the boxes.

I’ve tried changing the field setting so that it’s a number field with only four decimals but while it changes it cosmetically, rounding to the nearest 4th digit, once I click on the field it returns to the strange numbers. Also, I realized that I just can’t find/ replace 899999999 with nothing because the number becomes 85.0202 instead of 85.0203.

I was wondering if anyone has had an issue like this before? Re-importing the data is not a big deal if there’s a way that I can do it without getting the extra digits. Again, I apologize for the length of this post and I would really appreciate any advice anyone can give.

Thank you so much.

[TheTominator]

Change the FileMaker field to be a Text field.

[saraheagle]

Hi,

Thanks so much for responding. I actually tried that first, and it gave me the same response. That's when I tried changing it to a number with only 4 decimals places. Which only changed it cosmetically.

[Raybaudi]

What about changing the Excel value from number to text before importing ?

[TheTominator]

I’ve tried changing the field setting so that it’s a number field with only four decimals but while it changes it cosmetically, rounding to the nearest 4th digit, once I click on the field it returns to the strange numbers. Also, I realized that I just can’t find/ replace 899999999 with nothing because the number becomes 85.0202 instead of 85.0203.

An alternative to those described above is to do a Records-->Replace Field Contents... on the field with a calculated result.

Use the Round() function (and other functions to add trailing zeros if desired) to change the data in the way you want.

For the most consistent results you should use a FileMaker text field so that leading and/or trailing zeroes will be preserved. You aren't actually entering a number. You are entering a sequence of text that just happens to have characters 0-9 and a "." in it.

[saraheagle]

Fantastic, that seems to have been the key!

I followed your advice and applied the rounding function to the Acquisition number field and it worked perfectly. The Acquisition field is currently a text field with the calculation added in, which is where I'll leave it.

Your time and advice is greatly appreciated.