I'm a bit perplexed (please see simple attached new test demo file) ...

Searching grandchild field requires that all key fields in the parent table be indexed as well? I tested this on vs. 9, 10 and 11 and it behaves the same ... if one key field (in this case c_string_PA) in the Parent table is unstored then the search on a grandchild Name field fails - "Could not perform operation. One or more of the relationships are invalid."

How could the search fail but the yellow field display the name properly?

I realize it would be slower to search a grandchild table (and this is probably the first time I've ever wanted to) but my mind insists that it should be working.

I would really like to understand the theory behind the failure. If I store c_string_PA then it works. Thank you all for any input!

GrandChildFind.zip

Not sure if I have an explanation but I do have an observation.

If you set the calc field to have global result; or a stored result; or if you just set it to be a global text field; the find will work.

Edited August 20, 201015 yr by Guest

I knew it would work if I stored it. I appreciate you taking a look!

It still bothers me. I've used globals in similar situations and even to grandchildren (I believe) without issue. And I've used unstored strings in many places. The process working if it is global makes no sense either ... the relationship works or it doesn't.

Strange. Now it'll bother me until I figure out the principle behind it or report it as a bug (it must be logical or it isn't). :wink2:

Here's an attempt:

First, this has nothing to do with the searched table being a grandchild - the same thing will happen if you search a related CHILD field.

Now, what exactly does searching a related field mean? I think the search takes place in the related table itself, and after that an attempt is made to return to the original table - something similar to:

Go to Layout [ Child ]

Perform Find

Go to Related Record [ Parent; Match found set ]

Although the relationship is invalid in the opposite direction, this WILL work with a global field/calculation, because the global value is same for all parent records - and therefore can be ignored as a predicate. But the same assumption cannot be made with an unstored calculation field.

Wow. I've been chewing on this and it makes sense. But now I question many situations where I've used unstored calcs on the parent side and GTRR'd to child and then, IIRC, GTRR'd back to parent. I suppose I've just never needed to do it except with globals but I'm surprised that, after all these years, I haven't.

I've fussed between whether to use global or unstored calcs at times. I had recently decided to go with unstored calcs because of many times you've mentioned it. I'm sure you already took this issue into consideration when deciding but I'll need to review any 'string' calculations and see if they contain round-trip tickets which should be cashed in.

Thank you for the clarity, as always, Michael.

I'm sure you already took this issue into consideration when deciding

Not really, because I never tried to GTRR back to the parent with a relationship based on a global/unstored. You have a rather special case here with two matchfields, and only one of them being problematic. If the global/unstored were the only matchfield, it wouldn't matter which one you used, because the GTRR would be meaningless in any case.

So performing a find through a relationship is meaningless, whether Child or Grandchild; it will not filter according to the relationship.

Now, what exactly does searching a related field mean? I think the search takes place in the related table itself, and after that an attempt is made to return to the original table ...

For example (in demo file) if I search for (yellow field) 'David' (which is grandchild table), the relationship does NOT filter with Type = PA. It still finds parent record CheckID 2 even though StaffID 3 (David) isn't type PA and there are no other PAs assigned to CheckID2. I must again include PA as part of the find in Assignments.

Even placing a Child portal and searching Staff 3 (David), it still finds Check 2 unless I include PA in the search (within the portal). This confirms your belief that searching (even through a relationship) bypasses the relationship itself.

UPDATE: And this isn't simply the 'grandchild' issue ... it acts the same even if using child.

Edited August 23, 201015 yr by Guest
Added update

So performing a find through a relationship is meaningless, whether Child or Grandchild; it will not filter according to the relationship.

I misspoke. It works if both fields can be indexed ... it filters the relationship accordingly. It only won't include the relationship if any key (on the parent side) is global or unstored.

Edited August 23, 201015 yr by Guest

This confirms your belief ...

Just wanted to emphasize it's no more than that: a belief, or a conjecture. It could just as well be an unintended quirk or a bug.