Help with match fields

[Tom007]

Hi

I have two files: FileA with near 4 millions of records and FileB have 140000 records. FileA have fieldA_ID of 11 digit number and fieldB_ID on FileB for matching purpose. The idea is to match FileA, fieldA_ID to FileB, fieldB_ID for getting the values (name, in this case) from FileA.

Example:

If fieldA_ID of FileA MATCH fieldB_ID of FileB then get the value of the field "name" of FileA.

For this I create a relationship to match both field_IDs of both files but don't works.

Could you please upload an example for this procedure?

Thank you

Tom

 

[Matthew F]

Hi Tom. Is there a many-to-many relationship between the two tables? In other words, can a record in File_A link to multiple records in File_B?   If so then you should not link the tables together directly, but instead create an intervening Join table.  The Join table would contain fieldA_ID and fieldB_ID at a minimum.  FileA and FileB would only have fields for their respective IDs.  The relationship looks like this (with the Join table in the middle):

FileA::FieldA_ID.pk  <-->  [ Join::FieldA_ID.fk    Join::FieldB_ID.fk ]  <--> FileB::FieldB_ID.pk

 

In contrast, if you have a One-to-many relationship (e.g. FileA links to multiple FileB records, but not vice versa) then put a fieldA_ID.fk on FileB, but do not put fieldB_ID.fk on FileA.  Link the records like this:   

FileA::FieldA_ID.pk  <--> FileB::FieldA_ID.fk

 

(the .pk and .fk suffixes help you keep track which of the IDs is the primary key for that table and which stores keys for related records).  See Filemaker Help for more info on Many-to-Many relationships. 

 

Hope that makes sense.