2 Replies Latest reply on Oct 26, 2015 12:19 PM by dtcgnet

    One to Many Related Tables - Do not show all values

    soreese

      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.

       

      Relatioship Table.gif

      Thanks in advance for your assistance.

       

      Scott

        • 1. Re: One to Many Related Tables - Do not show all values
          Mike_Mitchell

          Hello, Scott.

           

          Data scrubbing ... ah, the joy.  

           

          I would suggest that the failure to match the fields likely comes from some errant control characters that were present in the source data (either on the parent or child side) in the original Excel spreadsheets. You might try creating a new, parallel set of fields (just temporarily, so you don't mess anything up) and use the Filter function to strip out anything that doesn't conform to strict alphanumeric characters. Something like this:

           

          Filter ( __fkMemberCity ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789" )

           

          Just use Replace Field Contents to populate the new fields, and see if the join will work then.

           

          HTH


          Mike

          • 2. Re: One to Many Related Tables - Do not show all values
            dtcgnet

            Can you upload a copy of the database? In order to attach a Zip file, you have to click "Use advanced editor" in the upper right of a reply window. If it's not related to a need to scrub the data, someone will be able to see what's causing the problem.