SQL

A very common use case is, is to store the data of https://portrait.atlassian.net/wiki/spaces/PA/pages/98554 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 https://portrait.atlassian.net/wiki/spaces/PA/pages/93061161

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 https://portrait.atlassian.net/wiki/spaces/PA/pages/122224641 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

Copyright Treskon GmbH.