Logic concepts

Introduction to business logic

Business logic of the application that cannot be modeled can be added using code. This business logic not only comprises traditional business rules but also the logic to control the user interfaces, process flows and messages.

Business functionality can be integrated into various business logic concepts. This can be done in the Functionality screen.

Schematic overview of the various business logic concepts

An overview of the available logic concepts

You can add logic concepts to a subject in the menu User interface > Subjects > tab Default/Variants > tab Settings > tab Performance.

The following concepts are available:

Concept	Function	Activation
Default	Changes the value of fields during import or modification on the basis of values that are entered into other fields. Moves the position of the cursor on the basis of the entered fields.	Is called automatically: - as soon as the user clicks on Add or executes a Task or Report - in edit mode, as soon as a field that has been modified by the user is exited
Layout	Determines the visibility of fields and whether fields are modifiable and mandatory during import or modification on the basis of values that are entered into other fields. Determines the availability of the data manipulation buttons on the basis of the values in the fields of the current record.	Is called automatically: - if another record is selected - as soon as the user clicks on Add or Modify, or executes a Task or Report - in edit mode, as soon as a field that has been modified by the user is exited
Context	Determines which tasks, reports and detail tabs are available, given the values in the fields of the current record. Determines the active detail tab on the basis of the values in the fields of the current record.	Is called automatically: - if another record is selected - in edit mode, as soon as a field that has been modified by the user is exited
Process	Following a process action, determines which following process actions must be carried out and in which sequence. Can make use and exercise influence on process flow variables.	Is called automatically after an action is executed that is part of a process flow.
Trigger/event	Performs controls to possibly reverse transactions, or updates other fields or rows in other tables.	Is called automatically after data in a table is added, modified or deleted. Available for insert, update and delete events, that fire before, after or instead of the event
Task	Standalone (business) functionality.	Is explicitly started by the user or by means of scheduling (batch). A task may also be called from any other concept.
Badge	Determining the number that is shown on the badge of a subject, task or report.	Automatically on the predefined interval and after data modification of the subject in question.
Change detection	Informs the user interfaces during certain events whether or not a subject has been changed and needs to be refreshed.	Activated just before the user interface performs an auto-refresh.
Handler	Overrides the default insert, update, and delete SQL commands with your own business logic.	Handlers are activated if the action is initiated by the GUI or the API (Indicium).
Other	Determines the operation of a subroutine.	Depends on the type of subroutine. The user interface never makes explicit use of subroutines. Other logic concepts can make use of this. There are no application specifications written for this. They cannot be found in the user interface and do not contribute to specs but support other logic.

Default

The following options can be set within a Default:

• The value of each field
• The name of the field where the cursor will be placed

The following context is provided when executing the Default logic:

Parameter
default_mode	Indicates during which mode the Default logic will be executed: - 0 = insert - 1 = update
import_mode	Indicates what action is performed: - 0 = Universal GUI Regular Universal GUI action - 1 = API Import row via API, for CRUD operations, functions, and procedures, executed for a column (cursor_from_col_id is available) - 3 = import
cursor_from_col_id	The field that triggered the Default to be executed. When performing the add or modify action the value is null. A cursor_from_col_id value is only entered if a user manually changed the field value. If the field value is changed another way, for example, by using input parameters in a process flow, the cursor_from_col_id value is null.
cursor_to_col_id	The field where the cursor must move to after leaving the Default procedure. - Windows GUI Only when the Default procedure is triggered by Enter, the focus can move to another field. - Universal GUI Universal GUI uses Tab instead of Enter.
col_id	All columns in the table, both input and output. Every value can therefore be modified.
auto_commit	Initiates a commit without requiring the user to press Execute in the task pop-up or the Save button in the form. When you set @auto_commit = 1 in the Default logic code, the GUI will execute the commit based on the object to which the Default logic is assigned. See Auto commit.

This Default template code ensures that the current date is set for the activated_date field when the activated field has just received the value 1.

if (@cursor_from_col_id = 'activated' and @activated = 1)
begin
  set @activated_date = getdate();
end

The Default logic is not limited by settings in terms of visibility and validity, both at a model level and through authorization.

Auto commit

Universal GUI

The auto_commit feature makes it possible to automatically save the record after filling in a field. This opens up new possibilities, such as, after receiving input, starting a process flow for validations and automatically returning to edit mode.

The Universal GUI supports this in the following contexts:

• Editing a new or existing record after changing a value.
• Directly after add/copy.
• When changing a parameter value of a task or report.
• In combination with auto-edit and auto-save.

Restrictions

• The setting ask confirmation for tasks does not work with auto-commit.
• A task without tsf_send_progress does not show progress during automatic saving.

Layout

A Layout can be used to disable fields and operations, depending on the context. The following settings can be made:

• Make fields read only, invisible and hidden
• Make fields mandatory
• Disable or hide add, update, delete, save and cancel button

The following context is provided when executing the Layout logic:

Parameter
layout_mode	Indicates during which mode the Layout logic will be executed: - 0 = insert - 1 = update - 2 = navigate
import_mode	Indicates what action is performed: - 0 = Universal GUI Regular Universal GUI action - 1 = API Import row via API, for CRUD operations, functions, and procedures, executed for a column (cursor_from_col_id is available) - 2 = export - 3 = import
add_button_type update_button_type delete_button_type confirm_button_type cancel_button_type	Indicates how the corresponding button should be displayed: - 0 = enabled - 1 = disabled - 2 = hidden
[col_id]	All columns of a table. These values can be used to make decisions about the behavior.
[col_id]_type	The type can be modified for each field: - 0 = normal - 1 = read only - 2 = hidden within the form (space remains reserved) - 3 = hidden outside the form
[col_id]_mand	Whether fields are mandatory can be set for each field: - 0 = optional - 1 = mandatory

The variables for type and mandatory only need to be set if the value differs from the default value of the meta-level. It is therefore not necessary to reset the value.

In the following Layout template code, the activated_date field is made mandatory when the user is navigating around the screen and the activated field has a value of 1. The activated_date field is hidden outside the form when the activated field has a value of 0.

if (@layout_mode = 1 /* update */ and @activated = 1)
begin
  set @activated_date_mand = 1; /* mandatory */
end

if @activated = 0
begin
  set @activated_data_type = 3; /* hidden outside form */
end

The Layout logic should always be written in such a way that the fields are stateless. For example, when a field is made mandatory by the Layout logic, this change only applies until the next call of the Layout logic. If the field in the Layout logic is subsequently not explicitly set to mandatory again, the status of the field will revert back to the default setting.

note

The Layout logic does not have the possibility to provide more access than the model and authorization settings allow. For example, when a column in the model is set to hidden, the field cannot be set to read only by means of the Layout procedure. A Layout can only restrict.

Context

A Context procedure offers the following options:

• Disabling and hiding detail tabs
• Disabling and hiding tasks and reports
• Changing the active tab

The following information is given when executing the Context logic:

Parameter
active _ref_id	Displays the name of the active reference tab
[col_id]	All columns of a table. These values can be used to make decisions about the behavior
[ref_id]_type	Indicates whether a reference tab should be displayed - 0 = enabled - 1 = disabled - 2 = hidden
[task_id]_type	Indicates whether a task must be displayed: - 0 = enabled - 1 = disabled - 2 = hidden
[report_id]_type	Indicates whether a report must be displayed: - 0 = enabled - 1 = disabled - 2 = hidden

The following template disables the detail tab inactive_property when the activated field has a value of 1.

if (@activated = 1)
begin
  set @ref_item_inactive_property_type = 1; /* hidden */
end

The Context logic should always be written in such a way that the fields are stateless. For example, when a field is hidden by the Context logic, this change applies until the next call of the Context logic. If the field in the Context logic is subsequently not explicitly set to hidden again, the availability of the field will revert back to the default setting.

note

The Context logic does not have the possibility to provide more access than the model and authorization settings allow. For example, when a detail tab in the model is set to hidden, the detail cannot be set to visible by means of the Context logic. The Context logic can only restrict in this instance.

Badge

Badges can be used to indicate to the user that there are still open tasks or something similar. These are numbers for a table, view, task or report. They can also be made visible in the menu, on detail tiles and on detail tabs.

The following information is available when executing the Badge logic:

Parameter
variant_id	The variant the badge is executed for. The Badge concept is the only variant specific concept. The reason for this is that the badge value is often dependent of the default prefilters, which can deviate per variant.
badge_value	The value that is to be displayed by the GUI. Currently, only an integer can be used with a value between 0 and 99.
col_id	All column values, linked task parameter values, or linked report parameter values that are part of a detail foreign key.

The following template fills the badge value with the number of validation messages or empty when 0.

select @badge_value = nullif(count(1), 0)
from validation
where model_id = @model_id
  and branch_id = @branch_id

This is presented as a number in square brackets in your end application:

Example: badge in tab Validation (Windows GUI)

Process

See Business logic in a process flow in the Process flow guide.

Trigger/event

Trigger or Event based logic is performed around the data manipulation. This logic concept is highly platform dependent. It offers the following possibilities:

• Perform actions as a result of (attempted) changes in data

• Prevent or undo changes in data (control)

In general, the concept can be divided into three types, with three moments per type. The available context is different for each type.

• Instead of / before / after performing a create action - The field values of the record(s) to be added or that have been added.

• Instead of / before / after performing an update action - The old and new field values of the record(s) to be changed or that have been changed.

• Instead of / before / after performing a delete action - The field values of the record(s) to be deleted or that have been deleted.

The following Trigger ensures that when deleting an order, a record is written to the log:

insert into deleted_order_log
select
    d.order_id as order_id,
    getdate()  as deleted_date
from deleted d

Change detection

The logic concept Change detection allows you to inform the user interfaces during certain events whether or not a subject has been changed and needs to be refreshed. This logic concept is only available for subjects, such as tables and views. It is not available for tasks and reports as they have no data.

You can enable it at a subject's configuration (menu User interface > Subjects > tab Settings > tab Performance > checkbox Change detection). Once enabled, the Change detection logic can also be called directly via the API.

warning

Enabling this logic without assigning any code will effectively disable auto-refresh.

The Change detection mechanism is only used in conjunction with the auto-refresh functionality. Every time an auto-refresh is performed, the user interface can call this logic concept to conditionally prevent it.

You can use the code group CHANGE_DETECTION for a control procedure to assign Change detection logic.

A control procedure with code group CHANGE_DETECTION

The logic has the following input parameters:

Input parameter
variant_id	Optional. The variant for which the Change detection logic is called.
last_refresh_utc	Optional. The date and time used for the Change detection. The user interface fills this parameter with the last time the data was refreshed. For the Universal GUI, this is the date and time of the last auto-refresh (null if the data has not been refreshed). For the Windows GUI, this is the date and time of the last manual or auto-refresh.
col_id	Only columns which are part of a detail which is mapped to the subject are available as parameters. Subjects that are never available as a detail have no parameters for columns. To illustrate this, a sales_order_line that is a detail of sales_order will have the sales_order_id column available as a context column. The column will only have a value when the logic is called if sales_order_line is shown as a detail of a sales_order.

note

The last refresh UTC time is determined by the GUI, while the UTC time in the data against which Change detection compares is often determined by the database/server. When these UTC times differ from each other, Change detection might return a false positive or negative time.

The following output parameter is available:

Output parameter
refresh_data	- A bit indicating whether or not a refresh should be initiated by the user interface. Possible values are 0 (no) and 1 (yes). - If no value is set by the logic, the value will be interpreted as 0, indicating no refresh is needed.

System-versioned example

Change detection works extra well with system versioned tables. An example of a template for this logic for SQL Server, for a system-versioned temporal invoice table:

if @last_refresh_utc is null
begin
    -- No refresh was done yet
    set @refresh_data = 1
end
else if exists(
    -- New or modified records
    select * from invoice
    except
    select * from invoice for system_time as of @last_refresh_utc)
    or exists(
    -- Modified or deleted records
    select * from invoice for system_time as of @last_refresh_utc
    except
    select * from invoice)
begin
    set @refresh_data = 1
end

Some warnings when writing this logic:

• If the invoice table (or a variant) has locked prefilters or unauthorized columns, this example might yield false positives for changes in the unauthorized rows or columns, causing unnecessary refreshes.

  • It is not possible to avoid false positives entirely. There is no way to see the currently applied filters or prefilters. Pagination information is also not available.

• Changes to expression columns or lookup translations of the invoice table are not evaluated by this sample logic and may produce false-negative results, preventing timely refresh.

• Changes to the data in the detail tabs of the invoice table (normally included in an auto-refresh) play no role in the decision-making with this example logic. If the parent subject is not refreshed, the details will not be refreshed, either.

Non system-versioned examples

This topic provides two more Change detection template examples for SQL Server that do not use system-versioned temporal tables.

To detect an updated sales order in order to refresh a corresponding main sales order subject:

if @last_refresh_utc is null
begin
    -- No refresh was done yet
    set @refresh_data = 1
end
else if exists(
    -- Updated sales order
    select 1 from sales_order
      where last_updated > @last_refresh_utc
    )
begin
    set @refresh_data = 1
end

To detect a new sales order line in order to refresh a main sales order subject, for example, to see a newly calculated total amount:

if @last_refresh_utc is null
begin
    -- No refresh was done yet
    set @refresh_data = 1
end
else if exists(
    -- New sales order line
    select 1 from sales_order_line
      where last_inserted > @last_refresh_utc
    )
begin
    set @refresh_data = 1
end

Handler

Handler procedures allow you to override the default insert, update, and delete SQL commands initiated by the user interface and Indicium with your own business logic.

A handler can be used best for actions that need to be performed row-by-row, for instance, to delete child records before deleting a parent record. For set-based actions, it is best to use Triggers.

• Handlers only fire when the action is directly initiated by the GUI or API. Therefore, using handlers can be a good way to avoid a cascade of Trigger activation.
• The handlers' single-row approach is well suited for calling other procedures, such as tasks. When using set-based Triggers, a cursor may be required to achieve this. Often, handlers are a better alternative to Instead-of triggers on views modified solely by the UI or API.

The handler procedures are atomic by default, which means all code is run inside a transaction. This ensures that all mutations will either run entirely successfully or fail and roll back to their original state.

The stored procedures for handlers can also be unit tested.

Activate the handler logic

You can activate the handler logic per table and action.

menu User interface > Subjects > tab Default > tab Settings > tab Performance > group Use concept

For each action, you can generate the corresponding default statement.

The following input parameters are available:

Handler	Parameters
Insert	All table columns and possibly identity column as an output parameter. When using an Insert handler on a table with an identity column, you must add the following line to the handler: set @id = SCOPE_IDENTITY() where @id is the column id of the identity column. For more information about the SCOPE_IDENTITY() T-SQL function, see here. When using an Insert handler on a view to insert a record on an underlying table with an identity column, the GUI will refresh and attempt to jump to the newly inserted record. To let the GUI know which record to select, you need to set the identity column of the table as an identity column on the view as well. This will turn the parameter in the Insert handler into an output parameter that can be set using a scope_identity, and thus used by the GUI to select the appropriate row.
Update	All table columns, plus a set of primary key columns to determine which record is updated.
Delete	Only primary key columns.

Exclude columns from handler logic

In handler logic, there might be situations where you do not want to automatically update every column, such as trace columns.

To exclude a column from the default update and insert statements:

menu User Interface > Subjects > tab Subjects > tab Default/Variants > tab Columns > tab Form > tab Performance

1. Select a column.

2. Clear the checkboxes Insert handler or Update handler. In the following cases, you cannot clear the checkbox for the insert handler:

   • The column is a primary key
   • The column is a mandatory field without a default value

3. Re-generate the handler control procedure after updating the checkbox before the changes become visible.

warning

This may break your dynamic model if you use sql select * into #cols from col in combination with a defined insert statement into the #cols. Make sure to verify your dynamic model code and add the new columns if needed.

Optional default statements for handlers

If you have selected one of the handler checkboxes, the Software Factory will add the corresponding default statement when you re-generate the handler.

Initially, a handler will behave just like the corresponding CRUD button. For example, 'delete' means deleting a record in both cases. By deviating from the default in a handler, you can change its behavior. For example, 'delete' means archiving a record instead of deleting it. You can also add your own handler logic. For example, 'delete' means that the record will be deleted and that a checkbox Deleted is selected.

• If you activate a handler for a table, the Auto generate checkbox is selected by default.
• If you activate a handler for a view, the Auto generate checkbox is cleared by default. The automatically generated statement is often not applicable because a view usually consists of more tables.

Checkboxes for automatically generating the default statements

Handler example

In this example, an order is not deleted but instead archived when the user uses the delete-action. A locked prefilter is used to omit archived items for the users.

Using an Instead-of trigger would make it difficult to actually delete orders via logic, for instance, when archived orders have their retention period expire.

update order
set archived = 1,
retention_date = dateadd(year, 1, sysutcdatetime())
where order_id = @order_id

Other

In addition to the logic concepts above, there are platform-specific concepts that are often defined on the basis of a subroutine. These logic concepts can be used in other logic components. Consider, for instance, database functions: the context of this logic is generally consistent with the parameters defined for a subroutine.

View/Snapshot

View and snapshot logic determines the contents of a view or snapshot respectively. This logic can only be applied in template-based views and snapshots. In the template, the complete query is defined for the data that will be displayed by means of the view or snapshot.

When modeling a view the developer provides the columns which must be available in the view. These columns become available when writing the functionality as parameters. All these parameters must also be used in the code.

Template code for a view could then look as follows:

select
  h.hour_id,
  h.project_id,
  h.description,
  e.name as employee_name
from hour h
join employee e
  on e.employee_id = h.employee_id

Generated session variables

When using SQL Server as your RDBMS platform, the following session variables are available to use in your business logic. These session variables can be read from the SQL Server's session context using the SESSION_CONTEXT function.

• tsf_ipv4 - contains the IPv4 address of the client that made the request (null if the connection was established with IPv6). When using proxies, it is necessary to apply the X-Forwarded-For header to get the correct IP address. See Reverse proxy settings for more information.
• tsf_ipv6 - contains the IPv6 address of the client that made the request (null if the connection was established with IPv4). When using proxies, it is necessary to apply the X-Forwarded-For header to get the correct IP address. See Reverse proxy settings for more information.
• tsf_appl_lang_id - contains the language of the user who sent the request. It makes the language available to your end application instead of only to IAM. This session variable can be read by using the following statement: select SESSION_CONTEXT(N'tsf_appl_lang_id'). You could, for example, make a Default stored procedure return a translation or add a translated column to a view without turning it into a lookup column.
• tsf_use_log_session_id - contains the session-id corresponding with the session-id in this requests Session log page in IAM. This session variable can be read using the following statement: select SESSION_CONTEXT(N'tsf_use_log_session_id'). This variable is empty for a public API call.
• guid - contains a guid representation of the tsf_use_log_session_id. It can be read by using the following statement: select SESSION_CONTEXT(N'guid'). This variable is empty for a public API call.
• tsf_appl_id - contains the application ID. You can read this session variable using the following statement: select SESSION_CONTEXT(N'tsf_appl_id').
• tsf_appl_alias - contains the application alias. You can read this session variable using the following statement: select SESSION_CONTEXT(N'tsf_appl_alias').
• tsf_original_login - Universal GUI Windows GUI contains the user who initially logged in. This can be useful when simulating users. You can read this session variable using the following statement: select SESSION_CONTEXT(N'tsf_original_login'). This variable is empty for a public API call.
• tsf_is_public_request - Indicium identifies public requests on the database. It can have the values 0 or 1. See Public API call.