Administration Guide

SQL Expressions and SQL Scripts as Assigned Values in Update Field Operations

When configuring Functions or defining the Programming section of Actions and Triggers, you can add the Update Field operation to assign a value to an object field. Even though you are free to provide a specific value, using expressions and scripts you can perform calculations that involve other fields, parameters, and static values.

INFO: For details on Update Field operations, see Update Field.

The following example demonstrates two simple SQL scripts for the Update Field operation assigning a value to the Knowledge Base Article's Version field.

By default, new KB Articles have the initial two-part version number 1.0. This version is expected to be incremented whenever the article gets updated. You can use these scripts to increment the version number automatically, so your technicians do not have to change this number manually when they update KB Articles.

  • The first script increments the minor number of the KB Article version (e.g. 1.0 → 1.1, 12.12 → 12.13, etc.):

    select CAST( FLOOR(CAST(%[DBF Version]% as float)) as nvarchar(10) ) + N'.' + CAST( CAST(SUBSTRING(%[DBF Version]%, CHARINDEX('.', %[DBF Version]%) + 1, LEN(%[DBF Version]%) - CHARINDEX('.', %[DBF Version]%)) as int) + 1 as nvarchar(10) )

  • The second script increments the major number of the KB Article version (e.g. 1.3 →2.0, 2.12 →13.0, etc.):

    select CAST( FLOOR(CAST(%[DBF Version]% as float))+1 as nvarchar(10) ) + N'.0'

INFO: SQL scripts contain Transact-SQL statements. For details, you can see the Transact-SQL Reference for Microsoft SQL Server 2017 at https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017.

NOTE: It is always a good practice to put repeating operations into a Function. For details, see Creating Functions. Alternatively, you may want to create a SQL script macro. You can use this macro in Update Field operations of your Functions or Actions, assigning it as the value of the corresponding field. For details, see Creating User Macros.