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:
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:
Select ‘Value’ as [Column name in the database]
‘Value2’ as [Column name in the database2]
‘Value3” as [Column name in the database3]
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
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.