1 2 Previous Next 16 Replies Latest reply on Apr 10, 2012 3:14 PM by nasho23

    Convert Text Date to Date for Age Calculation

    nasho23

      Title

      Convert Text Date to Date for Age Calculation

      Post

       I am using FMPro 11 Adv with Win 7 64bit. I have set up an Age Test Table to perform Calculation of Age, based on DeathDate - BirthDate, for various scenarios, as shown in Age Test Table below. With outside help, I have solved the basic Calculation Coding, but I am left with a Problem of Calculating on Partial DeathDates. DeathDates, in my case have to be in a Text Field, because the source material I have to use from Descendants, does not always provide the Full Date, so it has to be entered as Short Dates.

      Can anyone offer assistance with further Coding required to solve this part of the Calculation, as I am not familiar with Coding. I do not even know what Programming Language is used in Filemaker.

      Copy of Coding so far to achieve results for Full Dates and Blank Fields in Records, as seen in below copy of Age Test Table.

       Let (

       [

       DateX = Get ( CurrentDate );

       Date1 = Table::BirthDate;

       Date2 = Table::DeathDate;

       Date2 = Case ( Date2 = ""; DateX; Date2 );

       

       Year1 = Year ( Date1 );

       Year2 = Year ( Date2 );

       

       Day1 = DayOfYear ( Date1 );

       Day2 = DayOfYear ( Date2 );

       

       Adjustment = Case ( Day2 < Day1; ( -1 ); 0 ) ];

       

       Case ( Date1 <> ""; ( Year2 - Year1 + Adjustment ); "" )

       

       )

       

       

       

       

       

      Age_Test_Table.jpg

        • 1. Re: Convert Text Date to Date for Age Calculation
          davidanders

          Google "age calculation site:filemaker.com"
          https://www.google.com/search?q=age+calculation+site%3Afilemaker.com

          Google "text to date site:filemaker.com"
          https://www.google.com/search?q=text+to+date+site%3Afilemaker.com

          A Filemaker Date field is the number of seconds since Jan 01, 1900

          Calculate the Age of a Person in Years, Months and Days
          http://help.filemaker.com/app/answers/detail/a_id/5532/related/1

          Calculation for Elapsed Months
          http://help.filemaker.com/app/answers/detail/a_id/5139/~/calculation-for-elapsed-months

          • 2. Re: Convert Text Date to Date for Age Calculation
            philmodjunk

            A Filemaker Date field is the number of seconds since Jan 01, 1900

            Um, no. It's the number of days since 12/31/0000 Wink

            I'm not sure what you want to do with the incomplete dates. If a date is just a year, what date should that be? 1/1/Year? or some other date?

            • 3. Re: Convert Text Date to Date for Age Calculation
              nasho23

               Thank You David,

              I have reviewed the Links that you have provided, but they do not provide me (an absolute novice) with a solution to my problem of,  how to add suitable Code that will convert the Text of Short Dates to a Date Format that can be calculated to privide the Age of Tom and Rick, to the nearest year.

              Perhaps I did not indicate the Field Properties clearly - BirthDate = Date, DeathDate = Text, and Age = Calculation with result Number.

              a) Is it at all possible to convert these "Short Dates" that are in a Text Field (DeathDate), to a Date Format, that can be calculated to produce an Age Number.

              b) If Yes, can someone please write this Code for me and show me where to place it within the Code that I currently have, which now produces the Age Number, when both Fields have a "Full Date" shown in a BirthDate (Date) and DeathDate (Text) Fields.

              c) What Programming Language is used by Filemaker Pro 11 Adv, so that I may study it for the future.

              • 4. Re: Convert Text Date to Date for Age Calculation
                nasho23

                 Thank You PhilModJunk,

                I think if you study the TestAgeRota Results Table I provided in my original Post, I think what I am needing to Calculate is very evident, by the "?" marks against Tom and Rick, in the Age Field. The "?" is produced by the Code I have shown, because the DeathDate Field has to be a Text Field in my case, as explained in my Post.

                • 5. Re: Convert Text Date to Date for Age Calculation
                  philmodjunk

                  It's not at all evident to me and I need to be sure before suggesting a way to convert your text into dates.

                  Take the first row of data: You have a birth date of 12/12/1923 and a death date of ../../1993

                  that death date could be interpreted as 1/1/1993 in order to get a date that can be used to compute an age. I'd guess that's what you want to do but would prefer to be sure. The calculated age can vary by 1 year depending on the month and day values that you use.

                  • 6. Re: Convert Text Date to Date for Age Calculation
                    nasho23

                     Thank you PhilModJunk. You are pretty much spot on with your interpretation. If the calculated Age is out by one year, so be it, that is much better than it being Blank, as they are now. If a Descendant of a Rat of Tobruk were to complain to me about the error in the Age of their Ancestor, well they would need to find out and supply me with the Full Date of His Death, so that His Record could be corrected. There is a Feedback Form on the Web Site for them to do this for me.

                    Now that you are clear on this, can you please produce the Code for me and show me where it needs to be included in the current code.

                    Also, what is the Programming Language?

                    • 7. Re: Convert Text Date to Date for Age Calculation
                      philmodjunk

                      What you have is a calculated expression. It's not a programming language. FileMaker does have scripting and it's a "language" unique to FileMaker, but it's not in use here.

                      I'd set up this calculation for each date field in separate calculation fields that return date data types:

                      Let ( [ dstring = YourTable::YourDateField ;
                                 start = Position ( dstring ; "/" ; 1 ;1 ) ;
                                 end = Position ( dstring ; ";" ; 1 ; 2 ) ;
                                 d = Max ( getasnumber (  Left ( dstring ; start ) ; 1 ) ; // max supplies a 1 when month is missing
                                 m = max ( getasnumber ( Middle ( dstring ; start ; end - start ) ) ; 1 ) ;
                                 Ya =  getasnumber ( Right ( dstring ; length ( dstring ) ; end ) ) ;
                                 Y = If ( Ya < 1900 ; Year ( Get ( CurrentDate ) ) ; Ya )
                                ];
                                Date ( m ; d ; y )
                              )

                      Once you get real date fields, you can use them in this expression to compute the age:

                      Year ( DeathDate ) - Year ( BirthDate ) - ( Date ( Day ( BirthDate ) ; Month ( BirthDate ) ; Year ( DeathDate ) ) < DeatDate )

                      Any calculations that use Get ( CurrentDate ) should be unstored or they will not update as the system clock's date changes.

                      Note: I've banged this out late at night and didn't take time to test it. Check it out and if it doesn't quite work, let me know.

                      • 8. Re: Convert Text Date to Date for Age Calculation
                        nasho23

                         Thank you, PhilModJunk. I do not expect you to bang your head against the wall over this and I appreciate your efforts and assistance with this.

                        For this Test, I changed the DeathDate Field to Deceased, this now being the Text Field that holds the Short Dates and will only be used to test your Code. When this Text Date to Date conversion is solved and results are in the Deceased Field, I hope I can add the Age Field to the Table and then run the previous Code to establish the Age Date.

                        In your Code, I replaced "Your Table" with "TEST AGE ROTA" and "Your Date Field" with "Deceased" - Pressed OK - Line 4, Semi Colon before 1 was highlighted and Message "Too many Parameters in this Function" - I removed the Semi Colon - Pressed OK - Line 4, 1 highlighted and Message " An Operator required here"- I cahnged it to = 1 - Pressed OK - Line 5, "m" highlighted and Message "Specified Field not found" - I did not proceed past this point, as I was lost.

                        Do not rush, Mate.

                        • 9. Re: Convert Text Date to Date for Age Calculation
                          philmodjunk

                          Nothing like typing late at night to introduce typos.

                          This works. I've tested this one:

                          Let ( [ dstring = Deceased ;
                                     start = Position ( dstring ; "/" ; 1 ;1 ) ;
                                     end = Position ( dstring ; "/" ; 1 ; 2 ) ;
                                     d = Max ( GetAsNumber (  Left ( dstring ; start ) ) ; 1 ) ; // max supplies a 1 when month is missing
                                     m = Max ( GetAsNumber ( Middle ( dstring ; start ; end - start ) ) ; 1 ) ;
                                     Ya =  GetAsNumber ( Right ( dstring ; Length ( dstring ) - end ) ) ;
                                     Y = If ( Ya < 1900 ; Year ( Get ( CurrentDate ) ) ; Ya )
                                    ];
                                    Date ( m ; d ; y )
                                  )

                          • 10. Re: Convert Text Date to Date for Age Calculation
                            nasho23

                             Good Morning PhilModJunk. With my Table (TEST AGE ROTA) now set up with these Fields - Name - BirthDate - Deceased - DeathDate - Age.

                            First of all, in your Code, I changed the Date Format from (m:d;y) to (d;m;y) for Aust Date.

                            I then ran your Code and it placed Numbers in the DeathDate Field as I expected it would.

                            I then retested the Calculation for the Age Field and it worked as it should, with Dates converted to numbers, for all types of Dates Entered into the Deceased Field and Blank Fields. Great stuff.

                            I then reset the Age Field as Calculation and placed your Code first, followed by the my Age Calculation Code I had previously used, expecting it to run through and perform both Code operations, one after the other. However, when I pressed OK, all of the Age Calculation Code was highlighted and a Message was saying "An Operator expected here". Stumped again!!

                            Can you please review all the Code, to determine, if with corrections, it will perform both operations together, as I reguire.

                            The basic logic, as I see it is: Convert Short Dates (Text) entered in the Deceased Field, to Numbers stored in the DeathDate Field (Number) and then using the BirthDate and DeathDate Fields, calculate the Age and place the result in the Age Field (Number) and ignore BirthDate Blank or BirthDate + DeathDate Blank. If only DeathDate Blank, calculate Age to CurrentDate.

                            Let ( [ dstring = Deceased ;
                                       start = Position ( dstring ; "/" ; 1 ;1 ) ;
                                       end = Position ( dstring ; "/" ; 1 ; 2 ) ;
                                       d = Max ( GetAsNumber (  Left ( dstring ; start ) ) ; 1 ) ; // max supplies a 1 when month is missing
                                       m = Max ( GetAsNumber ( Middle ( dstring ; start ; end - start ) ) ; 1 ) ;
                                       Ya =  GetAsNumber ( Right ( dstring ; Length ( dstring ) - end ) ) ;
                                       Y = If ( Ya < 1900 ; Year ( Get ( CurrentDate ) ) ; Ya )
                                      ];
                                      Date ( d ; m ; y )
                                    )


                             Let (
                             [
                             DateX = Get ( CurrentDate );
                             Date1 = TEST AGE ROTA::BirthDate;
                             Date2 = TEST AGE ROTA::DeathDate;
                             Date2 = Case ( Date2 = ""; DateX; Date2 );
                             
                             Year1 = Year ( Date1 );
                             Year2 = Year ( Date2 );
                             
                             Day1 = DayOfYear ( Date1 );
                             Day2 = DayOfYear ( Date2 );
                             
                             Adjustment = Case ( Day2 < Day1; ( -1 ); 0 ) ];
                             
                             Case ( Date1 <> ""; ( Year2 - Year1 + Adjustment ); "" )
                             
                             )

                            Regards Owen Carlton

                             

                             

                             

                             

                             

                            • 11. Re: Convert Text Date to Date for Age Calculation
                              philmodjunk

                              Date (month ; day ; year )  is a built in function here. You cannot change the order of the parameters. But once you have a date value returned in your field, you can change the data format on the inspector's data tab to show the specific date format you need if it does not automatically display in the desired format. (Your system settings may already be all you need to get the desired date format.)

                              • 12. Re: Convert Text Date to Date for Age Calculation
                                nasho23

                                 Thank you PhilModJunk, I have readjusted the mdy Format Line of your Code.

                                What are your thoughts on the remainder of my Post??

                                 

                                • 13. Re: Convert Text Date to Date for Age Calculation
                                  philmodjunk

                                  To quote from a previous post:

                                  Once you get real date fields, you can use them in this expression to compute the age:

                                  Year ( DeathDate ) - Year ( BirthDate ) - ( Date ( Day ( BirthDate ) ; Month ( BirthDate ) ; Year ( DeathDate ) ) < DeathDate )

                                  • 14. Re: Convert Text Date to Date for Age Calculation
                                    nasho23

                                     Thank you PhilModJunk, I hope you had a good Easter.

                                    Please refer to my April 7 Post.

                                    Are you saying I have to run two seperate pieces of Code to: 1) Convert Text Date to a Number, and then: 2) Perform Age Calculation.

                                    I was hoping that it would be possible to combine both pieces of Code into one, in some way, so that both DeathDate and Age are created at the same time, upon entry and/or at Login, instead of having to perform two seperate operations.

                                    Are you able to put this together for me, please.

                                    Thank You,

                                    Owen Carlton

                                    1 2 Previous Next