11 Replies Latest reply on Feb 2, 2016 7:21 AM by RPeters

    Value List - Limiting Results Based on the Date


      Hi Everyone,

      I'm new to the Filemaker Community, so I hope you'll bear with me for this question.  I'm happy to read a link to another discussion if this has already been addressed.  I just couldn't find anything for the life of me!


      Anyways, I work for a non-profit music festival.  I have an income list, where I keep track of who is donating money to what event.  The problem is that my event value list (based on the EventID) shows all of my events - ones that passed months ago, and ones that are upcoming.  Is it possible to limit that list?  When an event has passed more than a week ago, it would be amazing to not see it in the list.  I only want to see values of events that have passed up to 7 days ago, and events that are still upcoming.


      I'm tracking a fair amount of information in my events, including the date.  I'm just not sure how to make that information useful in something like this.


      I'm stumped, and super grateful for any advice!


        • 1. Re: Value List - Limiting Results Based on the Date

          Make an unstored calculation field as

          Get ( CurrentDate ) - 7

          Then, make relation using it

          theFiled <= eventDate

          So you can make value list using the relation "only related values".

          • 2. Re: Value List - Limiting Results Based on the Date

            Okay, I understand the concept of what you mean, but I don't really understand how to go about creating it.  Which table do I create the calculation field in?


            After that, I create a relationship... so that's connecting 'one' event date to 'many' of the newly created calculation field??  Is that correct?


            Once I've created the relationship, I'm not sure how to create a value list with only related values.


            Sorry to ask so many questions, I'm so new to Filemaker.  I'm finding it incredibly useful, but definitely challenging at times!

            • 3. Re: Value List - Limiting Results Based on the Date


              Are you viewing your "Event List" on a layout in List view, or are you using a portal to see the events?


              If you are in a List View layout, I like to create a script which would omit the older events doing a "Find", and assign the script to a button. This way you can view all the events or just the ones in your time frame, whichever you desire by the click of a mouse.



              Enter Find Mode

              Specify Request: (uncheck the Pause box)

              Select the TO and the event date field < Get (CurrentDate) - 7

              Perform Find


              Hope this helps


              • 4. Re: Value List - Limiting Results Based on the Date

                Thanks Jesse! That's a really interesting idea!  Right now, I access the Event List through my income list.  In the income list, I have a pop up menu that has values from my EventID, and a second value showing the event name.  (I actually have it set to only show the value of the second field - event name only, not the ID.)  Anyways, that's what I've been using, but I like the idea of going to a layout where I'm viewing a found set instead.


                I'm having a bit of trouble with the script though.  I know I'm doing something wrong, I just can't figure it out.  Here's what I'm doing in my script:


                1. Enter Find Mode (no problems there)

                2. I uncheck the pause box, and then when I click "Specify Request" I press "New" and I get the "Edit Find Request Box".

                3. This is where I'm a little mixed up.  The one box says "Find records when" so I choose my event date field from there, but then the criteria box won't accept < Get (CurrentDate) - 7.  It says "The value of this field must  be a valid date in the range of years 1 to 4000 and should look like "2013/12/25"."


                Any ideas??

                • 5. Re: Value List - Limiting Results Based on the Date

                  Try this script:


                  1) Set Variable [$Date; Value: Get (CurrentDate) - 7]

                  2) Enter Find Mode

                  (In the edit find request box change the action box from "Find Records" to "Omit Records")

                  Select your Date Field and in the Criteria box put "<$Date"  (no quotations)

                  3) Perform find


                  I tested this and it works.

                  • 6. Re: Value List - Limiting Results Based on the Date

                    Hi Rachel,

                    Value Lists can be very confusing.

                    Here is another option that I use:


                    #1.  In your Events table create a new calculated field and call it  Event_ValueList  (or some other name that makes sense to you).

                         -  the calculation should be text and the Indexing should be set to All


                         -  the calculation is:

                              Case ( YourEventDate > ( Get ( CurrentDate ) - 8 ) ; YourEventName ; "" )

                                        for   YourEventDate     use the date field you have in your file for the event

                                        for   YourEventName    use the field that you are using to identify the event


                              the calculations says: 

                                   in the case that     YourEventDate   is more recent than 8 days ago  YourEventDate > ( Get ( CurrentDate ) - 8 )

                                   then     the calculation returns  YourEventName

                                   otherwise    the calculation returns and empty string     this is signified by the 2 quotation marks   ""


                    #2.  Now for your Value List:

                         in the dialogue box where you set up the value list:

                        •   "Use values from first field"  should be your ID field

                         •  check the box for "Also display values from second field ... and select the new calculated field    Event_ValueList

                         •  below the field lists you should:

                              -  click the radio button for   "Include all values"

                              -  click the radio button for   "Second field"

                                   ** note:  if you do NOT want to display the ID then check the box for    "Show values only from second field"


                    Let me know if that works.  Michael

                    • 7. Re: Value List - Limiting Results Based on the Date

                      Hi Rachael,


                      If you're really looking for a drop-down list or pop-up menu then I think user19752 put you on the right track.


                      If you're interested in alternative solutions you could consider the following. Instead of a pop-up menu or drop-down list on the field (I'll call it event_ID_target) in which you want to enter the eventID you could make a pop-up button besides the field. In the pop-up window you make a portal that shows records from your table with events. In the definition of the portal you then specify a calculation that filters the records that you want to see (i.e. the events that are not too old). In the portal you need to define a button with a script that inserts the EventID into the event_ID_target, closes the popover and (if you want) goes to the next field.


                      For the user who enters the data this may not be the handiest solution because he/she needs to click more. On the up-side you don't need to define a new relation. I find defining the filter calculation easier than defining a new relation. Also on the up-side is that you can present a lot more information about the events and control the layout far better in a portal than in a pop-up list or drop-down menu.


                      Again, if you don't need to show more information about the events in the list, you'd better stick to a drop-down list or pop-up menu.





                      • 8. Re: Value List - Limiting Results Based on the Date

                        Any table but usually I use the table which use the value list.


                        You don't need to care about one/many.


                        For "Use values from" , use the right side table on the relation I wrote as

                        theCaluculationField <= eventDate

                        then for "Include only reralted..." use left side table.

                        • 9. Re: Value List - Limiting Results Based on the Date

                          Hi Jesse,


                          Thanks for spelling this out for me step by step - it worked and it's great!!


                          And thanks to everyone else for their responses.  It's neat to see all the different ways to achieve a very similar result.  I so appreciate all of your advice!!



                          • 10. Re: Value List - Limiting Results Based on the Date


                            Glad I could help. If you were trying to select an event by way of limiting values in a value list they are some good ideas listed here in this thread by both user19752 and Michael.

                            • 11. Re: Value List - Limiting Results Based on the Date

                              Yes!  I've actually printed them out and made a note in my "Try Sometime" list!

                              Thanks again!