using the contents of another field as a find criteria

[Bob W]

I am using Filmaker Pro 7.0V3

I am having problems with using the find script step. I have a database that is indexed by date and record number and sorted by date. The dates are in calendar sequence, but not all calendar days are in the database, as not all dates are “business days”, so using date functions is out. I wish to enter a date and find the records of that date and the previous 19 days to perform financial calculations on them. As the current date is entered I wish to have the “window” represented by the found set contain only the current day and the previous 19 days.

I thought of using the (Record Number) of the current day for finding the records that = Record Number OR the record numbers > = ( that Record Number – 19) AND <= the Record Number to find the records, but am confused how to create the find script set to do that.

The actual formula is not the main issue, the basic concept of how to enter a calculated value determined by the value of an existing field in the find criteria. I can not even figure out from the docs how to get the find function to return the record that matches the contents of another field. Even on the basic level , even manually, what would the syntax be to enter the command to find the record = to the value contained in another field? Or a value calculated from the value contained in another field? Any direction would be appreciated. I tried using the set field command with no luck. (Set Field [searchfield;anotherfield])

I know I am failing to understand basic concepts here, but the docs with the software are not extremely useful.

Thanks,

Bob

[comment]

Only global fields have values when you enter Find mode. So if you "wish to enter a date and find the records of that date", make the entry into a global field. Then Set Field [ searchfield ; globalfield ] will work.

not all calendar days are in the database, as not all dates are “business days”, so using date functions is out

I don't see the connection between the two.

[Bob W]

Hello,

Using Filemaker Developer 7.0 V3

I asked a question yesterday and it is possible I did not describe my problem properly.

I am tying to make the find script work properly.

Fields:

Both fields are contained in the find layout.

recordnumber (contains the record number calculated by Get(RecordID) function

filterfield ( number, global field)

The database has 25 records, record id numbers 1-25.

Script:

I goto the find layout (debug yes)

I sort records on recordnumber (debug yes)

I Go to the last record (debug yes)

Set filterfield ( Set Field [filterfield; recordnumber – 19] (debug yes)

Enter Find Mode (debug yes)

Perform Find [Restore]

Criteria : recordnumber [>=filterfield]

I expect it to return records 6 through 25, instead it returns records 2 through 25.

What am I doing wrong?

Thanks,

Bob

[comment]

Stored criteria cannot be dynamic (they cannot reference fields or variables). You probably want to do:

...

Enter Find Mode []

Set Field [ recordnumber ; "≥" & filterfield ]

Perform Find []

---

P.S. In general, it's best to append clarifications to the original thread.

[Bob W]

Thanks Consultant,

That explained my problems

The inability to store dynamic was my issue.

Bob

[Lee Smith]

I merged your two topics, and sent you a Private Topic.

Lee