As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Parent/Child/Grandchild Portal and filtering the grandchildren help needed
Posted on 2012-12-24
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
2-
2
- +1
10 Comments
I will admit first that I am NOT a FileMaker Pro person. My comments are simply data base design related and might spur a solution within the limits of your environment.
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?
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?
The KPIs are associated with a department if (one dept to many KPIs that are date driven). The depts (many) are then associated with a client (one). The global date field is a FM Pro trick to work with layouts.
If g_Selected Date is a global field, it may not matter in which table it lives. Can you push it down to the department table and then build the relationship from there?
Here is a little sample that works for the problem you described, but I don't know if it will work for your overall solution.
KPI-Selector.fp7
Here is a little sample that works for the problem you described, but I don't know if it will work for your overall solution.
KPI-Selector.fp7
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Thanks.
I just checked this and although it works, we are running into the problem of displaying the department name in the filtered list. Take a look at the file (it is a FMP 12 file, can you read that? If you can't, you can recreate what I did...see below.)
I added Dept name to the Dept table. Then I added the Dept Table:Dept Name to the filtered list. You will notice that Dept Name repeats and is not displaying the linked department name properly.
Unfortunately, displaying the department name in the filtered list is needed...and the names can change slightly, so placing it in the KPI table isn't really an option.
Thoughts?
I just checked this and although it works, we are running into the problem of displaying the department name in the filtered list. Take a look at the file (it is a FMP 12 file, can you read that? If you can't, you can recreate what I did...see below.)
I added Dept name to the Dept table. Then I added the Dept Table:Dept Name to the filtered list. You will notice that Dept Name repeats and is not displaying the linked department name properly.
Unfortunately, displaying the department name in the filtered list is needed...and the names can change slightly, so placing it in the KPI table isn't really an option.
Thoughts?
I think that was the file from a different question ;-)
There are a couple of ways of linking clients to multiple departments, the practicality of which will partly depend on how many departments you have. The attached sample uses a repeating field which is easy for a small number of departments.
To get the department name to display correctly, the deptID has to be linked to the KPI: deptID
KPI-portal.fmp12
There are a couple of ways of linking clients to multiple departments, the practicality of which will partly depend on how many departments you have. The attached sample uses a repeating field which is easy for a small number of departments.
To get the department name to display correctly, the deptID has to be linked to the KPI: deptID
KPI-portal.fmp12
The solution ended up being adding a copy of the dept table "downstream" of the KPI table. THis allowed us to properly display the department names and still base the portal on the KPI table.
Featured Post
This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Pop up windows can be a useful feature of any Filemaker database. Though best used sparingly, they can be employed in a multitude of different ways, for example; as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move. In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 23 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.