2 Replies Latest reply on Sep 13, 2013 3:14 PM by strngr12

    Many to one relationships

    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 = this.mov

       

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

       

      Results of scrip and import of resultant listt:

       

      table_2::name = this.mov

      table_2::ID = 123456

       

      AND

       

      table_2::name = this.mov

      table_2::ID = 78901

       

      Result of relationship and calculation:

       

      table_1::name = this.mov

      table_1::ID = 123456

       

      Desired Result:

       

      table_1::name = this.mov

      table_1::ID = 123456

      78901

       

      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!

       

      Thanks,

       

      Dan