Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Displaying and using a list of UNIQUE values from a find

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

721 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