Related Fields not displaying Correct data (With Screenshots)

[GisMo]

This is really bugging me..I have a sales rep, customer, and sales order tables all related in a linear fashion. See image below. I have a layout based on the Sales Rep table with a portal on that layout showing related records from the SalesRep.Customer.Salesorder table.(Showing all sales orders by that sales rep) This is fine. Now, I want to show Customer's Company Name in the portal row, So I add field SalesRep.Customer::CompanyName to the portal. Doesnt work.(Column Company Name 1)

 

NOW, just for fun, I added a second SalesRep.Customer.SalesOrder.Customer(Shown on far right of graph). I pull in that ::CompanyName and it works!(Column Company Name 2)  I'm uncomfortable with this behavior. Any explanations?

 

 

 

 

Thanks in advance.

[LaRetta]

Hi GisMo!

 

You are hitting what is known as the grandchild issue.  FileMaker, from parent, looks past the child to the grandchild and sees the first grandchild of it's first related child.  It does not really look through the child to make that determination.  I can probably find a link where it was discussed in depth.  I had also started to put together a demo explaining it because it does not seem correct to me either but it has a logic to it from FileMaker's POV.

 

I have to leave but I'll try to find more information on it and post here as well - or I'll finish that demo.

[GisMo]

Hi! Thanks! I figured it was something like that. Why does one table work and the other not? Or is that the expected/reliable behavior? Is this the best solution for this(Like the example above?)? Or is it something like creating more foreign keys to flatten out the database? (Also seems messy)

 

I found this related post and seems to use what I have done which is to create another table occurrence to get what record I need: http://forums.filemaker.com/posts/5d251c966a

 

I guess I'm trying to understand WHY this works the way it does.

[LaRetta]

Well, I could not suggest without seeing the file.  Here is the link I was looking for - starring our own Mr. Comment and Mr_Vodka ... two of our best:

 

http://fmforums.com/forum/topic/62292-relationship-sorting-question-table-a-to-table-c-via-table-b/?hl=grandchild#entry294550

 

It is not an easy issue to explain.  I don't have time to search for the demo file I had created  - maybe I can get to that tomorrow. But you will need another relationship as you've done. 

 

But you might consider using ExecuteSQL() also.