Getting Started with FME Form: Working with Databases

Introduction 

In this article, you'll discover how to create a workspace in FME Workbench, the authoring application that accompanies FME Form, specifically emphasizing utilizing data from a database. Please follow along with the step-by-step instructions to learn how to create a database connection, read data from a PostgreSQL table that includes employee information, add a transformer to filter for active employees, and then write the filtered data to a Microsoft Excel workbook. 



If you regularly use spreadsheets instead of databases, the same example can be completed by reading from a Microsoft Excel spreadsheet. See Getting Started with FME Form: Working with Table/Tabular Data for the instructions.

FME Desktop was rebranded to FME Form in 2023. FME 2024.0 features a new interface. This tutorial can be used with any version of FME. The concepts are the same, but the screenshot appearances may differ.

Source Data

The source data is a table from the Safe Software provided training PostgreSQL database that contains a list of employees. 

 

Step-by-step Instructions

1. Start a Blank Workspace
Start FME Workbench, then click the Blank Workspace button.


The FME Workbench canvas is where the workspace will be built using readers, writers, and transformers. 


2. Add a PostgreSQL Reader - Create a Database Connection
On the top menu bar, click Readers, then select Add Reader. A reader is an object in a workspace that reads a source dataset. 


In the popup Add Reader dialog, type in PostgreSQL as the Format. Then click the red drop-down arrow next to Connection, then select Add Database Connection. 


In the Add PostgreSQL Connection dialog, enter in the following connection parameters:

• Name: Training PostgreSQL
• Host: postgis.train.safe.com
• Port: 5432
• Database: fmedata
• Username: fmedata
• Password: fmedata

Click Test. Once the Successfully connected to database message is received, click Close, then click Save to save the connection parameters. 


3. PostgreSQL Reader Parameters
With the database connection created, we can now access the database tables. Still in the Add Reader dialog, click on Parameters. 


In the parameters, click on the ellipsis [...] next to Tables, then expand Public and select Employees. Click OK three times to finish adding the reader to the workspace. 



A reader feature type object has been added to the canvas and represents the Employees table. Readers create a feature type for each table or layer in the source dataset you wish to read.


The Navigator is where readers can be found. The A) reader represents the dataset/database as a whole, such as the entire PostgresSQL Public database. The B) reader feature type(s) represent each part of the dataset, in this case, the Employees table. 


4. Add a Transformer
Transformers are the building blocks within a workspace to manipulate data, and each has a specific function. We will use a Tester transformer to filter the data. 
Click on a blank space on the canvas, and start typing Tester to bring up the Quick Add dialog. The Quick Add dialog is where readers, writers, and transformers can be added. Double-click on the Tester under FME Transformers to add it to the workspace. 


To access the data from within the transformer, it needs to be connected to the workflow. Click and drag from the arrow on the public.Employees reader feature type to the red arrow of the Tester transformer. This connection line sends data from the feature type into the transformer for processing.




5. Modify Transformer Parameters
The Tester transformer filters data based on conditional statements. A conditional statement of status = Inactive will be created to filter for the inactive employees at the company. 

Double-click anywhere on the Tester transformer to open the parameters. Click in the red box below Left Value in the Tester parameters to expose the selection options. 


After clicking in the box, an ellipsis button and a down (drop-down) arrow will appear. Click on the down arrow to open the drop-down menu, hover your mouse over Attribute Value to expand the menu, then click on status. Status is the attribute where the Active and Inactive values are stored. 


Next, click in the box under Right Value; the selection options will appear but can be ignored, as we will manually enter a value. In the Right Value box, type Inactive. Notice that after clicking in the Right Value box, the Operator changed to = (equals); this is the default operator. The Operator can be changed by clicking on the =, which will open a drop-down menu. The conditional test has been set up. Click OK to confirm the Tester parameters. 


6. Run Workspace
We have authored the workspace to read the data and filter for Inactive employees, but we still need to run the workspace. On the top toolbar (ribbon), click the green Run button. Clicking Run will trigger the workspace to read and process the data through any transformers. 


After clicking Run, a Translation Parameter Value dialog will pop up; this is where any user-defined parameters can be modified. We do not need to make any changes at this time; click Run to continue.  


7. View Output Data in Visual Preview
After initiating the workspace to run, data will flow through the workspace. The numbers on the connection lines represent the number of features it is transporting, and the number on each output port is the number of features that is output. The Translation Log will also open; this can be ignored for now. A feature in FME is a single row in a table. Click on the green eye icon on the Tester Passed output port to open the data in the Visual Preview window. 


Note: In FME 2023.2 or prior, the inspect icon is a green magnifying glass. 

Visual Preview is the data viewer within FME Workbench. There is also an external application to view data called FME Data Inspector. In the Visual Preview window, you will see the Tester_Passed table containing all employees with an Inactive status. Inspecting the Failed output port would display all of the Active employees. 


8. Add a Writer
The data viewed in Visual Preview is currently only contained within FME Workbench. To store the data to use later or for use within a different application, we need to write it out. This is accomplished with a writer. 

On the top menu bar, click on Writers to open the drop-down menu, then select Add Writer.


In the Add Writer popup dialog, select Microsoft Excel as the Format. For Dataset, click on the ellipsis, browse to a folder, and then type in EmployeeRecords.xlsx. Ensure that Sheet Definition is set to Automatic. Click OK to finish adding the writer to the canvas. 


Note: For FME 2023.2 and prior, the Sheet Definition should be set to Copy from Reader for this example, and the writer feature type will be public.Employees until it is updated to Inactive. To update the Sheet Name, double-click on the writer feature type to open the parameters, then change the Sheet Name to Inactive. In FME 2024.0 and newer, the default is Automatic. 

9. Modify Writer Parameters
The Sheet Definition was set to Automatic, which means the schema will be copied from the workflow when we connect the writer feature type. We can modify the name before we connect it.

In the Feature Type dialog, type Inactive for the Sheet Name, then click OK. 




Click and drag from the arrow on the Tester Passed output port to the red arrow on the Inactive writer feature type to connect it.



10. Run the Workspace and View Output
The final step is to run the entire workspace. Running the workspace with a writer connected will write the data out to disk. Click on the Run button to run the entire workspace; click Run in the Translation Parameters dialog. 

To quickly navigate to the folder containing the EmployeeRecords.xlsx dataset, single-click on the Inactive writer feature type to open the popup menu, then click on the folder icon. 


In the file explorer, open EmployeeRecords.xlsx in Microsoft Excel. If you do not have Microsoft Excel installed, the dataset will open in FME Data Inspector. 



Note that the default parameters in the Microsoft Excel writer are set to insert data every time the workspace is run. If the workspace was run a second time, this would result in a duplicate copy of the inactive employees being added. To learn more about the Microsoft Excel writer, see Excel Writer Parameters | Converting Excel to Excel.  

Continue Your FME Journey   

• Getting Started with FME Form: Working with Table/Tabular Data
• Getting Started with FME Form: Working with Spatial Data
• Getting Started with FME Form: Working with Web Data

Want to learn about a specific format?

• Tutorial: Getting Started with PostGIS and PostgreSQL
• Tutorial: Getting Started with Microsoft SQL Server
• Tutorial: Getting Started with Oracle

Want to continue learning FME Form basics?

• The FME Academy is a guided, on-demand way to learn the entire FME Platform, and it's FREE! 

Interested in self-serve and automation?

• Tutorial: Getting Started with FME Flow
• FME Flow Authoring - FME Academy

Have a specific question?

• Reach out to the FME Community 

Data Attribution

The data used in this example was randomly generated with datagenerator.com. This dataset is a work of fiction. Names, dates, places, and incidents are randomly generated. Any resemblance to actual locales or persons, living or dead, is entirely coincidental.