Script find returns error: 500 regardless of date format

[ehwalker]

I'm running a script that should find a set of records within a specific date range. The date range is contained within a variable '$DateRange' which is then entered from the 'Perform Find' script step. Regardless of how I have the date range formatted, the script returns error 500 after the find. I've entered the date both as '1/22/2012...2/26/2012' or '22/1/2012...26/2/2012' or '1-22-2012...2-26-2012' and nothing seems to work. I've run this find request with the value I see for $DateRange in the data viewer while the script is running manually and it works without issue. It just doesn't work during the script. I've run this sort of find before without any issues... the only thing that has changed is that I've upgraded to FM14 (from 13). Any ideas???

Edited December 1, 2015 by Eli Walker

[Steve Martino]

Do you have any validation set up for the field?

And is it set up as a date field?

[ehwalker]

2 minutes ago, Steve Martino said:

Do you have any validation set up for the field?

And is it set up as a date field?

No validation and yes, it's a date field. 

[Josh Ormond]

I've run into periodic problems with using variables in the Perform Find script step. But it may also be that you don't have fully qualified dates.

If you change it to a process like this, does it work?

• Enter Find Mode
  Set Field [ TableName::DateFieldName ; $DateRange ]
  Perform Find [ No Restore ]

   

[ehwalker]

19 minutes ago, Josh Ormond said:

 

If you change it to a process like this, does it work?

• 
  Enter Find Mode
  Set Field [ TableName::DateFieldName ; $DateRange ]
  Perform Find [ No Restore ]

   

Yes!!! That did it, thanks so much. I didn't even think about using enter find mode

[Josh Ormond]

Another thing to check in your original script...triple check to make sure the correct target field is selected. It's really easy to accidentally have it trying to query the wrong field.

Also, were you using a FULL date? mm/dd/yyyy

[comment]

3 hours ago, Eli Walker said:

Regardless of how I have the date range formatted, the script returns error 500 after the find.

Why don't you print out your script and post it here, so that we know exactly what you're doing.

 

3 hours ago, Eli Walker said:

I've entered the date both as '1/22/2012...2/26/2012' or '22/1/2012...26/2/2012' or '1-22-2012...2-26-2012' and nothing seems to work.

You should be using the date format that is used by your file. If you normally enter dates as m/d/y (and the file accepts them), then use that format for finds too.

 

2 hours ago, Josh Ormond said:

But it may also be that you don't have fully qualified dates.

What is a "fully qualified date"?

[Josh Ormond]

2 hours ago, comment said:

What is a "fully qualified date"?

One that is not:

• 1/1
• 1.1
• Jan. 1st
• 1/1/15
• etc

When you put them in manually ( except the text version ), find fills in the wildcard slots for you. I've seen wonky behavior in 14 using variables. I have reported it, no word back yet.

[comment]

The term "fully qualified" is already taken, in computer programming in general as well as in Filemaker. Please don't invent your own vocabulary as it can be very confusing.

 

6 minutes ago, Josh Ormond said:

I've seen wonky behavior in 14 using variables. I have reported it

Do you have a link?

[Josh Ormond]

Fair enough.

I'll see if I can find it. It was on the FM forum site.

[bruceR]

I think this method is likely to work better. Use the date function to declare the dates; get the individual dates as text; use this in the $dateRange variable.

(Basically: let the file take care of its own date-to-text conversion)

Set Variable[ $d1 ; getAsText( date(1; 22 ; 2012))]

Set Variable[ $d2 ; getAsText( date(2; 26 ; 2012))]

Set Variable[ $dateRange; $d1 & "..." & $d2]

Enter Find Mode

Set Field [ TableName::DateFieldName ; $DateRange ]

Perform Find [ No Restore ]

Edited December 1, 2015 by BruceR

[Josh Ormond]

1 hour ago, comment said:

Do you have a link?

I can't find the thread anymore. Either way, I did some tests on a file using 14v2, and am unable to reproduce the problem. So either it is fixed, or I was having another problem.

[ehwalker]

Okay, so when I run this script on it's own it works alright (though the error code still shows) but when I run an 'import' script which runs a sequence of other scripts including this one it does not work. Any ideas on this? I tried using 'get as text' and it's the same issue... 

Script.pdf

[comment]

46 minutes ago, Eli Walker said:

when I run this script on it's own it works alright (though the error code still shows) 

If you're getting an error, then it doesn't "work alright". Since you are getting the start date from a field, I would suggest you go to a layout of the Releases table and do a find for ? (question mark) in the Date of Release field. If you find any records, they have invalid dates that need fixing - and the records not found will also be suspect.

[ehwalker]

5 minutes ago, comment said:

If you're getting an error, then it doesn't "work alright". Since you are getting the start date from a field, I would suggest you go to a layout of the Releases table and do a find for ? (question mark) in the Date of Release field. If you find any records, they have invalid dates that need fixing

I've run the search for ? in Releases and there are no found records

6 minutes ago, comment said:

and the records not found will also be suspect.

So what does this mean?

[comment]

If you have not found any records, then all the dates are valid. And I am afraid we have no way of reproducing your issue.

I suggest you start reducing your script (or rather a copy of it) to the bare minimum required to produce the error. For starters, remove all other find requests and criteria. Also see if the error is produced only by some records, or all of them.

[ehwalker]

Hi Comment, thanks for all your help on this... I finally managed to identify the issue. Apparently, it was the format in which the two time variables were entered. I guess error 500 encompasses date and time, not very intuitive. The find is now working as I originally had it. 

[comment]

1 hour ago, Eli Walker said:

I guess error 500 encompasses date and time, not very intuitive.

I don't think so: time validation error is 501. Perhaps the field in question is a timestamp field?

[ehwalker]

59 minutes ago, comment said:

I don't think so: time validation error is 501. Perhaps the field in question is a timestamp field?

Well it's not an auto-enter field if that's what you mean? It's just a normal editable time field... strange.

[comment]

3 hours ago, Eli Walker said:

Well it's not an auto-enter field if that's what you mean?

No, I meant is the field defined as type [ Timestamp ] - as opposed to [ Time ]? And how exactly are you getting the error number?

 

[bruceR]

Quote

Well it's not an auto-enter field if that's what you mean? It's just a normal editable time field... strange.

It is a time field; and you are putting dates into it?

[ehwalker]

 

On December 7, 2015 at 7:54 PM, comment said:

No, I meant is the field defined as type [ Timestamp ] - as opposed to [ Time ]? And how exactly are you getting the error number?

 

No, the field is just a time field, not a timestamp field. The error shows in the script debugger after running through the find step. The find returns no records and the error appears. I removed the time variables and the find worked fine without any error showing and it was definitely error 500... not 501

[Josh Ormond]

3 hours ago, Eli Walker said:

No, the field is just a time field, not a timestamp field. The error shows in the script debugger after running through the find step. The find returns no records and the error appears. I removed the time variables and the find worked fine without any error showing and it was definitely error 500... not 501

Eli,

This is very confusing. For every post you have made, you reference searching for Dates. In the last 2 posts you made, you say it's a problem with a "Time" field. Which is it?

1. See if you can reproduce the error/problem in a new blank file, with just the field in question and your script to find the records.

2. Post the text of your script here so we can see it. ( Print the script to a pdf, copy and paste ).

[LaRetta]

Hi Eli,

As Josh indicates, this is very confusing - starting out discussing date field and you clearly say it is a date field and then switching to time.  It also appears that you switched from using only Perform Find [ with variable as a Restore ] and later switched to using Enter Find Mode[].  

On December 1, 2015 at 7:07 AM, Eli Walker said:

The date range is contained within a variable '$DateRange' which is then entered from the 'Perform Find' script step. 

 

On December 1, 2015 at 8:30 AM, Eli Walker said:

I didn't even think about using enter find mode

When you do that, you must clear the find requests you have entered inside the Restore portion on your Perform Find[].  Might that be why you are getting error 500 ... because there is still find request included in the Perform Find[] step?  Note that Josh said, "no Restore" when indicating Perform Find[].

Still ... that does NOT answer the confusion on whether you are using time or date or timestamp.  :-)

Edited December 12, 2015 by LaRetta
added word 'not', completely changing the meaning of the last sentence, LOL.

[ehwalker]

Hi, let me clarify. In the table there are two fields, one date field and one time field. The find that the script performs has three find requests, one on the date field and two on the time field. Then during the script, error 500 would display after running the find. Nothing I did to the date fixed the error but as soon as I removed the find requests involving the time field the error no longer displayed... Which is where the confusion is. Why does error 500 display for an incorrect time format? (I later added the two time requests back in and made sure the time format was correct and it worked fine)

Edited December 12, 2015 by Eli Walker

[LaRetta]

I just saw the script you posted.  Now I see where the 'time' comes from.

“<$Time1”  

Please post your current script again now that you've made all the changes discussed.

[ehwalker]

Ok will do. But it should look exactly the same. The problem was the time format in the layout from which I pulled the time variable. Once I put it into the right format the script worked fine. I'll also try and make a mock file as josh requested to recreate the error so you all can see. Will have to do this tomorrow though as I don't have internet at my house and I'm currently typing on my phone... Plus our power is about to shut off 

Edited December 12, 2015 by Eli Walker

[LaRetta]

Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Fix Data::AJU#: “$$AJU#” AND Fix Data::NST Date: “$DateRange”
Omit Records; Criteria: Fix Data::NST Time: “<$Time1” AND Fix Data::NST Date: “$Date1” Omit Records; Criteria: Fix Data::NST Time: “>$Time2” AND Fix Data::NST Date: “$Date2” ] [ Restore ]
Perform Find [ ]

You should be using Set Field[] and not 'restore' in Enter Find Mode[].  You can not use "<$Time1" in the Restore.

That is why I asked to see your script again.  You should have changed it to match what Josh presented to you.

[ehwalker]

Oh sorry, I see. That script isn't the latest version, but I thought it was. I'll post it tomorrow! 

[Josh Ormond]

To use a variable in the Perform Find [ Restore ] //with the find specified in the step itself...

You can't use operators with the variable. It can only be the variable itself. So you can do 1 of 2 things:

1. Include the operator when you create the variable. So the variable itself is equal to "<WhateverTimeYouAreTestingFor".
2. Like LaRetta said, just use the Enter Find Mode, Set Field, Perform Find [ No Restore ]

I like #2 because it's easier to see what the script is doing. I don't have to open another dialog in order to see what it being set, etc.

 

[comment]

On 12/12/2015 at 8:58 PM, Josh Ormond said:

You can't use operators with the variable. It can only be the variable itself.

That's not entirely true. For example, this is a valid request ($n contains a numeric value):

 

 

Edited December 14, 2015 by comment

[Josh Ormond]

Thank you for the correction. That does indeed work. I know there are somethings that you can't do with variables. For example, the between operator ( "..." ) doesn't work with variables.

[comment]

36 minutes ago, Josh Ormond said:

For example, the between operator ( "..." ) doesn't work with variables.

Well, yes and no. This works:

10…$n

and this doesn't:

$n…20

 

 

[Josh Ormond]

As you can see, I don't use the Perform Find [Restore] step often.

Thanks again @comment.

[Rolfo]

Set field [ table::field ; "<"&$time ]    works (the variable must be evaluated)