Difficulty performing a complex find

September 21, 200619 yr

Hi,

I am having some trouble doing complex finds using the standard finding function in FM. I would like to find all records in my database where one field is not empty (no problem so far), and where another field does NOT contain a particular value (problem!). Can I use the find function, or do I have to write a script?

As a way around my problem, I sorted some records of interest in excel, and have a pared-down list of records that i would like to find. Is there a way in FM to find all records matching a list of serial IDs in an excel sheet?

Many thanks,

Marcel

September 21, 200619 yr

This can be done with a standard Find. The first Find Request would be to use "=" to find the empty fields, I presume that's what you have done. Then, still in Find mode, Add New Request, enter the value you don't want and click the Omit checkbox. Then perform the Find. All that can also be scripted.

FYI, your workaround is feasible too. Open your excel file with FileMaker, i.e. convert it. Make a relationship from the converted file to the original file using the ID. Then make a script and/or button to Go to Related, using the Match All Records option.

Oh wait, you're on FileMaker 7? In that case you'll have to use the old technique of putting all the IDs into a global field and GTRR based on that field.

September 21, 200619 yr

Author

Thank you,

The first method worked great. However, I did not quite understand the excel part. There are about 300 records to find - how do i put their IDs into a global field? Also, what is GTRR?

September 22, 200619 yr

GTRR = Go to related record. The reason I didn't elaborate on that is that I figured the Find would be easier and sufficient.

There are two ways to copy the IDs. One is to simply copy and paste them. In Excel you simply copy the column. In FileMaker you go to a layout with just the ID field on it and then hold the option key down while selecting Copy from the Edit menu. Notice the command changes to Copy All Records when you do that. (Or use cmd-opt-C.) This can also be scripted.

Another way is to use a script to loop through the records and use set field to add each ID to the global. (With FileMaker 8 you can use Get Nth Record in your loop, it's faster.)