Scripting a query for a range of dates specified by parameter

[-dp-]

I’m trying to create an interface that will find all entries in the month specified in the script parameter. If the user selects the Sep button, I want the script to query a date field for “2017-09-01…2017-09-30”. This works correctly when I do it manually, and a case statement correctly builds this string in the variable $_range. But when I try to script the query as follows:

Enter Find Mode [Pause: Off]
Set Field [Calendar::calDate; $_range]
Perform Find [ ]

it consistently throws a 500 error, even though I’ve set no validation restrictions.
Set Field [Calendar::calDate; “\” & $_range & \””] does not resolve the problem.

Can anyone troubleshoot this, or suggest an entirely better method?

Thanks,
dp

[comment]

You're not showing us how the $_range variable is being defined, so there's very little we can say. We also need to know what date format your file is using. 

[Fitch]

There should not be quotes around the string, could that be it?

FYI, if you're looking for a particular month, you can set the Find criteria to simply month/year, or even just the month number if you only want the current year.

https://www.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Ffinding-ranges.html%23

[-dp-]

I didn’t know that I could leave the day out of the query. Thanks for the education.

The date format is yyyy-mm-dd. Here’s the whole script thus far.

=====
Set Variable [ $_year ; Value: Year (Get ( CurrentDate ) )].
/* I haven’t yet started working through what to do if the month is in the next calendar year. */

Set Variable [ $_range ; Value: 
Case
( 
     Get ( ScriptParameter ) = "Jan"; $_year & "-01";
     Get ( ScriptParameter ) = "Feb"; $_year & "-02";
     Get ( ScriptParameter ) = "Mar"; $_year & "-03";
     Get ( ScriptParameter ) = "Apr"; $_year & "-04";
     Get ( ScriptParameter ) = "May"; $_year & "-05";
     Get ( ScriptParameter ) = "Jun"; $_year & "-06";
     Get ( ScriptParameter ) = "Jul"; $_year & "-07";
     Get ( ScriptParameter ) = "Aug"; $_year & "-08";
     Get ( ScriptParameter ) = "Sep"; $_year & "-09";
     Get ( ScriptParameter ) = "Oct"; $_year & "-10";
     Get ( ScriptParameter ) = "Nov"; $_year & "-11";
     Get ( ScriptParameter ) = "Dec"; $_year & "-12";
     ""
 )]
 
 Enter Find Mode [ Pause: Off ]
 Set Field [ Calendar::calDate; $_range ]
 Perform Find [ ]
 
 =====
 
 When I run it through the debugger, I can see that it’s building the variables correctly, but it’s not populating the calDate field in the Set Field step. I tried using
 Set Field [ Calendar::calDate; “\” & $_range & \”” ]
 but it returned the same error. 
 
 The error is 500, but I don’t know if validation fails because it doesn’t like what I’m unsuccessfully *trying* to enter, or if it’s not entering anything, which therefore fails validation.
 
 

[comment]

Could you post a copy of your file with only the date field, the buttons and the script?

[-dp-]

See attached. I've deleted all but the relevant layout and script.Thanks. 

Organizer Copy.fmp12

[comment]

First thing, your file's date format is MM/DD/YYYY. It is set to always use the current system's setting. Are you sure your OS short date format is YYYY-MM-DD? Because if it isn't, that would explain the described behavior.

[-dp-]

My short date format is set to YYYY-MM-DD. I've attached a screenshot.

[comment]

Okay, I have managed to reproduce the problem. It's a bug.

1. How to work around the bug:
Use Insert Calculated Result[] instead of Set Field[].

2. How to avoid the bug:
Save a copy of your file as clone (no records) and import the records into the clone. The new file's date format will be YYYY-MM-DD and your script will work.

3. How to avoid the bug and follow the best practice of not having your script depend on any date format:
Make your script do:

Enter Find Mode []
Set Field [ Calendar::calDate; Date ( Get (ScriptParameter) ; 1 ; Year ( Get (CurrentDate) ) ) & ".." & Date ( Get (ScriptParameter) + 1 ; 0 ; Year ( Get (CurrentDate) ) ) ]
Perform Find []

where script parameter is a number between 1 and 12 (there's really no good reason to send the month name as text, then spend 12 lines of code converting it to a number).

 

 

Edited September 22, 2017 by comment

[-dp-]

Thank you very much for troubleshooting this, and for suggesting so efficient a correction to my code verbosity. I implemented all of your suggestions. I created a clone and imported. I changed Set Field to Insert Calculated Result. I changed the calculation to the one you suggested. It's still broken, but we're making progress. I can see what's happening in the debugger. It's entering only the first date in the calculation. For example, if I click on March, it enters "2017-03-01...?" So, it's not resolving the second half of the calculation. Here's a copy/paste of exactly what I entered.

Date ( Get ( ScriptParameter ) ; 1 ; Year ( Get ( CurrentDate ) ) ) & "..." & Date ( Get ( ScriptParameter ) + 1 ; 0 ; Get ( CurrentDate ) )

[doughemi]

13 minutes ago, -dp- said:

Date ( Get ( ScriptParameter ) ; 1 ; Year ( Get ( CurrentDate ) ) ) & "..." & Date ( Get ( ScriptParameter ) + 1 ; 0 ; Year(Get ( CurrentDate ) ))

 

Edited September 22, 2017 by doughemi

[-dp-]

Duh. How embarrassing.

But with that correction, it works perfectly. Thanks very much to everyone who helped.