Any FileMaker Pro experts here? How to automate a search.

I have a FM Pro 10 database.
I would like to script a search so that it reads 1 entry at a time from a file, and executes a search (find) with that input inserted into a specific field, with some other field as constants (basically, I want to see if anyone in the list has an expired membership). Any matching record will either be written to a file, or inserted into another database, whoever is easier.

I have a small amount of experience with FM scripting, but I can’t seem to find the right terms to search for on the web to provide an example.

AHunter3is the filemaker guru here. He could write you a script in his sleep. Hopefully he will either stumble across the thread or you could give him a PM. If he doesn’t pop along I could try to help, but it won’t be pretty!


By “reads 1 entry at a time from a file” do you mean a FileMaker Pro database table or do you mean “I have a plain text file or an Excel sheet and I want FileMaker to read 1 entry at a time from it” ?

With the exception of SQL databases (Microsoft SQL Server, Oracle, MySQL), FileMaker can’t “read” anything except by importing it and then dealing with the newly-imported data as FileMaker records, so I am going to assume you’ve got a table in a FileMaker database file that is part of your solution, and that we are going to “read 1 entry at a time” by looping through the found set of records in that file, where each record is one “entry”.

If your “entries” are in an external text or Excel file, we shall assume that first we import from that file, with the same result: a set of FileMaker records that constitute the current found set, OK?

If that’s not what you had in mind, you’ll have to clarify what your situation is.
You did not specify the name of the table containing your “anyones”, your people on the list who either do or do not have an expired membership. I am going to refer to that table as Members and I will also assume that you have a layout that shows records from Members called “Members Data Entry”.

You also did not specify the name of the field that would contain the same value as the “input” value from the “entry” — I don’t know if it is the member name or the member ID or the member’s social security number or what. I will refer to the relevant field as Member ID. Will they be unique? In other words, is it safe to assume that the Members table will contain one and only one record with the search term (Member ID or whatever it actually is)? I am going to assume that that is indeed the case.

You also did not specify how you planned on marking the members who will need to be exported to a file and/or written out to another database, perhaps because that’s part of what you were asking advice about (?), so what we’re going to do is put a 1 in a field named “Mark” in the Members table, and at the end we will do a Find for all records where Mark = 1 and then export those suckers. You can use an already-existing field or you can define a new field, Mark, for this purpose.

You’ll have to adjust, using your real table names and layout names and field names, of course.

We’re starting on the layout containing your found set of inputs, the layout showing record from your table of inputs, which you may or may not have just imported from Excel or text file:

Go to Record [First]
Set Error Capture [On]
…Set Variable [$SearchTerm; YourTableOfInputs::YourInputField]
…Go to Layout [ Members Data Entry (Members)]
…Enter Find Mode <—— do not pause, do not restore requests
…Set Field [Members::Member ID; $SearchTerm]
…Perform Find <—— do not restore requests here either
… If [Members::Expiration Date ≤ GetasDate (Get (CurrentHostTimeStamp)) and Get (FoundCount)= 1]
… Set Field [Members::Mark; 1]
… End If
…Go to Layout [original layout] <—— this would be the table of inputs layout again
…Go to Record [Next; Exit After Last]
End Loop
Go to Layout [Members Data Entry (Members)]
Enter Find Mode <—— do not restore or pause
Set Field [Members::Mark; 1]
Perform Find <—— don’t restore…
Export Records [restore export order]

Thank you for the very detailed reply!

Yes, I originally wanted to have FM read the inputs from a text file. Since it can’t do that, I can import them to a new database, or a new field in the existing one. It’s kind of surprising that FM can’t do this, though…

I’ll look at this in the next few days - I’m sure I can get it to work with the instructions you have provided.

Thank you once again.