4 Replies Latest reply on May 6, 2009 8:42 PM by barfaz

    "lookup" fields that relate to multiple other fields?

    barfaz

      Title

      "lookup" fields that relate to multiple other fields?

      Post

      I would like to know how to create a field that will lookup data in several other fields, based on complex relationships.

       

      For example, table 2 is linked to table 1 via a relationship between Fields A and B in both tables, whereas table 3 is linked to table 1 via a relationship between Fields A, B and C in both tables. I want the data in Field M in table 1 to be the same as that already entered in Field M in table 2 if the record in table 1 matches that in table 2 for Fields A and B. If there is no match, then I want field M in table 1 to be the same as that already entered in Field M in table 3 if the record in table 1 matches that in table 2 for Fields A, B and C. If there are no matches, then I want Field M to remain blank.

       

      First, can I do this with a field definition or do I need to write a script to enter data into field M in table 1?

      Second, if I can do this with a field definition, can I do it as a realtime lookup so that the data in field M in table 1 reflects changes in field M in tables 2 or 3?

       

       

        • 1. Re: "lookup" fields that relate to multiple other fields?
          comment_1
            

          Whew. You're talking to people here, not machines - have mercy and use less abstract terms. For example:

          I have a TargetField in the MainTable. I want it to lookup the value from the Values table, but if there is no match, it should lookup from the AltValues table.

          This can be done by nesting the Lookup() function (the field should be set to auto-enter calculated value, replacing existing data).

           


          barfaz wrote:

          can I do it as a realtime lookup so that the data in field M in table 1 reflects changes in field M in tables 2 or 3?


          No. The target of a lookup does not change when the source value changes. That's the entire point of a lookup. You can, however, have a calculation field along the lines of:

           

          Case ( not IsEmpty ( Values::MatchField ) ; Values::SourceValue ; AltValues::SourceValue )



          • 2. Re: "lookup" fields that relate to multiple other fields?
            barfaz
              

            Thanks for that. I have used other relational databases but not filemaker so I find the syntax difficult.

             

            What is the Matchfield in the calculation you sent me - as I have 2 fields that have to match for the relationship between MainTable and Values?

             

            When I use just the first of these 2 fields as the "MatchField" in the calculation, the data from "Values" appears correctly in TargetField, but not that from "AltValues". Also, if I change the SourceValue data in Values, it does not change the TargetField value. Does Filemaker have an option where there is a real-time calculation in a field each time it is accessed, not just when a new record is entered?

            • 3. Re: "lookup" fields that relate to multiple other fields?
              comment_1
                

              The MatchField is the field used in the definition of the relationship. If you have several matchfields, you can use any one of them for this calculation. Actually, you could use ANY field from the related table, as long as it cannot be empty - for example, the primary key of the related table.

               


              barfaz wrote:

              if I change the SourceValue data in Values, it does not change the TargetField value.


              Make sure the 'local' field is a Calculation field - NOT a field with auto-entered calculated value. Note also that after changing the related value you must commit the related record, and the screen needs to be refreshed for the change to become visible in the calculation field.

               


              • 4. Re: "lookup" fields that relate to multiple other fields?
                barfaz
                   Thanks - it works!!!