Page Comparison

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 The SQL query does, is up to you. Common use cases would becan be a valid DML statement. For example:

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

DDL Statements are not possible.

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.
onSubmit.fieldProcessor	See https://portrait.atlassian.net/wiki/spaces/PA7/pages/1029112649/Post+Processing+onSubmit#Field-Processors
onSubmit.conditions	See https://portrait.atlassian.net/wiki/spaces/PA7/pages/1029112649/Post+Processing+onSubmit#Conditions
onSubmit.source	Used for Mode UPDATE details see https://portrait.atlassian.net/wiki/spaces/PA7/pages/1029112649/Post+Processing+onSubmit#Mode-Update-(new-for-SQL-and-Python) and to automatically reindex a given source

Example

Excerpt

name	sql marketing

Code Block
- id: createNewCompany

-

id

onSubmit:

createServiceCall

 type: SQL

onSubmit

connection: organigram
    source: demo-organigram

type:

SQL

 fieldProcessor:
      -

connection

field:

internal

Name

query

value:

|

"{{Name}}"
      - field: PARENT_IDS

INSERT

INTO

DemoServiceCalls (Id, ModelCode, Appointment, Name, Priority, Description)

value: "{{PARENT_IDS}}"
      - field: PARENT_LABELS

VALUES

value:

('

"{{

portrait-timestamp

PARENT_LABELS}}

', '{{ModelCode}}', '{{now ["format"] [format="yyyy-MM-dd"]}}', '{{Name}}', '{{Priority}}', '{{Description}}');

"
    query: |
      INSERT INTO DemoOrganigram

filePath: /service-calls/{{portrait-timestamp}}

(Name, PARENT_IDS, PARENT_LABELS)

dialog:

|-

  VALUES (:Name, :PARENT_IDS, :PARENT_LABELS);

{

dialog: config/forms/createNewCompany.json

In this given example, the form-data will be inserted into the table

...

DemoOrganigram via the connection

...

organigram.

The FieldProcessors can either be used to format fields or as a fallback in case of an optional field in the form. If not supplied the Query would fail as there would be no valid parameter :Name if not set previously. With the given FieldProcessor the fallback is an empty String.

Variables

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

...

customized with variables. Available parameters see https://portrait.atlassian.net/wiki/x/DQBwPQ

Versions Compared

Old Version 1

New Version Current

Key

Example

Variables