Finds Across multiple windows

I was thinking how useful it would be for a user working in a FileMaker database to be able to filter a list in their current window based on content from another window, FileMaker 10 script triggers now makes this possible.

Let’s set the scene, you have two windows open in FileMaker Pro 10.  In the first window (A) is a customer record in form view, in the second window (B) is an order list view.  My idea was to allow the user to select the content of window A as a find request in window B, from a pop-up list. The trick to this is making portable code that can work across many windows and Table Occurrences, without the need for complex re-coding each time.

Order List with Pop-up filter
Window B: Order List with Pop-up filter

The following code will give you the basics on how to reproduce this feature.

For our example we’ll create a FileMaker file with 4 tables: PREF; CUSTOMER; ORDER and PRODUCT. For ease of demonstrating I wont use an order lines table and just link 1 product and 1 customer to an order. So the Order table will have a Foreign Key for the Customer and Product table in turn.

The trick to making the code portable is down to a good naming convention, as you’ll see shortly. Name your Primary Keys as follows: __kp_PRODUCT and your Foreign Keys: _kf_Product

Now we need to create three scripts, as follows:

SCRIPTS

Script Name: Set Window Name
Set Variable [$WindowNo; Value:GetAsNumber( Get(WindowName ) )]
Set Window Title [Current Window; New Title: Substitute( Proper( Get( LayoutTableName ) ) & $WindowNo ; “-” ; ” – ” )]

Script Name: Select Filter
Set Error Capture [On]
Freeze Window
Commit Records/Requests [Skip data entry validation; No dialog]
Set Variable[$Window; Value:Get ( WindowName )]
Select Window [Name: ORDER::select; Current file]
Set Variable[&ID; Value:Evaluate( “__kp_” & Get ( LayoutTableName ) )]
Set Variable[&ForeignID; Value:”_kf_” & Proper( Get( LayoutTableName ) )]
Set Variable[&ForeignTO; Value:Proper( Get( LayoutTableName ) )]
Select Window [Name: $Window; Current file]
Enter Find Mode [ ]
Set Field By Name [Get( LayoutTableName ) & “::” & $ForeignID; $ID]
Perform Find [ ]
Perform Script [“Set Filter to Window Names”]
Set Field [ORDER::Filter; “”Filtered by ” & $ForeignTO & ” ID: ” & $ID]
Exit Script [ ]

Script Name: Set Filter to Window Names
Set Error Capture [On]
If [Get ( ScriptParameter ) = “New”]
Perform Script [“Set Window Name”]
Set Field [ORDER::Select; “”]
Commit Records/Requests [Skip data entry validation; No dialog]
End If
Set Variable [$$ScriptTrigger; Value:$$ScriptTrigger + 1]
If [$$ScriptTrigger > 1]
Set Variable [$$ScriptTrigger; Value:””]
Exit Script [ ]
Else
Go to Layout [“PREF” (PREF)]
Set Field [PREF::Windows; Substitute( WindowNames ; Get ( WindowName ) & ¶ ; “” )]
Commit Records/Requests [Skip data entry validation; No dialog]
Go to Layout [original layout]
End If

SCRIPT TRIGGERS

Now on the Layouts for both the Order and Product Tables attach a Layout Script Trigger; this can be found in the Layout Setup dialog. Select the option to run a script ‘OnLayoutLoad’ and run the script ‘Set Window Name’. Next do the same for the Order layout, but set the script to ‘Set Filter to Window Names’ with the parameter ‘New’. These scripts will now run whenever a user goes to any of the above layouts in Browse mode.

Next we need to set a Field Script Trigger. We do this on the ‘Select’ field on the Order layout (Next to the Filter label in the screen shot above). In layout mode, right click on the Select field and choose ‘Set Script Triggers…’; choose the Event: ‘OnObjectModify’ selecting the ‘Select Filter’ script and pass the parameter ‘ORDER::Select’. This means that whenever this field is modified the ‘Select Filter’ script will run with the contents of the filter being passed to it.

RUNNING

So now we have everything in place we can test are example. By opening new windows and select either the Customer or Product layouts a script will run to set the window name to something meaningful, ie: the name of the table you are on. Whilst this step isn’t absolutely necessary it does mean that when a user selects a window name in the filter list, the names are relevant to the areas within each window.

Then when the user goes to the Order layout in a window a script runs that sets a field in the PREF table to the list of all the Customer and Product windows. This field is then used in the ORDER::Select value list. You’ll also notice that whenever a user changes the filter this script is also called at the end of the ‘Select Filter’ script, thus keeping the window list up-to-date.

The ‘Select Filter’ script is fairly straightforward and you can see how the naming conventions become important here in making this code portable through many tables.

CONCLUSION

Hopefully this example has given you an insight into how Script Triggers can be very powerful within the user interface. You may also have spotted that I have also used a new Script Step form FileMaker 10: ‘Set Field By Name’; this new step helps to make the code considerably smaller and much more portable.

I shall be uploading a demo file in the Linear Blue Technical Presentations area shortly, which will hopefully help you walk through this slightly complicated example.

If you have any questions regarding this or any other blogs, please feel free to leave comments.

Cheers
Paul

Paul de Hallé

Paul de Hallé

Paul de Hallé is the CEO and founder of Linear Blue, having set up the company in 1999 to focus on intuitive database solutions and web integration. Paul is a FileMaker Certified developer; and has also been a key player in the implementation of the FileMaker Certification program since it's inception with FileMaker 7. A regular guest speaker at FileMaker Business Alliance & Technet meetings in the UK and the FileMaker Developers Conference in the USA, along with contributing to various technical publications around the world.

More Posts - Website - LinkedIn

Leave a Reply