Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 964
  • Last Modified:

Displaying and using a list of UNIQUE values from a find

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
OTAdmin
Asked:
OTAdmin
  • 3
  • 2
2 Solutions
 
Will LovingPresidentCommented:
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
 
OTAdminAuthor Commented:
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
 
Will LovingPresidentCommented:
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
 
OTAdminAuthor Commented:
0
 
OTAdminAuthor Commented:
This is what I ended up using.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now