3 Replies Latest reply on Oct 24, 2016 11:34 AM by mike_wallace_rcc

    Searching records by month

    jdevans

      I have a table that stores records (for a very specific reason) using a Month field (1-12), separately from the Year field (4-digit year) instead of just using a single date field.

       

      I have a script that runs a find against the records in that table by a date range (this month, last month, this quarter, last quarter, this Half-year, last half-year, etc...), but as the year nears end, I found a problem that previously wasn't tested. If the date range spans over a new year, the find fails.

       

      So as of right now, there is data in that table for the current month (10, 2016). I can run a find against the table using "10..." in the Month field and find records. However, if I use the following, it fails "10...3". Even if I simultaneously enter in 2016...2017 into the Year field, it fails. I think it is getting hosed-up because it fails to correctly interpret the "10...3" as October this year through March next year.

       

      Any thoughts on a solution?

        • 1. Re: Searching records by month
          philmodjunk

          I suggest you put in an actual date field as this makes for simpler searching and sorting.

           

          Another option would be to make two find requests.

           

          Specify 10...12 and 2016 in one find request. Create a new request and specify 1..3 and 2017 in the other.

           

          These two requests can be generated manually or automatically in a script.

          1 of 1 people found this helpful
          • 2. Re: Searching records by month
            jdevans

            Ok, I'm working on a concatenated field that combines the separate month and year fields into one that reads month/*/year. I just wonder if Filemaker will be able to interpret it as a date. Maybe use Get as Date with it?

             

            But I do like the idea you have. I may give that a try.

            • 3. Re: Searching records by month
              mike_wallace_rcc

              Create a calculated field (YearMonth) with the following calculation:

              YearField & Right ( "0" & MonthField ; 2 )

               

              This will give you 201611 and 201702 from Nov 2016 and Feb 2017 respectively

               

              You can then find on a range with something like:

              201611...201702

               

              You can also sort appropriately by this field as well.

               

              Edit:  Additional bonus, you can create subsummaries by this field if you want your reports to break a the month and keep sorted correctly.

              1 of 1 people found this helpful