Solved

Displaying and using a list of UNIQUE values from a find

Posted on 2013-01-03
5
836 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
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

713 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