How do I access FileMaker data in my Custom Component?
When creating a Custom Component it is vital that you can access the data stored in the FileMaker Database. In this lesson we will use SQL to query the FileMaker database and make use of the results.
This lesson will use the "Landscaping" example solution available in the FileMaker learning resources. We will create a very simple example but you can execute any SQL query using this method, and use the results to create advanced Custom Components.
The first step is to create the UI for the custom component.
- Create a new stack
- Set the name of the stack to "queryFMDatabase"
- Set the width of the stack to 700 and the height to 500
- Label field for "Contract type:"
- Option menu to choose the contract type from Weekly, Monthly or On Demand
- A button to run the SQL query
- A button to close the custom component window
- A field to show the column headers of the query results
- A field "result", to show the query results
Custom components must include a
FileMakerAction handler in their Stack Script. Each custom component can define its own actions in this handler by checking the pAction paramater.
If pAction is empty, then the component should perform a default action (if appropriate); otherwise it should perform the named action.
In this example the component only has one action, to show itself. Add the following handler to the Stack Script.
on FileMakerAction pAction
go this stack as modal
Custom components must also provide a way for the user to close the window.
mouseUp handler to the "Close" button
on mouseUp closeComponent end mouseUp
and add the
closeComponent handler to the Stack Script.
on closeComponent close this stack end closeComponent
The final step is to query the FileMaker Database and display the results. In the example we will be using the "Landscaping" database which has one table, Landscaping, with the fields
- Customer No
- Office Phone
- Hourly Rate
- Work Overview
We will build an SQL query that will return the Customer, Manager and Salesperson for all customers with the selected Contract type. The query is executed on the FileMaker database and a numerically keyed array is returned.
queryDatabase command to the Stack Script
command queryDatabase local tQuery, tContractType, tResult, tList // Get the selected contract type put the label of button "contractType" into tContractType // Build the SQL query put "SELECT Customer,Manager,Salesperson from Landscaping WHERE ContractType='" & tContractType & "'" into tQuery // Execute the query on the current database put fmExecuteSQL(tQuery, fmCurrentFileName(),"") into tResult // Loop across the elements in the returned array repeat with x = 1 to the number of lines in the keys of tResult // Create a row for each record, with the fields separated by tab put tResult[x] & tab & tResult[x] & tab & tResult[x] & return after tList end repeat // Display the list in the "result" field put tList into field "result" end queryDatabase
Lastly we need to call the
queryDatabase command from the "Query" button. Add a
mouseUp handler to the button.
on mouseUp queryDatabase end mouseUp
Start up FileMaker, open the LiveCode for FM Solution and open the Custom Components Workspace.
Click on the '+' icon to add a new component and choose the 'queryFMDatabase' stack in the file dialog. Name the component 'queryFMDatabase' to match the stack name and click `OK`. Return to the 'LiveCode Workspace' and click the 'Close' button.
For a more detailed description of installing a Custom Component see this lesson.
To test the Custom Component open the 'Landscaping' solution.
- Add a button to the solution
- Open the Button Setup
- Set the label of the button to "Open Query Plugin"
- Set the action to Single Step
- Add a 'Set Variable' step with the value
LC("queryFMDatabase").This will run the 'queryFMDatabase' component.
- Select 'Weekly' in the option menu and click 'Query'
Change the Contract Type to 'Monthly' and click 'Query'
Click 'Close' to close the component.