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?
Instead of Lookup, use calculated value. This is much easier to control and understand
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.
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?
1 of 1 people found this helpful
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.
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!
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.