trouble with "find" script

[johnb]

newbie to fmp - using version 6 - have a client that uses fmp - I'm experienced with msaccess, c#, .net. Not trying to be critical, but I'm having trouble with what seems to be a much more restricted set of commands/options than what I'm used to with access and using vb code. It may just be I don't understand the proper script structure/usage, etc.

I've seen the examples posted how to create a 'find' script. In version 6, the set field command seems to be where my problem is ---

Before I beat my head against the wall way to much, are there restrictions with scripting a 'find' in version 6? Will updating to version 7 be more user friendly?

My example specifically is I have a field called "receivedMaterial". I want to find records that are marked with the letter y.

I thought the command would be Set Field[receiveMaterial="y"], but fmp doesn't let me free text the entry.

Any help would be appreciated.

[Vaughan]

Enter Find Mode [] #Do not restore find requests

Set Field [ receiveMaterial, "=y" ]

Perform Find [] #Do not restore find requests

Set Field only works if the data being set matches the data type of the field it's being set into. Most stuff works, but trying to set a date range "1/1/2001...1/1/2002" will not. In this instance use Insert Calculated Result [], but remember that ICR is layout dependent (the target field must be on the current layout when the step performs.

[johnb]

Thanks Vaughan. That works fine. Also thanks for the info re: date range -- because that's actually what my client wants to search for --- several different date fields with a range of dates.

Is is possible to create a script that does something like

set field[date1=11/3/2004 OR date2=11/3/2004 OR date3=11/3/2004]

That would be most helpful and my immediate request will be resolved.

thanks again.

[-Queue-]

This would be an OR find, so you need to add multiple requests.

Set Error Capture [On]

Enter Find Mode [ ]

Set Field [date1, Date( 11, 3, 2004 )]

New Record/Request

Set Field [date2, Date( 11, 3, 2004 )]

New Record/Request

Set Field [date3, Date( 11, 3, 2004 )]

Perform Find [ ]

Replace Date( 11, 3, 2004 ) with a global date field, if that's what you're using. If you have more than just the date criteria, use Duplicate Record/Request instead of New Record/Request as in

Set Error Capture [On]

Enter Find Mode [ ]

{set criteria}

Set Field [date1, Date( 11, 3, 2004 )]

Duplicate Record/Request

Set Field [date1, TextToDate("")]

Set Field [date2, Date( 11, 3, 2004 )]

Duplicate Record/Request

Set Field [date2, TextToDate("")]

Set Field [date3, Date( 11, 3, 2004 )]

Perform Find [ ]

[johnb]

Thanks JT -- I've got it working -- just wasn't sure how to script the OR find -- it works perfectly.

Also thanks again to Vaughan -- I created a calculated field that determines if an existing date field is <= 14 days ago. This calc field value is therefore, either 1 or 0. I simply created and added a similar calculated field for each date field in question to the layout. Then I scripted using JT's New Record/Request suggestion for each date 'checker' field. The result is the OR set of all the searches -- which is what my client wanted -- without having to do a separate find on each date field.

thanks again.

[Ender]

I created a calculated field that determines if an existing date field is <= 14 days ago. This calc field value is therefore, either 1 or 0.

Hmmm, this sounds like an unstored calc (is there a Status(CurrentDate) in that calc?). Unstored calcs are not good for searching on. As the record set gets larger (thousands of records,) searches on unstored calcs can get pretty slow, especially over a slow network.

Specifying the date ranges to search on within the script will result in better performance for the find. The script can do this dynamically if you insert a calculated search date:

Enter Find Mode[]

Insert Calculated Result [ Date; ">=" & Status(CurrentDate) - 14 ]

...

[johnb]

Ender,

Example of calculated field being used:

Field: myItem1Checker is calculated as: Today-Item #1 Order Date<=250

this returns a 1 or 0 depending on "Item #1 Order Date" (a date field)

the full script is:

Go to Layout ["Mat'l Status"]

Enter Find Mode[]

Set Field["myItem1Checker","1"]

New Record/Request

Set Field["myItem2Checker","1"]

New Record/Request

Set Field["myItem3Checker","1"]

Perform Find[Replace Found Set]

this seems to work OK, but I'll believe you that there may be something

I should change, so I tried using the following script:

Go to Layout ["Mat'l Status"]

Enter Find Mode[]

Insert Calculated Result["Item #1 Order Date","" >= " & Status(CurrentDate)-250"]

Perform Find[Replace Found Set]

This obviously only searches for 1 field criteria, but I get an error message right away regarding "the date field must be a valid date in the range of 1 to 3000...." with a "revert field" button

I don't think I'm using the Insert Calculated Result correctly... I see your format of

Insert Calculated Result [ Date; ">=" & Status(CurrentDate) - 14 ]

using semicolon instead of comma as mine is ..... this was done automatically by fmp when I inserted the field ...I don't see how to manually edit the specifics like that ... but is that what is maybe wrong with this.

Also, is my first version going to have the problem you describe, or is using the Today function just as good as Status(CurrentDate)?

thanks for the help

john

[-Queue-]

Versions below 7 require DateToText conversion in such situations.

Use ">=" & DateToText(Status(CurrentDate) - 250)

'Today' works all right in a script step, but it has been phased out in version 7. So it's recommended to use Status(CurrentDate).

Semicolons are used in version 7 in place of the commas you should be using.

[johnb]

thanks for the clarifications.

thanks again to all that helped