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.

 

 

And do you have this relation in your Relationship Board

KPI::KPI Date = Client::g_Selected_Date?

 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.