4 Replies Latest reply on Jul 13, 2011 2:12 PM by philmodjunk

    Using a varible to perform find for a date

    flamecoder

      Title

      Using a varible to perform find for a date

      Post

      I want to create a script that will ask what month to run a report on then perform a find. This is how I'm going about it so far...

      Go to Layout ["Monthly Insignias"]

      Set Variable [$YearDate; Value:Year (Get (CurrentDate))]

      Show Custom Dialog ["Monthly Report"; Insignia::Report :Month]

      //Custom Dialog collects the number of the month to do find on

      Set Variable [$MonthDate; Value:Insignia::ReportMonth]

      Set Variable [$ReportDate; Value:$MonthDate & "/*/" & $YearDate]

      Perform Find []

      //The Perform Find is on a Date Out which is a date field. Trying to put the variable in results in a dialog box saying it needs to be in the formate 12/25/2003

      How can I select a month and perform a find for every record with a date out for that month?

        • 1. Re: Using a varible to perform find for a date
          philmodjunk

          You might want to check the value of $ReportDate with the data viewer (Filemaker Advanced) or by inserting a Show Custom Dialog step to display it's value (FileMaker Pro).

          That might reveal a problem with the expression that isn't obvious when you just look at the calculation expression.

          It's also possible to construct a find request for a specified month without using a wild card:

          Set Variable [$ReportDate ; Value: Date ($MonthDate ; 1 ; $YearDate ) & "..." & Date ($MonthDate + 1 ; 0 ; $YearDate ) ]

          The second Date function returns the date of the last day of the month so this finds your dates on a date range from day 1 to day last (28, 29, 30 or 31) of the specified month.

          • 2. Re: Using a varible to perform find for a date
            flamecoder

            I can do a simple test...

            set Variable [$SearchForDate; Value;"6/*/2011"]

            Perform Find[]

            Try to put that variable in a Perform Find function of a field which has a class of date and the Edit Find Request won't take it.

            How would you perform a find for a date you need to compose on the fly and pass it to Perform Find function????

            • 3. Re: Using a varible to perform find for a date
              GeoffreyMartin

              I set a variable $search = Month (Get (Currentdate)) & "/1/2011 ... " & Get (CurrentDate).  Then put this in the search string for the date field of the find.  Works like a charm for finding the current data from the current month to now.

              • 4. Re: Using a varible to perform find for a date
                philmodjunk

                I prefer not use variable in a restored find request like this. (Which would read as Perform Find[Restore] BTS, not Perform Find[] like you have posted here.)

                Instead I set up scripted finds like this so that they are easier to read when I examine  the script in the future:

                enter find mode[] //clear the pause check box
                Set Field [YourTable::YourField ; $SearchForDate]
                Peroform Find [] //no stored criteria here

                And instead of $SearchForDate you can often put calculation expressions as long as the calculation only refers to functions, constants, variables and global fields (Fields that are not global will evaluate as being empty when in find mode.)