1 Reply Latest reply on Sep 3, 2013 10:34 AM by philmodjunk

    newbie and confused



      newbie and confused


           I have 2 tables with corelating data.  the first table is the primary table and includes dozens of fields.  I want filemaker to compare field 4 from table A to produce a value automatically that will change as the values from fields 1, 2, and 3 change and it will reference table B to find the appropriate value.


           Field 4 =

           table A:: field 1 = table B::field 1 and 

           table A:: field 2 = table B::field 2 and 

           table a::field 3 >= table b::Field 3 

           and then find the record on table b where the above conditions are all true and place the value of table B:: Field 4 in table a:: field 4..  How do i do that and also if table A is the primary table, how many keys need to be linked to table b to make this work?

        • 1. Re: newbie and confused

               You last question is in fact the "key" to making this work. You may not, in fact, need a Field 4 at all in Table A. The relationship would look identical to what you have posted:

               table A:: field 1 = table B::field 1 and
               table A:: field 2 = table B::field 2 and
               table A::field 3 > table B::Field 3

               In Manage Database, you can drag from field 1 in table A to field 1 in table B, then do the same to link Fields 2 and 3. Then double click the relationship line ot open a dialog where you can change the = operator to > for the Field 3 pair of match fields.

               Then, with that relationship in place, you can use the field tool to add Table B::Field 4 directly to your layout and it will display data from Table B whenever the values in FIelds 1, 2 and 3 match as specified in the relationship.