The "Change items list values" action

Applies to version: 2021.1.x and above; author: Konrad Wojtycza

Introduction

The “Item list” form field is used to store any number of data rows organized into columns. In business practice, there is a need for the item list to be updated based on the values from other elements or updated automatically, and a user cannot edit it. For this purpose, you can use the “Change items list values” action which will be presented in this article based on the simple “Invoice approval” workflow.

Fig. 1. The "Invoice approval" workflow

The "Change items list values” action can work in three modes:

• Add new values - allows you to add new rows to the existing item list
• Update values - allows you to change the values of each row of the item list in the selected columns. If the row with a given key is not found, it will be added as a new row.
• Replace values - clears all rows of the item list and completes this list with new rows with the given values in the selected columns.

Below, we will describe the configuration and operation of the individual action modes.

Configuration

The "Change items list values” action will be used in two lists – Analytical breakdown (Fig. 2.) and History of approval (Fig. 3) after following the selected paths.

Fig. 2. The "Analytical breakdown" item list

Fig. 3. The "History of approval" item list

A) Add new values mode

The "Change items list values" action with the “Add new values” mode was placed on the Approval step at the Accept path (the action adds information about approving the analytical breakdown line), and at the Reject path (information about rejecting the analytical breakdown).

Fig. 4. The Approval step

Configuration of this mode:

• Change type – select the “Add new values” mode.
• Unique value – indicate a unique value that identifies the row of the item list. According to the item list definition, it is the DET_ID column, it is also recommended to indicate such a column in this field.
• Data source – indicate a connection to the current database.
• SQL query – the query should contain the values that will be placed on the item list with indicating a column to which these values will be passed, in the form:

Select ‘Value’ as [Column name in the database]

‘Value2’ as [Column name in the database2]

‘Value3” as [Column name in the database3]

• Unique value mapping – indicate a value from the SQL query that should be treated as unique, and select this value on the item list.
• Columns mapping – fill in the mapping of the values from the SQL query with the columns of the item list.

Values can be defined with the expression editor. In the presented example, the value of DET_ID is defined as NULL to allow auto-incrementing the value of the DET_ID column and adding the row as the last row in the item list.

Values of the current user, current date, information about approval, and last comment text are passed to the remaining columns. Values passed to the item list can be dynamically generated based on the value from the form or entered statically into the SQL query.

The new row will be added to the "History of Approval" item list after following the Accept or Reject path.

Fig. 5. The configuration of adding value to the History of approval" item list

B) Update values mode

In the Update values mode, indicate the line/lines of the item list that should be updated in the SQL query. As in the previous mode, select the column that stores the unique value of the item list – DEF_ID. In the case of the following query, it is the same column. In the presented example, the value of the Approved column will be updated in the row where the current user is the approver.

Fig. 6. The configuration of adding the value to the "Analytical breakdown" item list

The effect of the action after following the Accept path is visible on Fig . 2 .

C) Replace values mode

The configuration of the Replace values mode is analogous to the previous modes. This mode allows you to completely replace the current values on the item list with values indicated in the SQL query.  

Fig. 7. The configuration of value replacement in the "Analytical breakdown" item list

The presented query (Fig. 7) would only return values if the condition 1=2 is met, which is a contradictory assumption and such query will never return a value. As a result, all rows in the Analytical breakdown item list will be cleared (Fig . 8 ). 

Fig. 8. The "Analytical breakdown" item list after using the Replace values mode
 

Summary

The "Change items list values" action is a useful tool that allows you to edit the value of the item list. The available three modes of this action provide full possibilities of automatic and configurable modification of the item list, without the participation of the user filling out the form.