4 Replies Latest reply on Nov 27, 2016 11:04 AM by champino

    Date Range Scripted


      In FMP.12 I created 2 date fields with global storage (Start Date, End Date).  I created a blank layout that prompts for input in these 2 fields.  The script goes to Find mode in an existing report layout.  The date range is specified with the following line:


      SetField [<database>::DATE[ <database>::Start Date & "..." & <database>::End Date ]]


      This yields the requested results in FMP.12, on a copy of the client's database.  The client's database is on FMP.13, so I recreated the fields, layout, & script in FMP.13, but receive an error:


      "The provided find criteria is not valid.  Enter a valid request before proceeding."


      I've tried several modifications to the script, but it seems the DATE field, rejects the references to "Start Date" and "End Date" regardless of what I try.

        • 1. Re: Date Range Scripted

          You've got the set field step set up incorrectly and that's why there's an extra set of [ ] that should not be in evidence in the step you show here.


          It should look like this:


          Set Field [<database>::DATE ; <database>::Start Date & "..." & <database>::End Date ]


          To set up set field, Click the target field option and select the field you want as the target. This field's name will appear to the left of the semi-colon shown above. Do not click "specify" inside the specify field dialog. that button is for setting up a reference to a particular repetition of a repeating field and that's what you did here as the reference to a repetition is shown in the inner pair of brackets.


          Instead Select the calculated result option and enter your date range expression there.

          • 2. Re: Date Range Scripted

            Philmodjunk basically answered it for you.  You might also want to consider what to do if they leave one of the fields blank.  You could have an IF that Exists the script if either of blank.  Or you could have it search everything before or after the date that is filled out and ignore the empty one. 

            • 3. Re: Date Range Scripted

              That worked!  I found out this morning that the script actually wasn't working in my FMP12, I was running the script against a previous find where I had input the date range manually.  I'm now able to run the script successfully on my FMP12 copy & the client's FMP13 database.  Thank you!

              • 4. Re: Date Range Scripted

                This is true.  I based my script on a KB article:

                How to write a script that finds a range of dates in FileMaker Pro | FileMaker

                The article includes If statements to check for blank fields and start date later than end date.  I haven't included the If statements, but may find that I need to.