SQL

A very common use case is, is to store the data of Forms into a database. This database could be either an “internal” one (only used by Portrait) or a database of a different system. However, in both cases, you need to set up a connection and check the permission first.

What the SQL query does, is up to you. Common use cases would be:

INSERT statement for creating a new record.
UPDATE statement to edit an existing record.
DELETE statement to remove a record from the database.

onSubmit with SQL, supports the following settings:

onSubmit.type

Must be SQL

onSubmit.connection

name of the SQL connection - see SQL connections

onSubmit.query

The SQL query you want to execute.

The form values are accessible via the form field names as handlebars. If required, you can use Field Processors to manipulate the data before executing the query.

Example

    - id: createServiceCall
      onSubmit:
        type: SQL
        connection: internal
        query: |
          INSERT INTO DemoServiceCalls (Id, ModelCode, Appointment, Name, Priority, Description)
            VALUES ('{{portrait-timestamp}}', '{{ModelCode}}', '{{now ["format"] [format="yyyy-MM-dd"]}}', '{{Name}}', '{{Priority}}', '{{Description}}');
        filePath: /service-calls/{{portrait-timestamp}}
      dialog: |-
        {

In this given example, the form-data will be inserted into the table DemoServiceCalls via the connection internal. Note that the field Appointment is filled via a field processor statement with the current date.

Handlebars

The query can be written using handlebars.

The variables from the form are provided. In addition, we add the following use of parameters from the user that submitted the form:

`PORTRAIT_USER_ID`	unique user id	`cNkXPLhKjXMFiFYvYvcGOdmHRrTOxesEfcWmoteLGUMyyqslLyEV`
`PORTRAIT_USER_NAME`	name of the user	`Linda Jackson`
`PORTRAIT_USER_EMAIL`	email	`linda.jackson@larsens.portraitapp.co`
`PORTRAIT_USER_ROLE`	assigned role	`ADMIN`