Find Criteria: I Think, Therefore I Develop
by John Mark Osborne (jmo@best.com)
RATING: Intermediate
VERSION: FileMaker Pro 3, 4 & 5
PLATFORM: Macintosh & Windows
TECHNIQUE FILE: FINDCRIT.FP3
This technique comes out of the deepest depths of my FileMaker insanity! This article showcases my thought process rather than offer a solution. While it does show you how to list the current find criteria in browse mode, it may not be a viable solution for all developers. Many developers use standard scripts, but a good portion of the scripts are unique to a particular solution. My aim in this article is to show you my thought process in order to help you with yours. Don't regurgitate scripts, understand them so you can be self-sufficient.
The Objective
The goal of this solution is to grab the find criteria and the fields used in find mode and place them in a global field so they can be seen in browse mode. The global field acts as a reminder of the most recent find criteria. This script needs to be dynamic. For example, if a field is added to the find layout, there should be no need to modify the script. This makes the script more difficult to program but saves time in the long run. If you want to modify the script weeks or months later, you will probably have to spend some time relearning the script. All you need to do is add a field to the find layout and the following solution will incorporate it.
The Script
Here is the entire script. Take a quick look at it to become familiar with the structure.
FreezeWindow
AllowUserAbort[Off]
SetField["xCriteria",""""]
EnterFindMode[Pause]
GotoField[]
GotoNextField
SetField["xStart","Status(CurrentFieldName)"]
Loop
SetField["xField","Status(CurrentFieldName)"]
Copy[Select]
Paste[No style,"Temp"]
Loop
GotoNextField
ExitLoopIf["xField=Status(CurrentFieldName)"]
EndLoop
SetField["xCriteria","xCriteria&Case(notIsEmpty(Temp),
Status(CurrentFieldName)&":"&Temp&"¶")"]
GotoNextField
SetField["Temp",""""]
ExitLoopIf["xStart=Status(CurrentFieldName)"]
EndLoop
PerformFind[]
Initialization
For the most part, the steps before beginning the outside loop initialize the file to make it ready for processing the find criteria. Here is the first part of the script.
Freeze Window
Allow User Abort [Off]
Set Field [" xCriteria ", " "" "]
Enter Find Mode [Pause]
Go to Field [ ]
Go to Next Field
Set Field [" xStart ", " Status(CurrentFieldName) "]
The Freeze Window script step prevents the screen from redrawing when moving between fields. There are two loops in the script that move the cursor from field to field. This makes for a lot of screen movement, which Freeze Window can eliminate.
Allow User Abort prevents the user from canceling the script. If you were publishing a solution using this technique, you would probably create a separate find layout, hide the status area, capture errors and add other safety mechanisms to prevent the user from causing problems with the solution. It is a good idea to keep user abort on until the script has been thoroughly tested. If user abort is off and the loop cannot exit, you will have to force quit your FileMaker file and possibly damage it. Once you are sure the script is flawless, turn user abort off.
The first Set Field step initializes the xCriteria global field by removing all data from it. This field will display the find criteria in browse mode so it is necessary to remove all previous find criteria. The "x" at the beginning of the field designates it as a global field. Many developers use a "g" at the beginning of a global field name to quickly recognize them. I don't like this since regular fields that begin with "g" will be sorted in the same grouping as global fields. I used the underscore character to sort global fields to the end of all dialogs, but found that I couldn't select the entire global field in a calculation by double-clicking on it. I have settled on "x" for the time being because I have found very few fields beginning with "x", "y" or "z" and I can still double-click the field to select it. I used "x" rather than "z" or "y" because I think it stands out more.
The Enter Find Mode script step enters the user in find mode. This is pretty straightforward but make sure you uncheck the option for Restore. This is a user-defined find so no find criteria should be automatically entered. However, do make sure Pause is checked since it allows the user to enter their find criteria.
To save time, the first Go to Field script step has no field specified. I was placing the following Go to Next Field step in the script and didn't want to scroll down to the Exit Record/Request script step. That's right! Go to Field with no field specified exits all fields (like typing the Enter key). In the FileMaker Pro 2.0 days, Go to Field was the only way to exit all fields. The point of this step is to start the looping script at the beginning of the tab order. If a field were selected prior to initiating the script, the find criteria wouldn't be listed in the xCriteria global field in the same order as they appear on the layout.
The Go to Next Field script step selects the next field in the tab order. Since no fields are selected, the first field in the tab order is selected. The reason for this step is apparent when considering the next step. The Set Field step records the start field in the xStart global field. While the Go to Record/Request/Page script step has the ability to exit after the last record, Go to Next Field has no such option. Therefore, you must record the first field name that is processed in order to exit the loop when it is selected again. Don't forget that this is a dynamic script. We could hard code the starting field so that it doesn't need to be recorded. However, this might cause a problem if a new field is added to the find layout.
The Loop
The outside loop is the main body of this solution. It loops through all the fields in the tab order on the current layout and grabs the find criteria and field names. Take a look at the entire loop including the inside loop.
Loop
Set Field [" xField ", " Status(CurrentFieldName) "]
Copy [Select]
Paste [No style, " Temp "]
Loop
Go to Next Field
Exit Loop If [" xField = Status(CurrentFieldName) "]
End Loop
Set Field [" xCriteria ", " xCriteria & Case(not IsEmpty(Temp),
Status(CurrentFieldName) & ": " & Temp & "¶") "]
Go to Next Field
Set Field [" Temp ", " "" "]
Exit Loop If [" xStart = Status(CurrentFieldName) "]
End Loop
The first step in the loop sets the current field name to the xField global field. This is different from the xStart value that is set before entering the loop. The xField global field is set every time the outside loop repeats itself. The value in the xField global field is used in the inside loop. The inside loop returns the cursor to the field currently being processed in the outside loop. This loop is needed because the outside loop has to paste to another field and loses the currently selected field. In order for the outside loop to process every field in order, the inside loop must return the cursor selection back to the field currently being processed.
After the current field name is stored, the contents are copied and pasted into a regular text field called Temp. Set Field cannot be used since a looping script is being used to move through all the fields in the tab order. Set Field cannot grab the contents of the current field since there is no calculation Status function for current field contents (hint). Set Field can place the result of a calculation into the currently selected field if no field is specified. This is a neat trick I have used quite a bit.
In addition, the contents of the currently selected field cannot be transferred directly to the xCriteria global field since it is not possible to paste into a global field while in find mode. I thought about shuffling between find and browse mode in order to transfer directly to the xCriteria global field. This would prevent the need to add the Temp field but would require additional script steps like Modify Last Find. Also, I did not want a field to be listed in the xCriteria field unless find criteria was entered into it. Keeping with the idea of creating a dynamic script, I did not want to hard code the testing of each field for empty. I opted to test the Temp field for empty each time the script looped to keep it dynamic.
The next series of steps are the inside loop. As stated before, the inside loop reselects the field that was selected before the copy and paste was performed. This is done by going to the next field until the xField global field equals the Status(CurrentFieldName). I sure wish I had the ability to go to a field based on a value found in a field. This would work like the Go to Layout script step and the Status(CurrentLayoutNumber) function. I guess FileMaker may need the Status(CurrentFieldNumber) function.
Once the current field being processed is selected again, the xCriteria global field is set to the current contents of the Temp field. The Set Field leaves the xCriteria untouched if the Temp field is empty. This is accomplished through a Case statement in the Set Field calculation. The same result could have been achieved with an If script step that tested the Temp field for empty, but that would have required two extra steps (If and End If). I find it easier to read shorter scripts, so I generally opt for solutions with as few script steps as possible unless that means speed degradation or limitations on capability.
The formula in the Set Field is worth discussing for a minute. Here it is so you don't have to scroll up.
xCriteria & Case(not IsEmpty(Temp), Status(CurrentFieldName) & ": " & Temp & "¶")
Notice that the xCriteria field is outside the Case statement. I could have written the script as follows and had the same results.
Case(not IsEmpty(Temp), xCriteria & Status(CurrentFieldName) & ": " & Temp & "¶", xCriteria)
Whenever I write a calculation, I look for any places where the same information is repeated more than once. In this case, xCriteria field is repeated twice if it is placed inside the Case statement. While this is a simple example, you will find you can cut the length of your formulas significantly if you look for duplicated functions and fields. Reducing the length of your formulas might even increase the speed and will certainly make it easier to read.
Once the contents of the Temp field have been transferred to the xCriteria field, the next field is selected with the Go to Next field script step. This is in preparation for the end of the loop. When the loop cycles back to the top, you want it to process the next field on the layout.
The Temp field is also initialized in case the next field is empty. If the Temp field is not initialized, the Paste at the beginning of the loop will not remove the data from the Temp field if the current field is empty. This will cause the script to report incorrect find criteria for empty fields.
The final step in the loop exits the loop if the xStart global field equals the Status(CurrentFieldName). When the loop exits, the find is performed. If the xCriteria field is placed on the resulting browse layout, the user will be able to see the contents of the find criteria.
Limitations and Considerations
This script was not written to handle multiple find requests or the omit check box. To script the ability for considering multiple find requests, you will need a third loop that cycles through all of the find requests. However, I'm not sure there is a way to detect if the Omit check box is selected or not. You could hide the status area and make the user check a field, titled Omit, if they wanted to omit records with find criteria from the current request. This wouldn't be too difficult to program.
Another issue with this solution is the multi-user viability. The Temp field is a regular field rather than a global field for reasons explained previously. This could cause problems with record locking. The best solution might be to switch to shuffling between find and browse in order to place the copied find criteria directly into the xCriteria global field. Global fields are local to each user in a multi-user scenario.
One last thing I'd like to mention is the use of Copy and Paste steps. Both of these steps require that the source field be on the current layout. This often forces people to hide fields in the corner of a layout to prevent users from seeing the contents but still allowing copy and paste functionality. But, even worse, the Copy step deletes the contents of the system clipboard. The user might have something stored there. Let's say a user copies a name from a field in your FileMaker solution and clicks on a button to navigate to another area of your solution. This button uses the Copy script step for whatever reason. When the user tries to paste, the data will be different than what he expected. This will result in confusion and most likely a phone call to you.
As I said before, this is an exercise for your brain. I'd love to hear from anyone who has ideas on how to improve this script.
Happy FileMaking!
John Mark Osborne is the Director of FileMaker Training Materials at Stratisoft ( http://www.stratisoft.com ). Visit the Stratisoft web site to find out more about intermediate and advanced FileMaker training. John Mark also has a personal web site with hundreds of free FileMaker tips and tricks ( http://www.databasepros.com ).