12 Replies Latest reply on Aug 7, 2009 11:42 AM by JMillerSki

    Filtering Values for a list by date range

    JMillerSki

      Title

      Filtering Values for a list by date range

      Post

      I have a series of values with a start date and end date.  I want to make a value list of these for a drop down box.  So like this:

      Table 1 Fields : Start Date, End Date, TargetValue

       

      I want to populate a field in Table 2 with the targetvalue list where Start Date <= Get(CurrentDate) <=End Date

       

      That way I get all of the values that are current.  I think a self join figures in somewhere but I'm new to FM (working in a demo version for now) and can't get it to work.  

       

      Thanks in advance.

       

      (I tried a calc field where the filter was :

       

      FilterValues ( tAdSchedule::Source Code; tAdSchedule::Run Date ≤ Get(CurrentDate) ≤ tAdSchedule::End Date)

       

      to no avail)

        • 1. Re: Filtering Values for a list by date range
          comment_1
            

          JMillerSki wrote:
          I think a self join figures in somewhere

          I think so too. But first you need to add an unstored calculation field cToday (result is Date) =

           

          Get(CurrentDate)

           

          Then the self-join can be defined as:

           

          YourTable::cToday ≥ YourTable 2::Start Date

          AND

          YourTable::cToday ≤ YourTable 2::End Date

           

          Set the value list to use values from YourTable 2, include only related values starting from YourTable.


          • 2. Re: Filtering Values for a list by date range
            JMillerSki
               This makes absolute total sense, but still I get no values in my dropdown box.  I checked the date, it finds today's date and today's date is in the range that I'm looking for, but no targetvalue.  I did make a relationship between the targetvalue in Table 2 and the dropdown field, should I do that relationship somewhere else?
            • 3. Re: Filtering Values for a list by date range
              comment_1
                

              I am afraid I don't quite follow. Please provide a wider context for what you are doing: which tables are involved, where is the field that is used for selecting the value, etc. Using meaningful names for the tables and fields would also help. This part I don't get at all:

               


              JMillerSki wrote:
              I did make a relationship between the targetvalue in Table 2 and the dropdown field

               

               


              • 4. Re: Filtering Values for a list by date range
                JMillerSki
                  

                Okey doke...

                 

                Table 1                        Table 2

                Source                         SourceCode

                Run Date                      cDate

                End Date

                 

                Relationships:  cDate >= Run Date AND cDate <= End Date

                                    Source = SourceCode

                 

                What I want is for SourceCode to be a dropdown box that pulls up all of the values from Source that are in the date range.  I'll then choose the correct Source to populate the SourceCode Field.  Perhaps I should store the final value in another field... yes?

                • 5. Re: Filtering Values for a list by date range
                  JMillerSki
                     I forgot the Value List info: SourceCode is controled as a drop down box using the Value list Source Code, which uses values from field Source from the first table and Includes only related values starting from Table 2, as you suggested.  I get no returns in my dropdown box with this set up.
                  • 6. Re: Filtering Values for a list by date range
                    comment_1
                       If I understand correctly, you need two relationships here. One is based on matching:

                    Table 1::Source = Table 2::SourceCode

                    This relationship will function AFTER you have selected the SourceCode value in Table 2.

                    You need another relationship to narrow down the values available for selection. This will be the relationship described in my first post, where YourTable would be Table 1, and YourTable 2 would be a second occurrence of Table 1 (some meaningful names you have picked…).


                    • 7. Re: Filtering Values for a list by date range
                      JMillerSki
                        

                      Heh, Table 1 is tAdSchedule - where I have a list of advertising running and dates that they are active.  Table 2 is tLead where we are setting a sales lead.  I want tLead to tell me which ad the customer is responding to, but I only want the dropdown box to be populated with current ads, not ads that have expired or aren't running yet.

                       

                      I took out the relationship between tLead::Source and tAdSchedule::Source Code  so now the only relationship is the dates deal.  Still no results in my dropdown box.  There are three values in tAdSchedule that match the criteria as far as dates.  I should see a value list of the three Source Code values that are in the current date range.  Thanks for your patience :)

                      • 8. Re: Filtering Values for a list by date range
                        comment_1
                           Hard to see from here what went wrong there. Try putting a portal to the tAdSchedule table on a layout of tLead and see if it shows the records that should be related. Also check your field types.
                        • 9. Re: Filtering Values for a list by date range
                          JMillerSki
                             Good call on checking my field types... All that, and my dates were still set as text.  Still couldn't have found it without you, good day to you!
                          • 10. Re: Filtering Values for a list by date range
                            JMillerSki
                              

                            New issue, when I change the value in tLead::Source Code to choose it, it changes the first related value in the tAdSchedule::Source to whatever I chose. 

                             

                            Ex. dropdown says "Newspaper1" "Newspaper2" "Newspaper3" - it is set to "Newspaper1" but I choose "Newspaper3".  Now my dropdown box only has 2 and 3 in it, and the value in tAdSchedule::Source "Newspaper1" is Changed to "Newpaper3".

                             

                            There is no relationship between tLead::Source Code and tAdSchedule::Source

                             

                            Thoughts?

                            • 11. Re: Filtering Values for a list by date range
                              comment_1
                                 It sounds like the field you are using to make the selection isn't what you think it is.
                              • 12. Re: Filtering Values for a list by date range
                                JMillerSki
                                  

                                I sure appreciate your help, but I have no idea what that means...  Can you be more specific?  Do I have to have one field to be a drop down box then have the value go into another field for storage?  I'm not sure how this "chooser" is changing the value in table tAdSchedule.

                                 

                                Nevermind my friend, I believe I have it licked...