?
Solved

Displaying and using a list of UNIQUE values from a find

Posted on 2013-01-03
5
Medium Priority
?
983 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 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
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…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

589 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