Relationship across 2 TOC's

[Greg58]

I have an experimental file I am looking at.  It has a company table, a contacts table, and a join table.  One company can have may contacts, and one contact can have many companies.  I have inserted 2 portals into the contacts table.  One based on the join table, and one based on the company table.  The portal based on join works fine.  The one based on the company table only shows the one record repeating (Record 2 in this case, see below).

As I say this is just experimenting.  My question is this.  Is it only showing record 2 in the portal based on 'Company' because going through the join TOC to the company TOC, you will only ever see the first record??

Thanks.

 

 

[comment]

54 minutes ago, Greg58 said:

The one based on the company table only shows the one record repeating

I see only one field with repeating value - and you're not telling us which field it is. Would it be correct to assume it is a field from the Join table?

 

Edited August 28, 2020 by comment

[Greg58]

Sorry for that.  It is the field with the number 2 in it on the right.  The field is join::company_ID_FK.

[comment]

23 hours ago, Greg58 said:

The field is join::company_ID_FK.

That is the expected behavior. It is kind of difficult to describe but a relationship looks only forward, never back. You will get a different result if you place another TO of the join table "behind" the portal TO:

Contacts --< Join >-- Company --< Join 2

and place fields from Join 2 inside the portal. These fields will show data from the first related record in Join 2, starting from the Company in the corresponding portal row. While fields from Join will always show data from the first related record in Join, starting from the current record in Contacts - regardless of the portal row.

Of course, the practical solution here would be to use the company_ID field from the Companies table. 

 

Edited August 30, 2020 by comment

[Greg58]

OK.  That helps me understand this.  Thanks.