13 Replies Latest reply on Aug 19, 2016 9:08 AM by realgrouchy

    Finding records within a month

    ChrisPink

      I have a set of records with a start date and an end date

       

      For instance Start Date = 20/05/2016 End Date = 13/07/2016

       

      So this record is relevant during June.

       

      How do I find a record with June relevance?

       

      Seems a simple question with no simple answer.

       

      Thanks for any help.

        • 1. Re: Finding records within a month
          David Moyer

          Hi,

          how's this?

          if start <= 6/30 AND end  >=  6/1

           

          Edit:

          You'd probably need to add:

          ... OR ( start <= 6/30 AND isempty(end) )

          • 2. Re: Finding records within a month
            philmodjunk

            Depends on how you define "relevance" here. Do all your records have start and end dates?

             

            Are they relevant if the start date falls some where between the start and end?

            If the end date falls between the specified start and end?

            If the start date is before the start and the end falls after the end?

             

            Note that this requires different criteria for each of the three examples.

            • 3. Re: Finding records within a month
              ChrisPink

              Yes, all records have start and end dates. Relevance in this context means some part of their period is within the month searching.

               

              As with many things search, the solution is 'obvious' once voiced, thanks to David Moyer, and mirrors the English way of expressing it "as long as it ends after the beginning of June AND begins before the end of June" it includes those events.

              • 4. Re: Finding records within a month
                Markus Schneider

                You can search for the month, at least with the german setup -> 6.2016 will find all entries in june, 2016

                • 5. Re: Finding records within a month
                  David Moyer

                  What made this difficult was that the two date fields might not contain a date in the relevant period.

                  I started down the road of "this is easy", then found the answer to be counter-intuitive.

                  • 6. Re: Finding records within a month
                    beverly

                    or 6/*/2016 will find the entire month (any days) for June 2016.

                    beverly

                    • 7. Re: Finding records within a month
                      beverly

                      BTW: the "*" wildcard/symbol may need to be used like this:

                      */6/2016, if your dates are dd/mm/yyyy

                      • 8. Re: Finding records within a month
                        fmpdude

                        This answer works, but it's hard coded.

                         

                        Why not modify the answer given to give you something generic that would work in ... ANY month?

                         

                        Unless you're only ever going to be concerned about June, 2016, how about:

                         

                        Month ( Get(CurrentDate) ) & "/*/" & YEAR(Get(CurrentDate))

                         

                        This might take a little tweaking, but you get the idea.

                         

                        HOPE THIS HELPS.

                        1 of 1 people found this helpful
                        • 9. Re: Finding records within a month
                          David Moyer

                          Hi,

                          either there's a disconnect, or there's something rudimentary about FM that I don't understand.  Given ChrisPink's example of finding records "relevant" to June, and that it's likely that neither the Start Date nor the End Date contain a date in June, does "find 6/*/2016" work?

                          • 10. Re: Finding records within a month
                            beverly

                            only:

                            1. you did not specify a year (so current year is assumed) and that is ok if the search if for current year.

                             

                            2. you specify the beginning and end of June with 1, 30. for flexibility (leap years and all), the preferred method is to use:

                                 Date(6;1;2016) & Date(6+1;0;2016) // the "0" is the last day of the previous month

                             

                            3. finding in the range (over two fields) won't help with the use of the wildcard *, as that is any day in the month, so the >= and <= are needed, as you have

                             

                            my reply to Markus was mostly a reference to finding any day in the month with the use of the wildcard. It may have not added to the discussion.

                             

                            beverly

                            • 11. Re: Finding records within a month
                              ChrisPink

                              indeed, I simplified the question (massively) as the script is to generate a timeline based on a drop down month selector so June is entirely arbitrary.

                               

                              and  sometime around November I'll have to start worrying about handling the year.

                              • 12. Re: Finding records within a month
                                ChrisPink

                                unfortunately that doesn't find ranges that start in May OR end in July. See David's solution. But thank you anyway.

                                • 13. Re: Finding records within a month
                                  realgrouchy

                                  I'm having difficulty understanding what's wrong with David's solution in the first reply to the original post. It's been marked as correct but there are lots of replies after it. Doesn't that do exactly what you wanted to do? This would catch entries that have a start date in May ( <= 6/30) and an end date in July ( >= 6/1 ).

                                   

                                  You can then replace 6/30 and 6/1 with the formulas shown by Beverly in her point #2:

                                   

                                  If you have two dropdown numeric global fields called SelectMonth and SelectYear, your formula would be:

                                   

                                  • if StartDate <= Date(SelectMonth+1;0;SelectYear) AND EndDate  >=  Date(SelectMonth;1;SelectYear)

                                   

                                  If you wanted to use a single global dropdown field called SelectMonthAndYear, populated by a value list with months hard-coded as text (e.g. 1/2016, 2/2016, 3/2016), then you would want to preface your calculation with a Let statement to parse the month and year from it:

                                   

                                  • Let (
                                  • [ SelectMonth = Left ( SelectMonthAndYear ; Position ( SelectMonthAndYear ; "/" ; 1 ; 1 ) - 1 ) ; SelectYear = Right ( SelectMonthAndYear ; Length ( SelectMonthAndYear ) - Position ( SelectMonthAndYear ; "/" ; 1 ; 1 ) ] ;
                                  • if StartDate <= Date(SelectMonth+1;0;SelectYear) AND EndDate  >=  Date(SelectMonth;1;SelectYear)
                                  • )

                                   

                                  And you could use various tricks and calculations to automatically generate a value list of months and years to populate that field, and to display them as "January", "February", etc. instead of 1, 2...

                                   

                                  - RG>