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
Add controls
- 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
end FileMakerAction
Closing the Custom Component
Custom components must also provide a way for the user to close the window.
Add a mouseUp handler to the "Close" button
on mouseUp
closeComponent
end mouseUpand add the closeComponent handler to the Stack Script.
on closeComponent
close this stack
end closeComponentQuerying the FileMaker Database
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
- Customer
- Manager
- Office Phone
- Address
- City
- State
- Zip
- Salesperson
- ContractType
- Hourly Rate
- Work Overview
- Notes
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.
Add the 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][1] & tab & tResult[x][2] & tab & tResult[x][3] & return after tList
end repeat
// Display the list in the "result" field
put tList into field "result"
end queryDatabaseLastly we need to call the queryDatabase command from the "Query" button. Add a mouseUp handler to the button.
on mouseUp
queryDatabase
end mouseUpInstall the Custom Component
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.
Run the Custom Component
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.
0 Comments
Add your comment