Import to Repeating Fields

[ancona0721]

I am trying to import records from an Excel SpreadSheet into our FileMaker database system. I inherited the system and am merely trying to enhance some of the functionality.

One of the enhancements is to automate as much as possible, so for example, if a person enters an area code, it automatically identifies the state, which identifies the country. A similar method is needed with the cities and states.

Well this is the problem. I have a "State" file. In the state file, there is the 4 fields, state abbreviation, state full name, area code, and cities. Both area code and cities are repeating fields.

I have a list of all the cities of Ohio in an Excel sheet. If I import with matching fields, the FileMaker record only displays the last city from the Excel sheet and does not fill up the repeating field, which is repeating up to 500 times. Any solutions?

[ancona0721]

Is importing to a repeating field not possible or something?

[bcooney]

Well, the answer you probably not interested in hearing is that you shouldn't be using repeating fields.

You should have a State table with a primary StateID and a City table. The City table should have the foreign key for StateID in it. Likewise, you need a ZipCode table, and that should have a CityID in it. I'm not sure about Area Codes? Are they children of Cities or should they be just children of States, like Cities?

So, you'd import your Ohio Cities into the City table, and use Replace to set the StateID to Ohio's.

[comment]

Is importing to a repeating field not possible or something?

It is possible, but not from Excel. If you export from Excel to a comma/tab separated format, then change the repeating values separator to the group separator character (ASCII #29), you can then import the result into repeating fields. Hardly worth the trouble, esp. since you will constantly run into problems with data in repeating fields. If you're making improvements, take the opportunity to go relational, as explained in Barbara's post.