Parent/Child/Grandchild Portal and filtering the grandchildren help needed

[brianlees]

Database setup (relevant fields)

 

Client  (parent)

- client ID

- g_Selected Date (global field to pass date to another layout)

 

Department (child; many depts linked to one client)

- dept ID

- client ID (linked to client table)

 

KPI  (grandchild; many KPIs to one department, one for each date)

- dept ID

- KPI date

- KPI 1

- KPI 2

 

I setup a layout (based on client table) with a portal (based on department table).  The fields in the portal are the four listed in the KPI table.

 

The Problem:  How do I limit the portal to only show KPIs with a date based on a variable passed to the layout (actually, the global field in the Client table)?  I've tried to use the Filter option in the portal using the following calculation:

 

KPI::KPI Date = Client::g_Selected_Date

 

The filter will not display anything.  If I turn the filter off, I see all the KPIs (all days) for all the departments under that client ID.

 

Not sure how to limit the portal.

 

 

[Pavlk]

And do you have this relation in your Relationship Board

KPI::KPI Date = Client::g_Selected_Date?

[comment]

 I've tried to use the Filter option in the portal using the following calculation:

 

KPI::KPI Date = Client::g_Selected_Date

 

IMHO, this should work, so check your setup again. Are you sure g_Selected Date is indeed a global field, and that both fields are Date fields?

 

 

The other option is to move the g_Selected Date field into the Department table, and define a relationship to another occurrence of the grandchild table as:

 

Department::dept ID = KPI 2::dept ID

AND

Department::g_Selected Date = KPI 2::KPI date

 

Then set the portal (and the fields in it) to show records from KPI 2.