AnsweredAssumed Answered

Many to one relationships

Question asked by strngr12 on Sep 13, 2013
Latest reply on Sep 13, 2013 by strngr12

Hello Everyone,


I have a table, call it table_1 that has a name field and an ID field. I have another table, call it table_2, also with a name field and an ID field. In the first table, the name field is a calculation field that produces an expected name of a record in another (non-Filemaker) database. I have a relationship between the two tables such that table_1::name = table_2::name. By that relationship I am able to get the ID from table_2 into the ID field in table_1.


I run a shell script using the name field from table_1 which finds the records in the external database and returns a list of file names and their IDs. I then import the records from that list to fill in the names and IDs in table_2. This allows the ID to be calculated into table_1.


The problem I have is that the external database has two records for every one record in table_1. When I import the list into table_2 I get two records for every 1 record in table_1. However, my calculation field in table_1 only picks up the ID of the first imported record. For example:


table_1::name =


Calculation: table_1::ID = table_2::ID (based on above relationship)


Results of scrip and import of resultant listt:


table_2::name =

table_2::ID = 123456




table_2::name =

table_2::ID = 78901


Result of relationship and calculation:


table_1::name =

table_1::ID = 123456


Desired Result:


table_1::name =

table_1::ID = 123456



Does anyone know how I can get two different IDs from two different records in table_2 into a single record in table_1 if both records in table_2 fulfill the relationship of name=name? This is not my database so the only changes I can make are in scripts, calculations, the way table_2 imports records and table relationships between table_1 and table_2. I cannot change the the way records are initially imported into table_1 so I cannot have, say, two records for every name. I must be able to match two records in table_2 to one records in table_1 and get both IDs. I can add a new field in table_1 if I need to, though.


I appreciate any help!