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.
Reminder - the portal is based on the Department table, not the KPI table. I think that is the problem. The filter I want to do is to limit entries from the KPI table, but the portal is based on the Department table.
Example Entries in KPI table:
Record 1
DeptID: 1
KPI Date: 12/20
KPI1: 5
Record 2
DeptID: 2
KPI Date: 12/20
KPI1: 9
Record 3
DeptID: 1
KPI Date: 12/22
KPI1: 7
Record 4
DeptID: 2
KPI Date: 12/22
KPI1: 5
So, lets say that I have the date set to 12/22. I only want to see records 3 and 4. Without the filter, only the first two records appear no matter how many are in the KPI table.
With that said... Since I live in a SQL world, this would be handled with a serious of cascading joins. Each referencing from the last.
I am concerned that the KPI's are not truly associated to the Client.
If I am reading your structure correctly, you have a date from the client, which connects to a department which has a KPI, but that KPI could be associated to several clients... is that okay? Or am I missing something?
Again, not knowing the world of FileMaker, can you assign a date as a local variable to use as a parameter down the line? Since the date is not directly associated, can you say something like:
DECLARE @KPIDate
@KPIDate = Client::g_Selected_Date
And then down the line, use that @KPIDate as a filter?
KPI::KPI_Date = @KPIDate
Would something like that work?