3 Replies Latest reply on Feb 12, 2015 10:41 PM by philmodjunk

    Get value from (indirectly) related table

    BenOtto

      Title

      Get value from (indirectly) related table

      Post

      Dear community,

      I still encounter a problem that I previously tried to describe in a previous post.

      http://forums.filemaker.com/posts/2ada6ac15e?commentId=317493#317493

      I seem to have made the description way to complicated so I'd like to put it in a new post in a very simple way:

       

      Given three tables (Main, table 1 and table 2) with the following fields and relations:

      Main_Table: UID_main, main_info, copied_info (calculated textfield)

      Table_1: UID_table1, relation_mainTable, relation_Table2 (ALL three fields are required to be unique!)

      Table_2: UID_table2, further_info

       

      The relations are as follows:

      Main_Table is linked to Table_1 via (UID_main = relation_mainTable) and 

      Table_2 is linked to Table_1 via (UID_table2 = relation_relationTable2)

      => In other words: Main_Table and Table_2 are not directly linked but indirectly via Table_1

      Note: via definition of fields in table_2 there will always be only one single unique match between Main_Table and Table_2.

       

      My Problem:

      How do I copy the value from Table_2::further_info to Main_Table::copied_info via some calculation (not via script but via field-internal calculation)?

      I would like "Main_Table::copied_info" to be a text field with a calculated vaule. But that doesn't seem to work, whatever I do. I tried the following calculations:

      Table_2::further_info

      GetField (Table_2::further_info; "") 

      Lookup ( Table_2::further_info ) 

       

      Any suggestions what I might be doing wrong and how I can copy the value?

        • 1. Re: Get value from (indirectly) related table
          philmodjunk

          Main_Table::UID_main = Table_1::relation_mainTable
          Table_2::UID_table2 = Table_1::relation_relationTable2

          Table_1: UID_table1, relation_mainTable, relation_Table2 (ALL three fields are required to be unique!)

          Hmm that seems a bit odd, why are the fields in table_1 required to be unique? That seems to defeat any purpose for having a table_1 in the first place. Specifying that UID_Main and UID_table1 be unique in there respective tables, on the other hand, makes sense. Are you sure that isn't what you meant here?

          With the relationships as described, a record in Main_Table matches to one and only one record in table 1 and a record in table 2 matches to one and only  record in table 1--producing a 1 to 1 relationship across all three tables. That should not give you any trouble accessing the data from the context of any one of these tables, but I doubt that this is what you really have as it doesn't really make sense as a database design.

          But if it is what you have, you do not need any special functions at all. You can simply add the field directly to your calculation. Example:

          In MainTable, you might define a calculation field as:

          FieldA + Table1::FieldB

          And your calculation will sum the value of FieldA (a field in MainTable) with the value of FieldB--a field defined in Table1. Referring to fields from Table2 would be just as simple.

          I suspect that you have this setup:

          MainTable----<Table 1>------Table 2

          Where one record in MainTable can match to many records in Table 1 and one record in Table 2 can match to many records in table 1. This would make table 1 a "join" table for a classic many to many relationship between MainTable and Table 2.

          If that's what you have, you have a problem due to the nature of your relationship. A calculation in MainTable that refers to a field in Table 2 refers to the "first" of possibly many records in table 2, all related to the same record in MainTable via the many to many relationship. And that "first" record might be the correct record.

          • 2. Re: Get value from (indirectly) related table
            BenOtto

            Dear Phil,

            With the relationships as described, a record in Main_Table matches to one and only one record in table 1 and a record in table 2 matches to one and only  record in table 1--producing a 1 to 1 relationship across all three tables.

            This IS exactly what I want. 

            Concerning calculation: If I actually use a real "calculation" ("formula" ?) field I can access the value without problem. What I actually need, is a "text" (!) field, where the value of my text field is set to a calculation within the field settings.

            Sorry If I might confuse some of the official field or setting names, I always have tom translate them from the german names to the english analogues.

            So, once I try to set the (text) field value to some (calculated) value, nothing works. This is not the case when I have a direct relation between two tables. With a direct relation I can use the functions mentioned above and they will work. Once I try to do the same for my "indirect" relation construct ... they break.

             

             

            • 3. Re: Get value from (indirectly) related table
              philmodjunk

              WHy do you need an auto-enter calculation? Why do you need a calculation at all?

              With the relationships you describe, you can simply add the field from the related table directly to your layout.