3 Replies Latest reply on Jul 7, 2017 7:29 AM by philmodjunk

    Filter value list by date

    3rdcycle

      Hi everyone

       

      This should be a fairly common thing, yet I can't seem to find a simple solution on the forums or anywhere else.

       

      I have an employees table with name, start_date and end_date fields. In a dropdown I want to show only the employees with with end_date empty or > today. What's the simplest way to filter my value list by date?

        • 1. Re: Filter value list by date
          philmodjunk

          Add a stored (indexed) calculation field, cDateKey with a date result type written thusly:

           

          If ( IsEmpty (end_Date ) ; Date ( 1 ; 1 ; 3000 ) ; end_date ) )

           

          In the table that's to be the layout context for your value list, add an unstored calculation field, cToday, with result type:

           

          Get ( CurrentDate )

           

          Set up a relationship:

           

          LayoutTableOccurrence::cToday < Employees::cDateKey

           

          Base your conditional value list on this relationship and you should get the results that you've requested.

           

          If you need more detail on how to set up the value list, please reply back and ask for that information.

          2 of 2 people found this helpful
          • 2. Re: Filter value list by date
            3rdcycle

            Fantastic, this worked perfectly. I ended up putting cToday into a separate table. As a software dev those globals everywhere make me uneasy .

             

            Thanks a lot.

            • 3. Re: Filter value list by date
              philmodjunk

              Except that cToday was not described as "global" but rather "unstored". These are not the same thing. This field needs to be unstored it does not need to be global. It does matter in this case where you define the field. It has to be located in a table that works in terms of the relationships because it's a match field in the conditional value list's relationship. Put it in the wrong table and the CVL doesn't work as expected.

               

              That said, I agree that putting global fields in a single dedicated table is a good idea--except for when you are also using that global as a match field in a relationship.