1 Reply Latest reply on Dec 4, 2014 6:56 AM by philmodjunk

    Filtered Value List or Omit Duplicates



      Filtered Value List or Omit Duplicates


      I am working on a Staff roster Database and am wanting to show a list with the "shift date" from the Table:Shifts that is sorted in Descending order and only shows the last 2 months. I have it as a drop down value list using values from field: Shifts::Date, but this is naturally sorted in Ascending order and will end up showing a lot of old shift dates. Is there anyway to evoke the Specify Calculation Box here?

      My other thought was to have it as Popover button with a Portal in and a TO relationship Shifts and Shifts2, with this I have the portal sorting and can filter the last 60 days, but I don't know how to filter the duplicates, as many shifts in a week have the "same date".

      Can you help me with either one of these options? Thanks in advance.



        • 1. Re: Filtered Value List or Omit Duplicates

          There are a number of ways to restrict the values that appear in a value list. A calculation dialog inside Manage | Value Lists, however, is not an option.

          The most commonly used method is to set up a relationship to limit the values. If, for example, you defined an unstored calculation field with a date result type as:  Get ( CurrentDate ) - 60, you could define a relationship with an inequality operator instead of the default = to match to only records that are 60 days old or newer. That relationship could then become the basis for your conditional value list.

          I recommend that you download the free file: "Adventures in FileMaking #1 - Conditional Value Lists". This file demonstrates 10 different ways to produce a conditional value list. Using a relationship is the first such example demonstrated. This is an unlocked database file with detailed explanations and documentation on each method demonstrated so that you can read the explanatory text and then drop into design mode in order to examine the actual implementation of what was demonstrated.