3 Replies Latest reply on Sep 28, 2010 9:19 AM by philmodjunk

    confusion on search, intended to find recent changes

    StevenBland

      Title

      confusion on search, intended to find recent changes

      Post

      The record have a field, last edit date, which is a timestamp that has the last edited timestamp of that record. As a search, I wanted to see which records have changed in the last 60 minutes, so a simple script

      Enter Find Mode []

      Insert Calculated Result[ Select; Planner:: Last Edit Date[">"&Get( Current Timestamp ) - 3600]]

      Perform Find []

      should return those records that have changes in the last 60 minutes? or am I missing something simple?

      it always seems to be an empty set, even with obvious records with a timestamp in the last 60 minutes

        • 1. Re: confusion on search, intended to find recent changes
          philmodjunk

          The problem is in the extra brackets in the above expression:

          Insert Calculated Result[ Select; Planner:: Last Edit Date[">"&Get( Current Timestamp ) - 3600]]

          Those should not be there. They indicate that you've clicked the first specify button (there are two) and entered ">"&Get( Current Timestamp ) - 3600 into the repetition box instead of clicking the lower specify button and entering this expression there.

          Using set field instead of Insert Calculated result would also be a better option here:

          Set Field [Planner:: Last Edit Date ; ">" & Get( Current Timestamp ) - 3600]

          • 2. Re: confusion on search, intended to find recent changes
            StevenBland

            Thanks Phil

            But I only get two specify buttons when I go back to edit the script - and if I do not create the script the way I did, I get an error about the date format. 'The field is defined to contain a valid timestamp in the range  of years 1 to 4000..." - this is from the find request dialog when it only has one specify button.

            Also there is no way in the UI to delete to the two brackets above, they do not appear in any editable fields, only in a locked UI summary field. Certainly there must be an easy way to leverage the timestamp field in a simple search to show the records edited over a specified period of time. Numerous web searches show ways of constructing a pretty complicated ( for what is needed ) script based on dates, but if anything a timestamp should be easier. 

            Your comment about the set field command is a little confusing, would that not change data? I am just going for a search.

            • 3. Re: confusion on search, intended to find recent changes
              philmodjunk

              Let's back up and add a bit more detail.

              Your script should look like this:

              Enter Find Mode[]
              Set Field [Planner::Last Edit Date ; ">" & Get( Current Timestamp ) - 3600]
              Perform Find[]

              1. When you add the Set Field Step, you'll see two specify buttons at the bottom of the edit script dialog:
              2. Click the first button "Specify target field" and select Last Edit Date from Planner to get the first parameter. Do not enter anything in the Repetition box at the bottom and do not click the specify button next to it. After selecting the field, click OK to dismiss the specify field dialog.
              3. Click the second specify button "Calculated result", and type in "> " & Get ( Current Timestamp ) - 3600. Click OK to dismiss the specify calculation dialog.
              4. FileMaker will add the ; for you.

               

              I don't recommend you use Insert Calculated Result for this, but you can if you want to. This step has the same two specify buttons and you'd use them the same way.