LiveCode for FM Manual Custom ComponentsHow do I access FileMaker data in my Custom Component?

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.

Building the Custom Component UI

The first step is to create the UI for the custom component.

  1. Create a new stack
  2. Set the name of the stack to "queryFMDatabase"
  3. Set the width of the stack to 700 and the height to 500

Add controls

  1. Label field for "Contract type:"
  2. Option menu to choose the contract type from Weekly, Monthly or On Demand
  3. A button to run the SQL query
  4. A button to close the custom component window
  5. A field to show the column headers of the query results
  6. A field "result", to show the query results

The FileMakerAction handler

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 mouseUp

and add the closeComponent handler to the Stack Script.

on closeComponent
   close this stack
end closeComponent

Querying 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
  • Email
  • 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 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

Install 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.

  1. Add a button to the solution
  2. Open the Button Setup
  3. Set the label of the button to "Open Query Plugin"
  4. Set the action to Single Step
  5. Add a 'Set Variable' step with the value LC("queryFMDatabase"). This will run the 'queryFMDatabase' component.
  6. 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

E-Mail me when someone replies to this comment