BTW: you set Error Capture on, but you don't do anything with any error. You sometimes use 2 dots in the date range and sometime 3 (though I don't think it makes any difference).
I have three basic debugging strategies I use when a scripted find fails:
Run it with the debugger enabled (Requires FileMaker Advanced)
Remove or disable the the Set Error Capture statement and run it again to see if I get any error dialogs that is supressed.
Immediately after running the script, select Modify Last Find to check and see what criteria the script actually used in performing the find.
Thanks Phil as always..
I followed your advice and I managed to get this message when the script enters find mode for $FYCurrent: The provided find criteria is not valid. Enter a valid request before proceeding.
And the date field in question contains the following range: 1/5/2010..30/4/2011, which seems good.
When hit enter, the error message pops up again, but when I add a third dot in the range (between the dates), it works. But when I change the date range calculation to have three dots instead of two, the same error occurs and if I remove the third dot, it works.
I'm not sure what else to think
I've always used three dots, though I've seen sample criteria here that used two.
Can you confirm that the two fields are of type date?
What do you see in the fields if you re-enable set error capture, run the script and select Modify Last Find?
Those two fields are outputting calculation results as text but in a date format and the calculation is producing the results intented. When I set the result to be date, I get a "?" for the second field. I should also note that the JOBS::date_service field is a valid date field.
Here are the steps
If ( Month ( date_current )>4 and Month (date_current ) ≤ 12; "1/5/" & year_current; "1/5/" & (year_current-1))
If ( Month ( date_current )>4 and Month (date_current ) ≤ 12; "30/4/" & (year_current+1); "30/4/" & (year_current))
When I reset the error capture, the field contains the following data when choose Modify Last find :
1/5/2010..30/4/2011. This is the range I was interested as per the calculations above.
When I perform find after checking the criteria in Modify Last Find, I get the error I mentioned above.
Whoa, what's the date format your system settings use. DDMMYYYY or MMDDYYYY?
Assuming DDMMYYYY, may 1 (1/5) comes after April 30, ( 30/4) and your date range is thus invalid.
If MMDDYYYY, then the second date is invalid as there is no month 30.
To keep things working for either format, I prefer to use the Date function when computing a date like this: Date ( month ; Day ; Year ) and set my return type as date.
Hey Phil, thanks for the suggestion. Just some background to make some sense.
As you've probably figured, this script is supposed to generate a report for a given fiscal year. This company's fiscal year begins on May 1 and ends on April 30 of the following year. And yes, the date format is DD/MM/YYYY.
So the JOBS::fy_date_begin_current's calculation is supposed to output "1/5/YYYY" where YYYY is the current year.
So if the current month is older than April and equal or less than December, the FY begin date is 5/1/ of the current year. But if the current month is January, February, March or April, the FY begin date is May 1 of last year. In other words, The third and fourth quarter of this company's fiscal year overlap or fall in the following year.
I'm gonna put your date formating suggestion to use and see what we come with. But In the meantime do you still see a problem with my calculations?
Nope, I missed the differing year values that make April 30, be April 30 of the following year. Do make sure that the date service field is of type date, however...
True and your Date function worked. I never paid attention to it before. I'm sure its gonna help with the find. Thanks a bunch again