11 Replies Latest reply on Aug 3, 2009 8:26 AM by Brin

    Searching for ranges of dates in different formats

    Brin

      Title

      Searching for ranges of dates in different formats

      Post

      I am a UK FileMaker newbie playing with the trial version of FMP10 for use as a databse for a church archive going back to 1850. Documents and sources within the archive are sometimes fully dated (dd/mmm/yyyy), sometimes only the month and year is known (mmm/yyyy) and sometimes only the year is known (yyyy). The archive needs to be searchable by reference to dates in any of these formats (though I appreciate you can't mix them) as well as by text/keywords. So my thought was to have three separate date fields, in the hope that I could devise a form of query that would search,e.g., for records on a keyword topic "between 1 Jan 1900 and 31 Dec 1932 OR between Jan 1900 and Dec 1932 OR between 1900 and 1932". However, FMP is not letting me set date-formatting options allowing the entry of a date in a field as only a month and year, or only a year.

      Does this sound like a difficulty that anyone else has encountered ?

      I have not yet started trying to write scripts or calculation fields but am willing to have a go if anyone can tell me that it is within novice capability by following instructions !

        • 1. Re: Searching for ranges of dates in different formats
          ninja
            

          Howdy Brin,

           

          I don't know if this would pass muster with yourself and/or others, but you could simply "fill in the missing pieces" by using a "1".

           

          March 4th 1932 would be 3/4/1932 (or 4/3/1932 depending on your convention)

          March 1932 would be 3/1/1932 (default of day=1 if it is unknown)

          1932 would be 1/1/1932 (default of day=1 and month=1 if unknown)

           

          As long as the defaults are known to users, they can work with them for searches and the like.  In my eyes, this would be easier both to build and to use than having three different date fields to search in.

           

          What do you think?

          • 2. Re: Searching for ranges of dates in different formats
            comment_1
              

            I'd suggest you enter the dates into three individual number fields - Day, Month and Year. Then define a calculation field with a Date result that forces a value of 1 for missing day and/or month. This way you will be able to distingush between real dates and artificial ones.

            • 3. Re: Searching for ranges of dates in different formats
              Brin
                

              Thanks guys. Pardon me if I'm being dense, but how does one then distinguish between a "forced" or "default" 1 and the occasions where the genuine full-date of an item actually is the first of a month ? or January ? Does such a 1 not actually show up in browse mode ?

              • 4. Re: Searching for ranges of dates in different formats
                comment_1
                  

                Brin wrote:

                how does one then distinguish between a "forced" or "default" 1


                That's eaxactly my point. A forced 1 would have no value in the Day field (or in the Day and Month fields).


                • 5. Re: Searching for ranges of dates in different formats
                  Brin
                     Right, I'll try that, thanks, will let you know.
                  • 6. Re: Searching for ranges of dates in different formats
                    comment_1
                      

                    Another point you should consider: this might work for you or not, depending on how you intend to build your searches. Searching the calculated date field for a specific date like 5/5/1866 will NOT find documents that have only partial data - though a document from the month of May, 1866 or from the year 1866 could well be relevant.

                     

                    An alternative would be to calculate TWO dates: a start date and an end date, thus enabling a 'range to range' search: for example searching for documents in the range of May - September, 1866, would also find all records with unspecified dates in 1866.

                    • 7. Re: Searching for ranges of dates in different formats
                      Brin
                         Sorry, I'm struggling. I can follow the example of building a Calculation Field in the Tutorial, but that's only an If formula and I can't work out how you'd express something like this:
                      If Day = 0 (is this how you'd put "missing" ?) then insert an "invisible 1" in Day field
                      and similarly for the Month field

                      Am I thinking about this the wrong way ?

                      I confess I am trying to run before I can walk.... this is all part of a project to select a suitable database for our archives prioject and one of the ingredients is, can I handle this on my own or do I need to get professional help to design what we need ?





                      • 8. Re: Searching for ranges of dates in different formats
                        comment_1
                          

                        Brin wrote:
                        how you'd express something like this:
                        If Day = 0 (is this how you'd put "missing" ?) then insert an "invisible 1" in Day field

                        You don't want an "invisible 1" in Day field - you want to use 1 in calculating the date when Day is empty. You could make the calculation  =

                         

                        Date (

                        Case ( IsEmpty ( Month ) ; 1 ; Month ) ;

                        Case ( IsEmpty ( Day ) ; 1 ; Day ) ;

                        Year 

                        )

                         

                         

                        or a bit more elegantly =

                         

                        Date (

                        Max ( 1 ; Month ) ;

                        Max ( 1 ; Day ) ;  

                        Year 

                        )

                         



                        • 9. Re: Searching for ranges of dates in different formats
                          Brin
                             That seems to work ! I don't pretend to understand the formula you gave me.... but it works on a number of dummy runs.... I invented a number of records with dates between 1947 and 1957, some of which had full dates and some year-only dates, ran a Find for all records featuring a certain place-name between 1900 and 1954, and it threw up the right results.
                          Thanks for your time ! If I knew how to allocate Kudos or Solved to you i would ! Maybe the moderator does it...
                          • 10. Re: Searching for ranges of dates in different formats
                            mrvodka
                               You can click the star on the right hand side to give a kudos. You can uncheck your own post as the accepted solution and then check the post that is ( again on the right hand side ).
                            • 11. Re: Searching for ranges of dates in different formats
                              Brin
                                 ah, I see how it all works now....