3 Replies Latest reply on Jun 14, 2011 9:49 AM by lmflora

    Using a date calculation as a match field



      Using a date calculation as a match field



      I have a problem.  My data comes in the form of ordinal date and year.  I created a "calendar file" where i can then reconstruct the date (mm/dd/yyyy) using the Date ( month ; day ; year ) function by pulling in the month and day from the calendar file using the ordinal date as the match field. I need to use the resulting date (mm/dd/yyyy) as a match field in other files. 

      I find that i have to export the whole database into an excel file and then bring it back into FM, this makes the calculated date field just a date field and not a calculation field.  When I try to match on the calculated date field, i get an "index missing" error code.  Isn't there some way that i can take the calculated date field and turn it into a plain old date field without having to go in and out of excel?  or is there someother way i can get arround this time consuming step.



        • 1. Re: Using a date calculation as a match field

          When a calculation field refers to a field in another record or table, the field cannot be stored and cannot have a permanent index. This keeps you from using it on the "many" side of a relationship.

          If the field is never edited after initial data entry, you could use an auto-enter calculation to enter the date into a date field and now it would be an indexed field that will work for you.

          If this field is subject to any chance it will be edited, then best bet is to write a script using set Field to update the field using data from the related table of dates. A script trigger on the data field can use OnObjectSave to perform this script.

          It should also be possible to replace this table of related dates with a calculation that converts the ordinal number into date. That could also eliminate the "unstored calculation" challenge here.

          • 2. Re: Using a date calculation as a match field

            Come to think of it, if an ordinal number of 1 represents January 1 of the specified year, this calculation would covert it into a date:

            Date ( 1; ordinalNumberField ; year field )

            • 3. Re: Using a date calculation as a match field

              Thanks PhilModJunk!

              the Date(1;ordinal fied;year field) idea works awesome!!

              Thank you! Thank You! Thank You!

              Have a great Week!