7 Replies Latest reply on Feb 16, 2011 9:51 AM by philmodjunk

    Date Searches

    Bricktop

      Title

      Date Searches

      Post

      Hi Everyone,

      I have drop down calenders for start and end dates on an order form, when I go to my "Lines" layout, I want to do a find of a date range I click find and I tried start date 10/*/10 and end date 12/*/10 and it shows me all types records. What am I doing wrong? I just want to see the lines or order based on the date range.

        • 1. Re: Date Searches
          philmodjunk

          Try this form: 10/1/10...12/31/10

          I often help out users with a script when they need to search by date range. I define two global fields for the first and last dates--formatted with pop up calendars and then use a script to perform the find, constructing a date range expression like the above example.

          • 2. Re: Date Searches
            Bricktop

            No I cant do that because it only looks for those exact dates. I guess I need a script, and it sounds pretty in depth. DO you have any links to your solution links? Is there a scripting book for FMP11?

            • 3. Re: Date Searches
              philmodjunk

              No I cant do that because it only looks for those exact dates.

              The script does the same thing that I suggested for a manual search with 10/1/10...12/31/10

              This finds a range of dates from October 1, 2010 thorugh December 31, 2010 (I'm using MMDDYY format) First, make sure these are fields of type date and not text fields. Then Enter find mode and enter that exact expression into your date field to find dates in that range. Modify the format accordingly if you are using DDMMYY system settings.

              • 4. Re: Date Searches
                Bricktop

                Let me explain a bit better I have a rental item, I being the middle man, have a rental start date and end day I want to search the invoice with range dates. I guess its a seach range between a range of dates its not like searching for one date. Its a search on my start AND end dates.

                • 5. Re: Date Searches
                  philmodjunk

                  OK, but that raises some questions about what you really want here. If you specify 10/1/10 and 12/31/10 do you only want records where

                  start date = 10/1/10, End date 12/31/10

                  or do you want any records where their date range overlaps the date range you've specified?

                  The date ranges will over lap if the specified start date falls within the interval of a record's date range or when the specified end date falls within this interval or when the specified date range completely encloses the specified date range. This can be done with a single find that uses three find requests for these three different forms of "overlap".

                  If this is what you are looking for, I have a demo file that illustrates a scripted find for that result. Let me now if it's what you want and I'll post a download link along with a bit more explanation about how it works.

                  • 6. Re: Date Searches
                    Bricktop

                    YES!!! That sound right. Because if that start date was a day before search dates thats an overlap and then I want it to show. Yes I like your idea 1 find with 3 types of overlap scenarios!!!

                    • 7. Re: Date Searches
                      philmodjunk

                      Here's the link:  http://www.4shared.com/file/eAi4f485/CheckReservations.html

                      How it works:

                      This demo requires the user to enter a room number and a pair of start and end dates in global fields located at the top of the screen. When you click Check Res. You either see all the conflicting reservations for the specified room and an error message that your specified room and date range conflicts or you'll see a new record created with your specified room and date range.

                      Be careful to understand this key detail:

                      The script, as written, sometimes creates the illusion that it modified the previous reservation instead of creating a new reservation record. Example: If you first create a reservation for room 101, 10/1/10...12/31/10 if there are no conflicts, the result is one visible record with that data. If you then try to create a reservation for room 101 for 1/1/11...1/20/111 and their are no conflicts, the result is one visible record with that data. Since it happens in the blink of an eye, it looks like the script just changed the dates on the previous record because all other records have been omitted from the current found set. If you select Show All Records, you'll see that both reservation records exist.

                      This is just a bare bones example, there are many ways you can modify this script to better adapt it to your needs.