Problems with Find in script

[rocktap]

Hi All

I'm a bit of a newbie but can't work out why a simple find step in my script isn't producing correct result. Here is the set up:

I have a field called 'Month' which has the values 01 for January, 02 for February etc. I want a script that will pull up all records that are say January & February.

So I have a step in my script which is Perform Find, and have specified a find request to find when 'Month' has range 01...02. But when I run this script, I get records from other months as well. The weird thing is, if I enter find mode and do a manual search for those records (01...02) it works fine. What am I missing?

Kind regards

Peter

Edited October 24, 2010 by Guest

[bruceR]

Please post a copy of your file or of your script. You can print the script to Preview then copy the text and paste it here.

[Matthew F]

If specifying the find requests in your script step does not work you could try the following script steps:

1. Enter Find mode

2. Set Field Month = 01..02

3. Perform Find (no dialogue)

[Lee Smith]

What happens when you try this search manually?

I believe that FileMaker is not reading the O1 and 02 as months, but as numbers. Have you tried 1..2?

What other dates are you getting, 11, or others.

Lee

Edited October 24, 2010 by Guest

[bcooney]

Why do you have a field, "Month?"

You should have a date field instead. You can easily format it to display only the month.

Also, you can derive a month field from it, if you want, Month (date).

[rocktap]

Hi there

Thanks for all your replies. I import bank statements into FM and unfortunately the date field comes in YYYY/MM/DD from my bank. So I created three fields to extrapolate day month and year using the left/middle/right functions. I thought the month field, which just has 01, 02 etc would be an easy search field. As I say - no problem when I go to find mode my self and manually search the range I want. The script is only one line! perform find (restore), with a find request of Month = 01...02

Will try that alternative script suggestion at let you know. Many thanks for helping this newbie!

[rocktap]

Hi Lee

The month field is a number field. I noticed if I perform the script to find 01...02, that works fine, but if I then select script to find 03...04, it adds the 03...04 records to the existing 01...02 records - its not omitting previously found records.

[comment]

if I perform the script to find 01...02, that works fine, but if I then select script to find 03...04, it adds the 03...04 records to the existing 01...02 records - its not omitting previously found records.

Please post a file showing the problem.

I import bank statements into FM and unfortunately the date field comes in YYYY/MM/DD from my bank. So I created three fields to extrapolate day month and year

Why not create one field that calculates the date?

[LaRetta]

Rocktap, the additional reason it would be beneficial to turn your text string (date) into a real date as suggested by Comment (Michael) is that, if you search only for month range, you will get all years for the months selected.

Having the date segmented, you cannot search for range of months AND range of years (the criteria could overlap); best to convert to proper dates. You will probably need those true dates for other reasons in your solution anyway. :smile2:

[rocktap]

Hi there

I've attached a clone of the file. I'm planning a new file for each financial year so don't have concerns about finding August 2010 and 2011 for example. I realize there are probably better ways to handle the date - as I said I'm a newbie. But I just don't understand why a find request works manually but the same find request in a script doesn't work. Thank you for your assistance though - much appreciated!

Peter

PK_Accounts_2011_Clone.fp7.zip

[comment]

I cannot reproduce the problem. Some of your scripts have criteria that don't match their names - but the 'GST April May' script works fine for me.

[rocktap]

Ah, solved my problem. I thought the specify find requests window was showing a list of all available find requests and it was up to you to select one. But the window is actually showing the find requests that are being applied. For some reason I had two find requests listed. Once I deleted the incorrect one all is good.

I had a feeling the answer was right in front of me!

I would like to create a more efficient way to convert the text date data to a proper date field. How can I convert a value such as 2010/05/23 to a date value of 23/05/2010?

(I'm based in New Zealand where we put day before month.)

[comment]

How can I convert a value such as 2010/05/23 to a date value of 23/05/2010?

Try =

Date (

Middle ( TextDate ; 6 ; 2 ) ;

Right ( TextDate ; 2 ) ;

Left ( TextDate ; 4 )

)

Set the calculation's result to type Date.

[rocktap]

Thank you Comment - worked perfectly.