Let me begin by saying I am new to filemaker and setting my first simple database.
I have two tables. The data in both these tables was imported from Excel spreadsheets. One table is a MEMBER table with a unique member ID and other member related information including name, title and other information including a City field (Member City). I have a second table called POPULATION which has a number of fields including a CityID, county, population and a City field (All California Cities). I want to relate the City fields in both tables to link the tables and access the information in the POPULATION Table.
I have been able to establish a one to many relationship from the POPULATION table to the MEMBER table using the City fields in both tables. I set the POPULATION City field Options>Validation>Require to Unique Values to make the relationship. One City may occur in many different Member records. When I view the information in the table I have created with the fields from the POPULATION table inserted, only about 5% of the data fields are populated with information for the POPULATION table. It seems as though the relationship has been established but perhaps there is some problem with the values that causes them not to relate to one another. Of the 5% of the relationships that are established between the City names, the actual number of Cities related are about 1% of the total Cities. I can get all the data fields from the POPULATION City table to display for only the few City records that actually seem to be related.
I have attempted to scrub the data both in Excel before import and in Filemaker, but I have not been able to overcome what I believe is some kind of formatting issue in either the MEMBER City Field or the POPULATION City field to complete the relationship. I assume this must be something simple, perhaps having to do with the source of the data, but I have been searching discussions and the internet without solution.
Heres the Relationship Diagram.
Thanks in advance for your assistance.