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

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. 

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

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.
 
 

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

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

Organizer Copy.fmp12

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.

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

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, 20178 yr by comment

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 ) )

13 minutes ago, -dp- said:

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

 

Edited September 22, 20178 yr by doughemi

Duh. How embarrassing.

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