1 2 Previous Next 16 Replies Latest reply on Oct 10, 2009 3:20 PM by marksealey

    How do I import dates in a TSV file into FMP10?

    marksealey

      Title

      How do I import dates in a TSV file into FMP10?

      Post

      I have an existing FMP 10 file with (two) Date fields.

       

      When I try to import a TSV file, in which the corresponding new date fields are in the format 'October 5', 'August 1' etc, they're not imported.

       

      (The dates have no year because they represent the yearly start and end dates of publishers' reading periods - and are the same for each year.)

       

      I can change these to, for example, '10/05', '08/01' etc. But a year would be meaningless.

       

      Anyone any ideas, please?

       

      How do I pre-format these fields for import?

       

      Or is there a way to hold dates as strings in FMP10, import them as strings and then convert them to date format?

       

      TIA! 

       

       

        • 1. Re: How do I import dates in a TSV file into FMP10?
          comment_1
            

          In Filemaker, 'October 5', 'August 1' etc. are text strings. A valid date MUST have a year in addition to day and month.

           

          You could add an arbitrary year to the source data and format the field to show only month and day - or split it into two values to be imported into two fields (preferably Number fields, since Filemaker does not understand "October" of itself).

          • 2. Re: How do I import dates in a TSV file into FMP10?
            marksealey
              

            Thanks, comment.

             

            I can easily have the Perl I have written to produce the TSV file convert all 'October's to '10's and all 'August's to '08's and add a year.

             

            What is the format for dates in FMP10, though?

             

            Is it really a string such as, '2009-10-01'; or held in a binary way?

             

            Will (date)strings like 2009-10-09 import?

             

            Is the format YYYY-MM-DD?

             

            I want to be able to perform calculations on the dates.

             

            I really don't want the years since I don't want to have to update them all every January 1st.

             

            Thanks again for any suggestions :smileyhappy: 

            • 3. Re: How do I import dates in a TSV file into FMP10?
              comment_1
                

              msealey wrote:
              What is the format for dates in FMP10, though?

              Each file has its own format, inherited from the short-date format of the OS where the file was created (or cloned).

              In addition, if a file is opened on a different system, it can use either it's own format or the current system format - see File > File Options… > Text.

               


              msealey wrote:
              I really don't want the years since I don't want to have to update them all every January 1st.

              You don't need to update them - you could just append any year, e.g. 0001 to all of them. However, I must ask what calculations do you intend to perform,  because the chosen year could affect them (e.g. being a leap year or not).

               



              • 4. Re: How do I import dates in a TSV file into FMP10?
                marksealey
                  

                comment,

                 

                Thanks - that's a helpful reply: of course; now I remember about FMP inheriting the OS' format of the date.

                 

                So, if I convert my text file's dates to YYYY-MM-DD and set the OS format to the same, shouldn't I be able to import them?

                 

                I can then change the OS back to whatever I want and FMP will respect that, can't I?

                 

                Calcs: this is a database of submissions to publishers who only read manuscripts between certain (published) dates every year - e.g. (on any and every date) between Sept 1 and Dec 31. Since it's the same for every year, if I enter 2009-09-01 and 2009-12-31 in my database and calculate if today's date is between those two, it'll not work in a year's time, e.g. on 2010-10-15.

                 

                Is there an easy buit-in FMP calc. to add 1 to the year every January? 

                • 5. Re: How do I import dates in a TSV file into FMP10?
                  comment_1
                    

                  msealey wrote:
                  Since it's the same for every year, if I enter 2009-09-01 and 2009-12-31 in my database and calculate if today's date is between those two, it'll not work in a year's time, e.g. on 2010-10-15.

                  It can work, if the calculation ignores the stored year and uses the current year instead. But it only goes to show that the straighforward solution here is to import the day and month into two separate Number fields.

                  • 6. Re: How do I import dates in a TSV file into FMP10?
                    marksealey
                      

                    So day as one number field; month as another?

                     

                    Leading zeroes?

                     

                    Year as a third, which I (somehow?) increment every January 1st?

                     

                    How do I then calculate time span to see if today is between, say, [10] [01] [2009] and [12] [31] [2009]?

                     


                    comment wrote:…
                    the straighforward solution here is to import the day and month into two separate Number fields.

                     

                     

                    • 7. Re: How do I import dates in a TSV file into FMP10?
                      comment_1
                        

                      msealey wrote:

                      So day as one number field; month as another?


                      Yes. Leading zeros are meaningless in numeric values.

                       

                       


                      msealey wrote:

                       

                      How do I then calculate time span to see if today is between, say, [10] [01] [2009] and [12] [31] [2009]?


                      Given these fields:

                       

                      • StartDay = 1

                      • StartMonth = 10

                      • EndDay = 31

                      • EndMonth = 12

                       

                      Let ( [
                      d = Get (CurrentDate ) ;
                      y = Year ( d ) ;
                      start = Date ( StartMonth ; StartDay ; y ) ;
                      end = Date ( EndMonth ; EndDay ; y )
                      ] ;
                      start ≤ d and d ≤ end
                      )

                       

                       returns True if today is between October 1 and December 31 of the current year.

                       

                      ---

                      Note that the calculation must be unstored in order to recalculate for the current date.

                       





                      • 8. Re: How do I import dates in a TSV file into FMP10?
                        marksealey
                          

                        Thanks again, comment!

                         

                        Is incrementing the Year just as simple?

                         

                        Your help much appreciated! 

                        • 9. Re: How do I import dates in a TSV file into FMP10?
                          comment_1
                             The calculation does not increment the year. Being unstored, it evaluates at every screen refresh - using whatever happens to be the current year at the moment. It's the system clock that takes care of incrementing the year.
                          • 10. Re: How do I import dates in a TSV file into FMP10?
                            comment_1
                               I should probably add the following caveat:

                            The above example is quite simple, because it assumes that the relevant period does not cross year boundaries. It would need to be smartened up in order to acommodate cases where the period starts on December 1 and ends on January 31, for example.
                            • 11. Re: How do I import dates in a TSV file into FMP10?
                              marksealey
                                

                              comment,

                               

                              Thanks again!

                               

                              I have a little prototype; I'm new to FMP calculations, so…

                               

                              :smileyhappy:

                               

                              Two quickies, if I may:

                               

                              1) How do I convert the '0' and '1' of the calculation result into 'No' and 'Yes'?

                               

                              2) Do I actually need to display a 'StartYear' and 'EndYear' in my layout: FMP seems to be making the calculation correctly without it?

                               

                              Thanks again! 

                              • 12. Re: How do I import dates in a TSV file into FMP10?
                                marksealey
                                  

                                Yes; that does happen. any ideas, please? As I say, this is my first foray into FMP calculation fields!

                                 

                                This is what's working for me now:

                                 

                                Let ( [

                                d = Get ( CurrentDate ) ;

                                y = Year (d);

                                start = Date (StartMonth ; StartDay ; y) ;

                                end = Date (EndMonth ; EndDay; y)  

                                ] ;

                                start  ≤ d and d  ≤ end

                                ) 

                                 


                                comment wrote:
                                …It would need to be smartened up in order to acommodate cases where the period starts on December 1 and ends on January 31, for example.

                                 

                                 


                                • 13. Re: How do I import dates in a TSV file into FMP10?
                                  comment_1
                                    

                                  OK, then let's make this more generic:

                                  Let ( [
                                  s = StartMonth + StartDay / 100 ;
                                  e = EndMonth + EndDay / 100 ;
                                  d = Month ( Get (CurrentDate) ) + Day ( Get (CurrentDate) ) / 100
                                  ] ;
                                  Case (
                                  s < e ;
                                  s ≤ d and d ≤ e ;
                                  d ≤ e or s ≤ d
                                  )
                                  )
                                  The result type should be Number. If you like, you can format the field to display as Yes/No.

                                   

                                  Note also that you can use the same formula in conditional formatting - so you could apply a different color to records that are currently "in session".

                                   

                                   

                                   


                                  msealey wrote:
                                  Do I actually need to display a 'StartYear' and 'EndYear' in my layout: FMP seems to be making the calculation correctly without it?

                                   


                                   
                                   I am not sure I understand what you're asking: you can display whatever you like (or need) on your layout. In any case, there is no 'StartYear' or 'EndYear' here.





                                  • 14. Re: How do I import dates in a TSV file into FMP10?
                                    marksealey
                                      

                                    Thanks, again, comment. I've got that working too. I see - I am using the year; but not displaying it; and not having to enter it anywhere. Correct?

                                     

                                    (A next step for me is to investigate and teach myself FMP calculations!)

                                     

                                    In the meantime…

                                     


                                    comment wrote:

                                    If you like, you can format the field to display as Yes/No.

                                     

                                    Note also that you can use the same formula in conditional formatting - so you could apply a different color to records that are currently "in session".

                                     

                                    How would I do that? 

                                    1 2 Previous Next