7 Replies Latest reply on Oct 7, 2016 8:59 AM by philmodjunk

    Lookup fails when relationship is two hops away

    deanchampeau

      Hello,

       

      I have a table (Estimates) that is related one-to-many to a second table (Estimate Requests), which is related many-to-one to a third table (Customers). When I enter a value into my match field in Estimates, it populates my lookup fields as I would expect, but ONLY those fields where the lookups point to the child table (Estimate Requests). Fields where lookups are defined as coming from the grandchild table (Customers) do not populate. Is this a limitation with FileMaker, or am I not doing something properly?

       

      Thanks,

       

      Dean

        • 1. Re: Lookup fails when relationship is two hops away
          philmodjunk

          at the times that this happens, are you sure that there's a matching record in Estimate Requests that also matches to a customer record?

           

          Might it be that the "bridge" record in Estimate Requests has just been created? And possibly not yet committed?

          • 2. Re: Lookup fails when relationship is two hops away
            Johan Hedman

            Instead of Lookup, use calculated value. This is much easier to control and understand

            • 3. Re: Lookup fails when relationship is two hops away
              philmodjunk

              There are trade offs between the two options and both can have issues when the source table is more than "one hop" away. One disadvantage (very minor) is that you can't use relookup to cause an update of the looked up information.

               

              What usually breaks down here is that FileMaker tries to get the data from Table 2, before the data needed to get a value from Table 1 is accessible. Sometimes, a commit records will solve this and sometimes you need a script trigger to update the value after the intermediary record (table 1) has been created and committed.

              • 4. Re: Lookup fails when relationship is two hops away
                deanchampeau

                Hi Phil,

                 

                In this instance, the data always exists in the downstream tables and records are fully committed, so making sure to commit a record in the Estimate Requests table won't help.

                 

                Bottom line is that it appears that there are issues with lookups involving tables that are more than one generation away from the parent. I might have to resort to scripting. Does that sound like the proper approach to you?

                • 5. Re: Lookup fails when relationship is two hops away
                  philmodjunk

                  I think that you need to describe how you are trying to use this Lookup in more detail first.

                   

                  It may be that the lookup is functioning exactly as you have designed it to work, just not as you are expecting it to work.

                   

                  When I try to replicate what you have described, I get the results that I would expect--I get the value from the 2nd table via the "first related record" in the 1st table that links your layout's table to that 2nd table record.

                  1 of 1 people found this helpful
                  • 6. Re: Lookup fails when relationship is two hops away
                    deanchampeau

                    Phil,

                     

                    This solution is big with lots of TOs and other clutter (I started this solution before I understood the value of the anchor-bouy approach with its naming conventions and relationship-graph methodology). Anyway, so I built a separate little solution probably like you did, with nothing else to clutter it up. And I got the same result as you... it worked just fine, pulling data from the "grandchild" table just as easily as from the "child" table. So I looked more closely at my ACTUAL solution where I was having the problem, and sure enough, my lookup was targeting an incorrect table occurrence. So I fixed that reference and now it works great!

                     

                    Two things I've learned... 1) Always use anchor-bouy and careful naming conventions even if I think the solution is never going to expand and grow beyond its modest beginnings, and 2) Always carefully review my work before I start bugging people on the discussion forum!

                     

                    Thanks for your help!

                    Dean

                    • 7. Re: Lookup fails when relationship is two hops away
                      philmodjunk

                      Sometimes being told, "it worked when I tried it" is exactly the help we need from the forum in order to go back and take a closer look at our work.