Filtered ported w/records from another table

[scanlin]

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!

[comment]

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.

[scanlin]

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?

[comment]

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, 2009 by Guest

[scanlin]

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!

[comment]

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.

[scanlin]

That worked. Thank you so much!