Save To Database Flow Development

We will now develop a Form Action Flow to save the Computer Inspection form data to a SQL database, For this tutorial we will provision a free PostgreSQL database on Supabase. If you want to run this tutorial on a PostgreSQL database and do not have access to one please create an account on Supabase and provision one. Alternatively you can continue to build this tutorial on a MySQL or MSSQL database also. The only difference is in creating the system which is explained later on.

Prerequisite - SQL Database

Recall that the Inspection Form has the following components and API property name.

Component/Field	API Property Name	SQL Type	Allow Null
Date / Time	inspectDateTime	text	No
Equipment	equipment	text	No
Condition	condition	text	No
Comment	comment	text	Yes
Inspected By	inspectedBy	text	No
Signature	signature	text	No

Note that we have left out the equipmentImage by design. We will revisit this later.

For this tutorial, we will be connecting to a PostgreSQL database on Supabase for free. Login to your Supabase account online and create a database / project. If you want to proceed with MySQL or MSSQL please follow the database documentation to create a database, table and the columns (use varchar instead of text where appropriate) and keep the connection credentials handy.

Supabase PostgreSQL

1. Create a project or choose an existing one and click on the Table Editor in your project. Make a note of the password for reference
2. Create a new table called computerinspection
3. Now add new columns to the table using the API Property Name as the column name with the corresponding SQL types from above
4. Remember only comment is a Nullable field as it may not be filled if the condition of the equipment is is good, all others are mandatory fields
5. Once the 6 additional columns are added click on Save
6. Note that the table has 8 columns in total including a primary key id and a created_at date time
7. Now create a RLS policy to allow to ALL access for Authenticated users. For MySQL and MSSQL refer the documentation to create the required access for reading and writing the table
8. From the Database settings screen, copy the DB host, port, database name and user name
9. To confirm that all works well (and to later verify if our data is being saved correctly) use a PostgreSQL GUI tool or install one and connect to the database server and confirm

We are now good to go on to the developing the flow and save our Inspection data when completed.

Getting intermediate results

Whenever you need to get intermediate results from the integration or other steps added, connect the last node to Success and Run the Flow. You can now copy the results for further steps. You can also Save the Run if you want to permanently save the flow results.

Lets say for e.g you need to extract results from an HTTP call. Add the HTTP node and configure it. Connect the HTTP node to Success and Run the flow. The results are now displayed and you can copy the results of the HTTP call. Now add the JSON Parser node and use the copied results as an example input to extract data from.

Flow Development

1. In the Forms list, click on the Flows icon on the Computer Inspection row
2. A list of Flows will be displayed. Now click on the Create button and choose the Form Action flow
   • Title - Enter Save to Database
   • Description - Enter description as Save to PGSQL database on Supabase or similar
   • Select the On Complete option
   • Click on Save to create the flow
3. You will now be navigated to the Flow Builder

Understanding the steps

Before we develop the flow, let us draw a flow chart of the different steps we need to perform to save the data:

flowchart TD
    A([Start]) -->|Form Complete Action| B[Extract JSON Data]
    B --> C{Error}
    C --> |No| D[(Save to Database)]
    C --> |Yes| E([ReturnError])
    D --> F{Error}
    F --> |No| G([Return Success])
    F --> |Yes| E

Note

1. When a form is completed we receive the data in the flow
2. From the form, let us extract the required data for saving to the database. For this we will be using the JSON Parser node
3. Save the extracted data to the database. For this we will be using the SQL Query node
4. Return success or error

Developing the Flow

1. In the Flow Builder, the Add Node dialog should already be open. If not, click on the Form Action Flow start node and then click on the :fontawesome-solid-circle-plus: icon that is displayed below the node
2. Search for the JSON Parser node by typing in json and clicking on it
3. The JSON Parser node is now opened and you can configure it.
   1. Change the title to Extract Form and remember to connect the Form Action Flow start node Success connector to this node (else the expression builder will not display all the possible fields and nodes)
   2. Since we want to work with the Form Data, click on the Form Fields section
   3. For Map Field Name select Form Data
   4. Under Expression click on the :fontawesome-solid-wrench: icon to open the Expression Builder
   5. To extract the data we need to know what format the data is going to be submitted. In the Expression Builder windows, click on the :fontawesome-solid-rectangle-list: icon to open the data format screen.
   6. Since this is the first time we are checking this there is no data. Click on the Add Form Data button and the form is now displayed. Fill in the form and submit it. Remember this is just test data for us to work with the flow and is not data that is submitted by an end user and hence does not affect the system status.
   7. Now the data format is displayed in the JSON editor. Click on the editor, select the entire JSON and copy it. Note that the condition is set to Warning so that we can also enter a comment in the form and get it in the format.

      {
      "inspectDateTime": "2024-03-07T03:45:09.000Z",
      "equipment": "cpu",
      "condition": "warning",
      "equipmentImage": [],
      "inspectedBy": 1,
      "comment":"Warning comment",
      "signature": "data:image/png;base64,iVBORw0K ......... AAEl==",
      "branding": {
          "data": {},
          "metadata": {}
      }
      }

   8. Paste the data into the JSON window on the left
   9. The expression builder we use for JSON parsing is a tool called Jsonata
   10. We now need to extract only the fields we want to persist. Note that if the column names do not match the API Property Names, the same JSONata expression can be used to rename the attributes in the output JSON.
       1. In our example since the column names and API Property Names match, we will write an expression to extract the required data as is
       2. :fontawesome-solid-triangle-exclamation: Important: The JSON data needs to be an object with the table name as attribute and the value the actual row of data. This allows the No-Code SQL Engine to determine the data for each of the tables specified
       3. Since comment is an optional field, we will use the inbuilt $exists() function to check if a comment is present and if not found add comment with a null value to the output JSON

          {"computerinspection":$.{"inspectDateTime":inspectDateTime,"equipment":equipment,"condition":condition,"inspectedBy":inspectedBy,"comment":$exists(comment) ? comment:null,"signature":signature}}

   11. Paste the expression from the step above into the Mapping Expression window on the right. The extracted data is now displayed at the bottom. Confirm that what is displayed is correct.
   12. Now Click on Save and close the builder,
   13. Now Click on the :fontawesome-solid-floppy-disk: icon and save the node
4. Now we need to save the data to the PostgreSQL database. Select the Extract Form node and then click on the :fontawesome-solid-circle-plus: icon
5. Search for the SQL Query node by typing in sql and clicking on it
6. The SQL Query node is now opened and you can configure it.
   1. Change the title to Save Form Data and remember to connect the Extract Form node Success connector to this node (else the expression builder will not display all the possible fields and nodes)
   2. First we need to configure a new database server. Click on :fontawesome-solid-plus: icon next to the Database Server dropdown.
   3. The Add System dialog is displayed. Enter the following data:
      • Description: Supabase (or anything else you desire)
      • Identifier: This is a unique identifier for the system, enter supabase-pgsql
      • Type: PostgreSQL (or any other database of your choice)
      • Server: Enter the host name from the Supabase config, for e.g. aws-0-us-west-1.pooler.supabase.com
      • Port: 5432 (for PostgreSQL)
      • Database Name: postgres
      • User: Copy the user name from Supabase database settings and paste
      • Password: Enter the password you configured when you created the project
      • Save the system
   4. Now choose the newly added supabase-pgsql system (or the name that you entered above)
   5. Select Execution Type as Operation since we are going to directly insert the data into the database
   6. Select SQL Operation as Insert
   7. For Keys, click on the :fontawesome-solid-wrench: icon and choose the table computerinspection and click Save
   8. Under Data To Process, click on the :fontawesome-solid-wrench: icon and enter success in the searh box. The previously added extractform node with Success is now shown. Click on Success and Save to accept it.
   9. Now click on Save to save the node.
7. Connect the Save Form Data node to Success
8. Now click on Publish button to save and compile the flow. If the compilation is successful it will also be published and we can run it to test.

Testing the Flow

1. Now that we have published the flow we are ready to test it. The Flow Builder has an integrated Run and Debug tool to help us troubleshoot the flow in case of errors.
2. Now click on the Run Flow button. The Run dialog is now displayed. You can enter Form (test) data now by clicking on the Form Data button.
3. Run the flow by clicking on the Run button. The flow should run successfully.
4. If there are errors it could be due to some mistakes while configuring the system or copy/pasting the expression or similar. Check each step carefully with the documentation above.
5. Now login to the external Database GUI tool and check the computerinspection table. Voila, the Form (test) data that you entered must now be inserted to the table.
6. Go ahead and make some changes to the Form (test) data and Run the flow again. Refresh the table in the external Database GUI and confirm if a second row is now inserted.

Login to Turbo Forms App

Now login to the Turbo Forms App on web or mobile as the same user and click on the + button to create a new form to fill and choose the Computer Inspection form. Fill in data, choose the user, take a picture and after filling all fields click on Complete. This will ensure that the form is finalized and the On Complete flow we developed above will be executed. Check in the external Database GUI tool if the form data you just submitted is inserted into the table.

Follow Along Video

Watch Video (opens new window)

Topics Covered

Note

The following concepts have been covered:

1. Developing a flow to extract required fields from the submitted form data
2. Saving form data to a PostgreSQL (or similar) database when a form is completed
3. Creating Form (test) data
4. Running the flow and testing it