Performing find by comparing to another field

[Ryan P]

Hello,

I've run into a frustrating problem. I have a custom dialog box which appears in my script and the user inputs a certain year. What I want to do is then perform a find on a date field and just keep the records that have the same year as was inputted by the user.

I what to have the set the satisfies the calculation Year(dateField)=input

However, the find function does not seem to let me do this...

Thank you in advance.

Ryan

[comment]

It's true that Filemaker does not do calculations during Find. However, you can simply enter a 4-digit year as the search criteria into a date field - Filemaker will interpret this as */*/yyyy (subject to date format being used) and find all records in the given year.

[Ryan P]

I'm not quite sure how to do this in a script however. In the criteria I have tried both */*/<> and just <> but it comes up with an error stating that the field should be in the range 1 to 4000 and has to be in date format. I also tried doing this with a variable */*/$y but still to no avail. Is there any other way I could do this?

[Brian S]

Try:

- Enter Find Mode

- Show Custom Dialog (for Input Field #1, select the date field)

- If Get ( LastMessageChoice ) = 1

- Perform find

else

- Browse mode

- End if

You have to verify that the user presses button #1 before proceeding. FileMaker treats buttons 2 and 3 as 'cancel' buttons and fails to set the field.

[Ryan P]

Thanks that worked great, one more slight problem though, is that I need to perform the same search on two different tables so can I do this without have to the user re-enter the date using the method you outlined?

[comment]

Perhaps you need to reconsider your strategy, because it will be difficult to show the results of searching two different tables at the same time.

[Ryan P]

I'm aware of that, I don't intend to show the result of the find, I intend to sum a certain field in both tables and compare the two values...

[comment]

Well, then you can grab the user-entered value into a variable, and use that for searching the other table, e.g.

Enter Find Mode [ ]

Show Custom Dialog [ FirstTable::Datefield ]

Set Variable [ $y ; FirstTable::Datefield ]

Perform Find [ ]

...

Go to Layout [ SecondTable ]

Enter Find Mode [ ]

Set Field [ SecondTable::Datefield; $y ]

Perform Find [ ]

[Ryan P]

Thanks very much that works perfectly now.