1 2 Previous Next 17 Replies Latest reply on Aug 2, 2015 2:16 PM by scottlynch

    Issues with date range filter

    scottlynch

      Title

      Issues with date range filter

      Post

      Im struggling to filter the correct number of records between two dates.

      An example, 
      In my Time Off portal I enter employee leave as below;
       
      Type.                  Start Date.       End Date.        Duration
      Paid Leave.        1/7/15.             3/7/15.             3 Days
       
       
      In my Time Off layout (List View) If I filter the range of dates to show leave records in the range of 1/7/15 - 2/7/15 it returns no results , when I would like it to show two dates the 1st and 2nd July 2015
       
      If I enter the leave dates into the portal individually i.e. 
      1/7/15 - 1 day
      2/7/15 - 1 day 
      3/7/15 - 1 day
      Then it filters as expected. 
      So I guess what I'm asking is how do i configure the filter to show the correct leave records between two date ranges without having to enter the leave records individually?
      Many thanks
       

        • 1. Re: Issues with date range filter
          SteveMartino

          How is this set up? I assume this is a global field in the portal.  How is the relationship set up?  If it's one global field and you enter a range, is there a script or script trigger attached?

          • 2. Re: Issues with date range filter
            GuyStevens

            How is it exactly that you "filter" records in a list view? Because you can have a filter in a portal. But in a list view you would do a find.

            A find within a date range would be the two dates with three dots in between. Like: "1/7/2015...2/7/2015"

            • 3. Re: Issues with date range filter
              philmodjunk

              Yes, but you have a problem here. Your "leave" records themselves record a date range. This complicates your search criteria.

              You might have a "leave" from 10/1/2015 to 10/20/2015

              If you specify a "range" criterion of 10/2/2015...10/15/2015 in your find, no matter which of the two dates you specify it in, you won't find that example record. Both 10/1 and 10/20 are outside of the start and end range of your search range, but clearly this record should be included in the resulting found set.

              Using the data as entered, you'd need to perform one find with three find requests. This is most easily managed with the enter find mode/set field method for which multiple examples can be found here: Scripted Find Examples

              Your first request would specify find criteria of gStartDate...gEndDate format on the leave::startDate field.
              The second would leave the Leave::StartDate field and specify this range on the Leave::endDate field.
              The third would specify ">" & gStartDate in the Leave::StartDate field and "<" & gEndDate in the Leave::EndDate field.

              First checks to see if the leave start date falls within the specified rang. The second checks to see if the leave end date falls within th especified range. The last checks to see if the specified leave "encloses" the specified range. (Our original example here.)

              There is an alternative method possible that uses a relationship and a pair of fields that record the date ranges as a list of dates listing all dates from start to end. This makes for a simpler script, but also requires adding a relationship to your relationship graph.
               

              • 4. Re: Issues with date range filter
                scottlynch

                HI All,

                Thanks for the input,

                Steve,

                Yes there is a script attached to a find button and I have attached pictures showing the “find” script and my relationship graph.

                Guy,

                Sorry for my lack of detail. It’s a scripted find that I’m using to filter the leave records. I have two global fields set up in my Time Off Table. These are gFilterDateStart and gFilterDateEnd as shown in the script picture. And this is where the date range is entered to create the find. BTW great Udemy videos, really helpful and very well explained, waiting for the next one. J

                Phil,

                That’s exactly what I’m after. Before posting here I read the FileMaker help files. And they suggested setting up a filed called ADateField which calculates “Personnel Records::Start Date...Personnel Records::End Date”.

                But when using this as the Set Field i.e. Set Field [Time Off::AdateField; Time Off::gFilterDateStart & “...” & Time Off::gFilterDateEnd it produced no records at all?

                I’ve been looking through the examples of the link you sent. And If I’m understanding correctly, all the three find requests would be included in the one script?

                You also mention an alternative method that uses a relationship and a pair of fields. Is that easy to explain? I have attached my relationship graph. Although I do need to clean away redundant fields that are still showing as this database has grown over the months and changed directions..... And I thought utilising a starter solution would be easy. wink

                 

                 

                • 5. Re: Issues with date range filter
                  scottlynch
                  /files/670c990d57/Screenshot_2015-07-31_08.03.42.png 999x567
                  • 6. Re: Issues with date range filter
                    philmodjunk

                    Not only would it be in one script, it would be three requests in one find. (Think of reach "request" as a special "search record" where you are telling FileMaker: "find all records matching any one of these requests...".)

                    The other method replaces your two date ranges (the one specified for your search and the one inside each Time Off records) with a text field listing all the dates from date start to date end. Either a looping script or a recursive custom function can be used to generate such a list of dates.

                    Then this relationship, evaluated from a layout based on Time Off:

                    Time Off:::DateListSearch = Time Off 2::DateList (TIme Off 2 is a second table occurrence of Time Off.)

                    where DateListSearch has all the dates from gFilterDateStart to gFilterDateEnd and DateList has all dates from Start Date to End Date.

                    Then a portal to Time Off 2 on your Time Off layout will show any records where at least one date in DateList is also found in DateListSearch. And Go To Related records can use this self join to produce a found set of these matching records also.

                    • 7. Re: Issues with date range filter
                      scottlynch

                      Hi Phil,

                      With regards the 3rd find request you specified below. I'm struggling to know how to build that into a calculation? I can set them up as two separate Set Fields as in the attached screenshot. But can't figure out how to have two Set Fields (Leave::StartDate field & Leave::EndDate field) in the one find request?

                      Your first request would specify find criteria of gStartDate...gEndDate format on the leave::startDate field.
                      The second would leave the Leave::StartDate field and specify this range on the Leave::endDate field.
                      The third would specify ">" & gStartDate in the Leave::StartDate field and "<" & gEndDate in the Leave::EndDate field.

                      • 8. Re: Issues with date range filter
                        philmodjunk

                        Please examine the sample scripts from the link provided again. You are misunderstanding what is meant by a "Request".

                        Your script should resemble this:

                        Enter find mode []
                        Set Field [Time Off::Start Date ; Time Off::gFilterDateStart & "..." & Time Off::gFilterDateEnd ]
                        New Record/Request
                        Set Field [Time Off::End Date ; Time Off::gFilterDateStart & "..." & Time Off::gFilterDateEnd ]
                        New Record/Request
                        Set Field [Time Off::Start Date ; ">" & Time Off::gFilterDateStart ]
                        Set Field [Time Off ::End Date ; "<" & Time Off::gFilterDateEnd ]
                        Set Error Capture [on]
                        Perform Find []
                         

                        • 9. Re: Issues with date range filter
                          scottlynch

                          Hi Phil

                          Im sorry for my misunderstanding but i couldn't see anything relating to New Record / Request in the link you copied, only Set Fields, but its starting to makes sense now i can see it written down.
                          I have attached another screen shot as unfortunately even with the new script, I still can't make this search criteria produce an accurate found set based upon the date range entered.  (I've cloned out staff names from the list, that's why they are missing)
                          Any ideas?

                          • 10. Re: Issues with date range filter
                            GuyStevens

                            In that screenshot you did find someone in that date range. So I would say that that is already looking good.

                            What seems to be the problem? Are you expecting to find more records there?

                            Have you checked that your global fields are actually set to global storage?

                            • 11. Re: Issues with date range filter
                              scottlynch

                              Hi Guy,

                              Yes it found two employee leave records, but if you look closely at their leave dates, they fall out of the Find range of dates. i.e 19/4/15 to 20/4/15

                              i've cloned out their names, so it does look confusing i must admit. I should of created made up personnel names for the screenshot. 

                              If it was working correctly the first person should only show 1 days leave within the Find range which is 20/4/15. And the second person should show 1 days leave being the 19/4/15. Does that make sense?

                               

                               

                              • 12. Re: Issues with date range filter
                                GuyStevens

                                But the way the find is set up it is correct.

                                It finds all leave records that have any kind of intersection with your date range. Both found records touch your global date range. 

                                If you only want to find leave records that are exactly and completely within the global date range then you need to set up your find script differently. 

                                But the way it is set up right now you will find every single record where the date ranges conflict or touch each other.

                                It is also not possible for only one day to be found because you don't have separate records for every single day. In your one record you specified the range for the leave.

                                If you want to find separate single leave days then you need to store every single leave day in a separate record.

                                And then you can find individual leave days within a date range.

                                It would also be a lot simpler to set up.

                                But it might be different to set up the creation of your leave days if you specify a date range of leave. Because then all the separate days need to be created as records. This is not to hard to do but might become a little more complicated when weekends are involved.

                                • 13. Re: Issues with date range filter
                                  scottlynch

                                  This is what I was afraid of Guy with "one leave record" showing a date range of 5 days, and not being able to split them up. Which I guess is logical as its only one record. I was hoping that the leave duration could be spilt some how?

                                  When I enter the leave records individually the global Find range works accurately as I want. I was just trying to reduce the amount of data input involved with a large personnel department.

                                  As its only one leave record that calculates a leave duration, and not individual leave records. Would this also cause a problem with Phil's other suggestion of using a Relationship with a pair of fields as he describes previously?

                                  • 14. Re: Issues with date range filter
                                    GuyStevens

                                    The use of the relationship would have the exact same outcome as what you have now. As long as you make one record with a date range you will not be able to split that up.

                                    But if you have a person who goes on leave for 5 days starting a certain date you can make it so that you just enter the start date and the amount of date and let a script create all the individual date records.

                                    This is actually pretty simple and would allow you to have every leave date as a separate record.

                                    1 2 Previous Next