Filtering Portal with Global Fields

[biiiink]

My database has Relationships of:

 

Clients ----< Policies ----< Renewals

 

In a layout on the Clients table, I have a portal that shows all of the related Renewal records

 

I would also like to be able to filter the Portal with 3 different global fields.  The names of the fields I want to be able to filter by are: Year, Carrier Name and Status.

 

The problem (at least I think it is the problem) is that the Carrier and Status fields are stored in the Policies table

 

I attempted to create the Carrier filter.

 

I created g_CarrierFilter in the Clients table.  Created a new table instance of the Policies and Renewals tables named Policies_filter and Renewals_filter with the same relationships as above, except the  Clients --< Policies Relationship also has g_CarrierFilter x Carrier Name

 

I added the global field and created a value list from the Carrier Name field in the Policies table.

 

I switched the Portal and all of it fields over to the new Renewals_filter table instance

 

But what I'm getting is shown below:

 

The Carrier for the top record is correct, but the carrier for the second record should show as "RBC Insurance" and the 3rd should be "Empire Life" and if I select either of those as the filter, all of the records disappear.

 

I've double checked that the Carriers are correct in the renewals record.

[Wim Decorte]

Seems to me that it is the policy that needs renewing, not the client.  So the portal should really be showing policies and through policy the related client.  That would solve the problem too.

[biiiink]

It is the policy that gets renewed

 

One Client can have multiple policies.  Each policy will have multiple renewals.

 

For example: If Client A has 2 Policies, and each policy has renewed 3 times, that would be a total of 6 renewal records that are related to Client A.

 

I need a portal that shows all 6 renewal records on the Clients layout.

[comment]

You have titled this as filtering a portal, but you describe filtering a relationship (or rather two relationships). 

 

If a client has a reasonable amount of policies and renewals, then filtering the portal would indeed be the preferable solution here. You wouldn't need the additional TOs and relationships, and the filtering expression could easily reference fields from both tables (Policies and Renewals).

 

To accomplish the same thing using dedicated relationships, those global filter fields that correspond to fields in the Renewals table (gYear?) would need to be in the Policies table.

[biiiink]

You have titled this as filtering a portal, but you describe filtering a relationship (or rather two relationships). 

 

If a client has a reasonable amount of policies and renewals, then filtering the portal would indeed be the preferable solution here. You wouldn't need the additional TOs and relationships, and the filtering expression could easily reference fields from both tables (Policies and Renewals).

 

To accomplish the same thing using dedicated relationships, those global filter fields that correspond to fields in the Renewals table (gYear?) would need to be in the Policies table.

 

The majority of clients will have only one policy, with one renewal per year that the policy has been in place.  A few clients will have 2 policies.  It would be rare that a client have 3 policies.

 

I originally tried filtering the portal as you described above, but I still ran into the same problem as shown in the screenshot.

 

Upon further testing, In the portal showing records from the renewals table, it seems that any fields that are from the policies table will only show information from the first policy record

[LaRetta]

I see no screenshot?

[biiiink]

I see no screenshot?

The screenshot is in the original post