1 2 Previous Next 17 Replies Latest reply on Dec 13, 2010 2:51 PM by Mitch

    Full and Part Dates

    Mitch

      Title

      Full and Part Dates

      Post

      Hi,

      I have a portal that keeps a basic timeline.  It has two fields, "Date" and "Details'.  

      Unfortunately, because of the type of data I need to keep some dates are not complete.  Example, one entry may have a full date of: 5/1/1888.  Another entry may one be a part date: 5/1888; being for May 1888. 

      I have changed the field to a text field so the data can be entered.  However, it can not be sorted as a dates.  

      Is there any method of being able to extract a date out of text can convert to a number of formats. Or a solution for part dates to be sorted along with full dates...?

      Mitch

        • 1. Re: Full and Part Dates
          philmodjunk

          A key question if you are going to sort dates such as 5/1888, 5/20/1888 and 5/3/1888, where would the partial date be placed? One option would be to treat 5/1888 as 5/1/1888.

          Let ( divider = Position ( dateText ; "/" ; 1 ; 1 ) ;
                  If ( PatternCount (dateText ; "/" ) = 1 ; Date ( Left ( dateText ; divider - 1 ) ; 1 ; Right ( dateText ;  Length ( dateText ) - divider ) ) ; GetAsDate ( dateText ) ) )

          Where dateText is the name of your text field.

          • 2. Re: Full and Part Dates
            Mitch

            I can see how that works.  I would prefer not to substitute a figure.  But may I may have too...

            My intention would be to allow user to enter what date data they have and display as entered.  However, sort the entries a a date...

            Could there be a calculation that allows the user to place a date into a text field and the calculation converts to a date for sorting purposes.  Yet displays exactly what has been entered (I have used English Dates - DD/MM/YYYY):

            Entered: 3/5/1888 Converted to and sorted by: 03/05/1888 Displayed in field: 03/05/1888

            Entered: 5/1888 Converted to and sorted by: 01/05/1888 Displayed in field: 5/1888

            Entered: 1888 Converted to and sorted by: 01/01/1888 Displayed in field: 1888

            Obviously there would have to be some fields created to run the calculations and sort..?

            • 3. Re: Full and Part Dates
              sunmoonstar.13

              You may want to try a custom function for this sort of thing, something like this one:

               

              http://www.briandunning.com/cf/373

               

              Nick

               

              • 4. Re: Full and Part Dates
                Mitch

                Nick,

                I actually had a look at that custom function previously and plan to use it in another part of my database.    The one is quesiton will only have three formats entered:

                DD/MM/YYY

                MM/YYYY

                YYYY

                The custom function does not cover YYYY.  

                So, I have played with the solution from Phil and have created a 'DateText' field and a 'DateCalc' field.  'DateText' is entered in either of the DD/MM/YYYY or MM/YYYY format and is displayed in the portal, 'DataCalc' is the calculation from Phil. The portal is sorted according to 'DateCal', then by 'TimeStampAdded' to cover two dates the same.  Works really well...  

                I just need to work out how to add to the calculation in 'DateCalc' for also converting YYYY to:    01/01/YYYY

                • 5. Re: Full and Part Dates
                  philmodjunk

                  Could there be a calculation that allows the user to place a date into a text field and the calculation converts to a date for sorting purposes.  Yet displays exactly what has been entered (I have used English Dates - DD/MM/YYYY):

                  Mitch,

                  My example does exactly that, though I used US MMDDYYYY format in my example, it works for DDMMYYYY format as well though the date function will insist on MM ; DD ; YYYY order in it's parameters.

                  DateText is the field where the user enters the date and you can keep it for display purposes.

                  Your calculation field then computes a true date value and you can use it for sorting records by date. (The field you use to sort records need not be visible on the layout.)

                   

                  • 6. Re: Full and Part Dates
                    Mitch

                    Phil,

                    I have a few questions on this one.  

                    1. Is there any way that a date of only YYYY can be added and it would also be sorted accordingly.

                    2. Can the function be changed to accept date entered as DD/MM/YYYY

                    Mitch

                    • 7. Re: Full and Part Dates
                      philmodjunk

                      It's not necessarily a function as FileMaker defines it, though you could certainly use it that way.

                      The expression, as written, will work identically for DDMMYYYY systems as it would for MMDDYYYY systems as the input text is in MM/YYYY format in either case. We just have to add an extra section for handling YYYY data.

                      Let ( divider = Position ( dateText ; "/" ; 1 ; 1 ) ;
                              Case ( PatternCount (dateText ; "/" ) = 0 ; Date ( 1 ; 1 ; dateText ) ;
                                         PatternCount (dateText ; "/" ) = 1 ; Date ( Left ( dateText ; divider - 1 ) ; 1 ; Right ( dateText ;  Length ( dateText ) - divider ) ) ; 
                                         GetAsDate ( dateText ) 
                                        ) // case
                               ) // Let

                      • 8. Re: Full and Part Dates
                        Mitch

                        Thanks Phil,

                        I have the date data entered into a Text Field (Timeline_Date) and the Calculation you have written in a Date Filed (Date_Calc).  The record is sorted according to the Date_Calc which works as you have said.  However the Timeline_Date is the field that is displayed.  

                        Should I be changing the Timeline_Date to a date field to accept actually dates, or keep it as a text field?

                        If so, is Filemaker able to allow for entering DD/MM/YYYY in a Date Feied, use your calculation to sort, and then display the english date....?  

                        After looking in the help menu I am starting to think the Filemaker only likes US formatted dates....

                        • 9. Re: Full and Part Dates
                          philmodjunk

                          FileMaker can use either format. There are system settings on your system that FileMaker can use to determine whether dates are displayed as DDMMYYYY or MMDDYYYY. This is strictly a difference in how the data is displayed, not in how it is stored internally where all dates are simply stored as a single integer number counting the number of days since an early arbitrary date.

                          Should I be changing the Timeline_Date to a date field to accept actually dates, or keep it as a text field?

                          It depends on what you need to do with your current data. You can keep it as a text field with partial dates and so users can continue to enter partial dates, or you can convert your calculation field to a date field type--which will retain the calculated values and use it to replace your current text field.

                          If you convert it to a date field, FileMaker can be configured to display data as DDMMYYYY and to interpret new dates that are typed in as being in this format.

                          • 10. Re: Full and Part Dates
                            Mitch

                            Ok.  Thanks Phil.

                            I will keep it as a text field so partial dates can be entered... that is the whole idea I suppose.  Altering the settings does format that Dale_Calc into a English Date format.   But I wish to keep that invisible...

                            I was just trying to find a way that the partial and full date data entered as text can be entered as DD/MM/YYYY or MM/YYYY or YYYYY.   Our English friends may just have to get used to inputting US Formatted dates.....

                            • 11. Re: Full and Part Dates
                              Mitch

                              Further to this, 

                              Is there any method in which to extract parts of the dates, whether full dates or partial and placing each part into a separate field?  

                              Example:

                              For a date given as MM/YYYY, take the MM and place it into a 'Month' field and the YYYY placed into a 'Year' field.

                              The same for a date given as MM/DD/YYYY, placing each value into three separate fields, Month, Day and Year.

                              Mitch

                              • 12. Re: Full and Part Dates
                                philmodjunk

                                The same type of text functions can be used to extract these values and put them into number fields.

                                A "month" field calculation, would look like this:

                                Left ( dateText ; Position ( dateText ; "/" ; 1 ; 1 ) - 1 )

                                Year:

                                Right ( dateText ; 4 )

                                Day gets more complicated as it may or may not be present.

                                Let ( [ d1 = Position ( datetext ; "/" ; 1 ; 1 ) ; d2 = Position ( datetext ; "/" ; 1 ; 2 ) ] ; 
                                         If ( PatternCount ( datetext ; "/" ) = 2 ; Middle ( datetext ; ( d1 + 1 ) ; d2 - d1 -1 ) ; "" ) 
                                       )

                                These can be calculation fields or number fields with auto-entered calculations.

                                • 13. Re: Full and Part Dates
                                  Mitch

                                  In fact, after I changed the code, the only data that will always be present if the YYYY.  

                                  Both the DD and MM values may not be present 

                                  • 14. Re: Full and Part Dates
                                    philmodjunk

                                    Then you will need a similar if function test for day as well.

                                    1 2 Previous Next