Jump to content

Script find returns error: 500 regardless of date format


This topic is 1465 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Eli Walker
Link to comment
Share on other sites

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 ]

     

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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"?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by BruceR
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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 by LaRetta
added word 'not', completely changing the meaning of the last sentence, LOL.
Link to comment
Share on other sites

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 by Eli Walker
Link to comment
Share on other sites

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 by Eli Walker
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

req.png.7addca233c01c7728005bd31dafaf201

 

 

Edited by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 years later...

This topic is 1465 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.