4 Replies Latest reply on Aug 2, 2010 11:48 PM by paolobkk

    How to delete the time from a timestamp and script a find of the last 2 weeks?

    paolobkk

      Title

      How to delete the time from a timestamp and script a find of the last 2 weeks?

      Post

      I'm using FMP11 Advanced on Windows 7 and I have a timestamp field (on creation) that I need to be a date field now. 

      (we realized that the creation time is not much important for this table in our database)

      To change the field is not a problem (from timestamp to date), but now I have many old records with date+time as the timestamp did in the past.

      I want to be able to make a script to search in this field (creation date on "calls" table) to find all the records (Customers table) over 14 days ago.

      (this date field is in a portal on the Customer page to record all the calls we make to every customer and the staff will be able to get the customers to call - last time called 14 days ago).

      Thanks!

        • 1. Re: How to delete the time from a timestamp and script a find of the last 2 weeks?
          sunmoonstar.13

          (1) Make a backup of your file first

          (2) Create the script below

          (3) Go to a layout based on the Calls table and Show All records

          (4) Run the script (it won't matter that the script runs on records where the date is already correct)

           

          Go to Record [First]
          Loop
              Set Field [Calls_table::Creation date ; Let ( date = GetAsDate ( Calls_table::Creation date ) ; date ) ]
              Go to Record [Next ; Exit after last]
          End Loop

           

          The text in blue needs to be edited to match your table and field names.

          Make sure you check Exit after Last when creating the Go to Record [ Next ] step.

          The calcualtion  ---  Let ( date = GetAsDate ( Calls_table::Creation date ) ; date )  ---   goes into the Calculated Result of the Set Field step.

           

          Hope this helps!

           

          Nick

           

           

           

          • 2. Re: How to delete the time from a timestamp and script a find of the last 2 weeks?
            ninja

            Howdy DudeCat,

            Check out the GetAsDate function.  You may want to simply add a new field to your table (datefield), translate the data from the timestamp into date form using the GetAsDate function, put it in the new field, then remove your timestamp field altogether.  This would be easy to script, and you could run it once and be done with it. (Practice on a copy, not the original!)

            To find records with dates earlier than a given date, use the "Less than" operator.  If you want all records earlier than July 12, 2010, use the find criteria "<7/12/2010" (without the quotes)

            Edit:  And Nick already showed you above how to script it... :)

            • 3. Re: How to delete the time from a timestamp and script a find of the last 2 weeks?
              philmodjunk

              Don't forget that you can also use Replace Field Contents to modify the value of all the fields in your found set without having to script a loop to do this. Either way, make sure you have a back up first so you can toss the file and start over with a new copy if you make a mistake and modify records in a manner you didn't want.

              • 4. Re: How to delete the time from a timestamp and script a find of the last 2 weeks?
                paolobkk

                Thank you guys!  The GetAsDate function is doing a good job, :)

                I've created a new calculated field in the "calls" table  with this calculation:

                (GetAsDate ( Calls_Table::Creation_Date ) + 14

                in this way it will show me the date when this customer has to be contacted by our staff.

                To make it more clear to our staff, I've made a formatting field with the rule to highlight that field when the "to_call_date" is equal or greater than the current date.

                I would like that field to be outside the "calls" portal, in the contact page, but when I do that, it shows me different values from that creation field (does the same with the new calculated field "cr_cate +14"). Sometimes the first record of calls, sometimes the last or anyone.

                After I get that field working I will make a button to filter and show me only the contacts with that date "equal or greater" than today's date.