Solved

Displaying and using a list of UNIQUE values from a find

Posted on 2013-01-03
5
882 Views
Last Modified: 2013-01-09
When searching through a table (called KPI - see below), there will be multiple entries for each date per client (one for each department).  There is a extra field, called committed, that gets flagged when a record is marked as "complete".  Basically, I want to display of list dates for the selected client where there are records that are not committed.  From there, the user will select a date, which will set the g_Selected Date global field in a table higher up in the relationship, and take them to the layout for that date.  The global field and the layout is already in use and working fine.  I just don't know how to show a list of "unique dates" from the KPI table for the selected client where the committed field is 0.  I'm also looking for suggestions on the easiest way to do this - a value list, a portal, something else?  Of note - this database is plublished via IWP, so the solution has to work there.
 
 
 
Table Structure:
 
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 client, one per date for each department ID)
 - dept ID
 - KPI date
 - KPI 1
 - KPI 2
 - Committed (0 or 1)
0
Comment
Question by:OTAdmin
[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
  • 3
  • 2
5 Comments
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 500 total points
ID: 38742585
Create a calculation field in KPI that displays the KPT date unless it's marked as Committed (so it shows only uncommitted dates)
Use the List() function in a calculation field to generate the list of uncommitted KPI Dates, e.g. List( Client_KPI::KPI Dates ). That gives you a list of all uncommitted dates but there will be dupes, so....
Go to http://www.briandunning.com/cf/596 and get the UniqueValues() Custom Function. Once you have this Custom Function (CF) available in your file, wrap it around your List calculation like this: UniqueValues( List( Client_KPI::KPI Dates ) )

If you set the relationship of Client to KPI to sort at the relationship level, in the graph ("Sort Records") by Date, then the dates in the list should also sort by Date.

In order to install the Custom Function, you need to have FileMaker Pro Advanced. If you don't have Advanced, you can tinker with the calculation used in the Custom Function and use it in a regular calculation such as the one use to generate the List of dates.
0
 

Author Comment

by:OTAdmin
ID: 38744531
How do I convert the list returned by the custom function into a selection list so the user can choose a date and click a button to move to another layout with that date passed as the variable?  We are already passing variables around (so I know how to do that).  I don't know how to take this list that is in a single field and return it as something the user can select a unique date out of.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 38744657
I'll need to think about the best way to do that but since this question is answered, I suggest that that be a separate issue.
0
 

Accepted Solution

by:
OTAdmin earned 0 total points
ID: 38745131
0
 

Author Closing Comment

by:OTAdmin
ID: 38758253
This is what I ended up using.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question