Viewing related Data more than one table away

[Roy Osborn]

When using a related field on a layout (without a portal) FMP shows data from the first related record right? This still seems to be true, but if you use a field that is more than one relationship away, (again not in a portal) FMP seems to be inconsistent with what record will be displayed. Has anyone else noticed this problem and understands it?

[Roy Osborn]

Maybe my logic is wrong...but I would think by putting a field on layout that is two tables away (without a portal ) the field would key through the first related record in the union table. Is that incorrect?

[Roy Osborn]

Here's a pdf of what I am talking about. If I put a field from Cards on to the projects layout, I get data from Cards, but which record seems to be determined by luck.

Cards.pdf

[-Queue-]

It seems to be based on the first related record from the grandchild file, according to the child-to-grandchild relationship's sort order and without regard to the parent-to-child relationship's sort order.

[Roy Osborn]

But the Child to Grandchild must go through the Parent to Child relationship...No? There is not direct- Parent to Grandchild relationship. It goes Parent, Child, GrandChild. And I've played with varying sort orders, it does not seem to make any difference. This one has me stumped!

[Roy Osborn]

Does this mean that without a portal, you really can't view grand child records?

[-Queue-]

Correct, it is based on the related child record, but apparently not concerned with the parent-to-child sort order. So which child record it uses is based on the sort order from the child-to-grandchild relationship, if there is one.

[Roy Osborn]

OK. You said "it is based on the related Child record", but I am not getting that result. In my example there is a one to many relationship bewtween projects and contacts (a project has many contacts) and there is a one to one relation between Contacts and Cards ( a contact can only be one person). So when add contacts to my project, say three or 4 in a portal--to me, its logical to assume the first contact added to the list would be "the related Child Record" if you put a field from Cards directly on the project layout without a portal. --assuming no Sort orders are defined at all?

[-Queue-]

Notice I said that the related child record it uses is based ONLY on the child-to-grandchild relationship and sort order. So if Project A has 10 Contacts, the related Card information that would be displayed in Projects would be the first related one in the Contacts-to-Card relationship, based on its sort order, and the sort order or order of creation (if there were no sort order) of the Projects-to-Contacts relationship would be inconsequential. If there were no sort order for Contacts-to-Cards, then it would be based on which related record was created first in Cards, not which related record was created first in Contacts.

At least, that's the way it seems to be behaving. And that does follow from the way it's always worked. It's only different that the parent relationship's sort order is not included, which I would venture to say is quite a bug.

[Roy Osborn]

I think we're on the same page, the fact that the parent to child relationship seems to be inconsequential when displaying a grandchild record-outside a portal-is what surprises me. I guess the "first record" logic only applies at one level?

[comment]

That is NOT what I am getting: if I place a field from the grandchild table on the parent layout, I see the first related grandchild of the first related child.

[Roy Osborn]

It's a frustrating one because all I want to do is have a list of contacts related to a project, and have the first contact entered be assumed as the "Client" which is displayed like a rolodex card, instead of being a part of the contacts list. I figured if I put the fields from Cards on my project layout, then put the contact list to show related records 2-10, I'd be golden. But... no dice.

[Roy Osborn]

I get that result too, sometimes, but not consistently. I get the expected result 100% in a portal, but not if the grandchild field is directly on a layout.

[Roy Osborn]

It would seem -Queue- explanation is accurate - You get the expected result only if the creation orders line up from Parent to Grandchild.

[Roy Osborn]

Does this mean that calculations that consider grandchild records will act in a simialr fashion? Because if it does, I think I may be hurting.

[comment]

See file.

childGrandchild.fp7.zip

[Roy Osborn]

I see that works, but there's one difference- you have a one to many relationship from child to grandchild. Look at this file---There are 3 or 4 records in the projects file, each has several contacts, notice which one is displayed below the contacts list, it's always the card that was created first, disregarding the the parent to child relationship.

[Roy Osborn]

attachment seemd to not work

Cards.zip

[-Queue-]

And if you change your grandchild relationship to have no sort, the second parent record's non-portaled grandchild is 1002, when you would expect it to be 1009 or 1018, its first child's related children. The same goes for parent 3.

If you keep the granchildren's sort and remove the parent's sort, parents 2 and 3's first grandchildren are again not related to their first children.

In both cases, the first granchild record is seemingly independent of the parent-to-child relationship's sort order. It is either the largest related id (for the descending grandchild sort) or the smallest (for the default creation order sort), which appears to confirm what we were saying.

So I guess the rule of thumb here would be not to put a single grandchild field on a layout if you have multiple child records in the intervening relationship or you may not achieve the expected result.

[Roy Osborn]

What about calculations?

[comment]

Ok, I see. I tried also sorting both relationships in opposite order, and as you say - the first granchild record is seemingly independent of the parent-to-child relationship's sort order.

Perhaps there is an advantage to this somewhere.

[Roy Osborn]

I just added a calc field to Projects, and calculations seem to evaulate in the same way--so it's no just a layout thing. This means that you can't evaulate grandchildren records if t you have a one to many relationship in the middle. I guess I need to do some rethinking on my projects...not sure where else I may have made that assumption. I've even tried putting another self-join table in the middle--contact id = contact id, and from that table drawing a relationship to the cards and I get the same result.

[-Queue-]

In such cases you are probably better off creating a calculation in the child file to reference the grandchild. Tunnelling like this was used to reference grandchildren in previous versions, but 7 was supposed to eliminate the need for it. I think this is a bug that should be reported and fixed ASAP. It kills some of the inherent flexibility that makes 7 a great product.

[Fenton]

Looking at Roy's file. Yes, it ignores the parent-child relationship, and uses the grandparent-grandchild relationship. Which I would expect. Otherwise it would be pretty hard to deal with sorting. The last relationship, which is to the table occurrence you actually specified, is used.

There could be many relationships in between the specified TO and the TO of the originating layout, each one with a different sort.

Unlike when sorting records, you can only choose a field in the specified TO for sorting a portal or relationship. So it is apparently not possible; not implemented in any case.