I understand how to filter a portal with a global variable if the layout and portal are both drawing from the same table. But I have a layout that shows records from 1 table and include a portal that displays related records from another table. I want to filter this related portal.

For example, table 1 is called Company, with a field (that I want to filter on) called Status. Table 2 is called Investors. A Company can have multiple Investors, and Investors can invest in more than one Company. I have a 3rd table called Shareholders that links the many to many relationship between Companies and Investors.

I have a layout that draws records from Investors table. That layout has a portal to Shareholders that displays Company fields for all Companies the Investor is a Shareholder of. I want to limit the Companies that are displayed in that portal to ones that match a particular Status (a field in Company).

Example:

Company records: A, B, C

Investor records: X, Y, Z

Shareholder records:

A, X

A, Y

B, X

B, Z

C, X

C, Z

which says X is an investor in A, B, and C; Y is an investor in A; and Z is an investor in B and C.

When looking at the investor layout for X the portal shows Company A, B, and C (3 rows).

Likewise, investor Y shows Company A; and investor Z shows Company B and C.

But Companies have a Status field. And on the investor layout I want to say "only show companies where Investor = X and Status = statusCodeOne.

Is my setup all wrong, or is there an easy way to filter Companies on the Investor layout based on Company->Status?

Thanks!

If you want to show companies, why not make the portal show records from the Companies table? Then your global filter can be in the Shareholders table.

The Shareholder table doesn't have the Company->Status info. Shareholders is just a join table with CompanyID and InvestorID to map the many to many relationship.

If I put a global statusFilter field in Shareholders and then set its value to the company status I care about then how does it make the connection to only show companies with matching status?

If you have the following two relationships:

1.

Investors::InvestorID = Shareholders::InvestorID

2.

Shareholders::CompanyID = Companies 2::CompanyID

AND

Shareholders::gStatus = Companies 2::Status

and place a portal to Companies 2 on a layout of Investors, it will show companies of the selected status that the current investor has shares in.

---

Note that this uses another occurrence of the Companies table for the filtering, leaving the core relationships intact.

Edited October 1, 200916 yr by Guest

That is awesome. Thank you so much!! Very clear and it works as I want. You rock.

Followup question: Now I want a report to be generated for a particular company status, showing all companies of that status, grouped by Investor. For example

Investor X

Company A

Company B

Investor Y

Company A

Where all of the Companies shown have Company->status = Shareholder->statusFilter.

I don't quite understand which Parts I need in the layout for this report, or what fields to put on each part. Would this layout be a layout of Investors or Companies?

Thanks in advance. Really appreciate the help!

Would this layout be a layout of Investors or Companies?

Neither. Such report must be produced from the joining Shareholders table. The required layout parts are a leading sub-summary (when sorted by InvestorID) and body. Place the investor's name field/s from the Investors table in the sub-summary part, and the company's name field from Companies in the body.

To generate the report, go to the report layout, perform a find (searching the related Companies::Status field), and sort the records by InvestorID.

----

Note: if you prefer, you can use a related name field from Investors as the breakfield - in BOTH the sub-summary part definition and the sort order.

That worked. Thank you so much!