Administration Guide

Conditions and Expressions

On this page

A condition decides whether a workflow item or a group of operations runs. Build it from comparisons grouped with AND, OR, NOT AND, or NOT OR. Nested groups specify the order of evaluation.

An expression is what a workflow value holds: a text whose placeholders are filled in, a SQL expression or script run on the database, a PowerShell script, or the return value of a workflow function. The value boxes of operation dialogs, such as the Update Field operation dialog and the Assign Parameter dialog, take expressions. See Operations Reference for the operations themselves.

You write conditions and expressions in the Settings App, in the Criteria, Condition, and Build Expression windows described under In the Settings App.

In the Settings App

For where workflow items and their editors sit in the Settings App, see Settings Map.

Build a condition

The Criteria window shows the condition as a tree. It opens on a single group row that reads AND, and carries Add Condition, Edit Condition, Add Group, Delete Row, Clear All, Move Up, Move Down, and Preview. Group rows are drawn in red.

  1. To add into a group, select that group's row. Add Condition adds a condition after the last condition of the selected row's group. Add Group adds a new group inside the selected row when it is a group row, and beside it when it is a condition row.
  2. Add Condition opens the Condition window at once, and the new row reads <new condition> until the window returns. Fill in the condition (see Choose a condition type) and click OK. If you cancel, the new row is removed.
  3. To change a group's operator, pick AND, OR, NOT AND, or NOT OR from the drop-down in the group row. Pressing Enter on a group row opens that list.
  4. To change a condition, click Edit Condition, double-click the row, press Enter on it, or click its row button.
  5. To reorder conditions, use Move Up and Move Down. A group row cannot be moved.
  6. To remove a row, click Delete Row. It asks for confirmation for a condition row or a group that still holds rows; an empty group row is removed without a prompt. Deleting a group row removes every row inside it. Deleting a group's last condition also removes every group that is left empty above it, up to but not including the root group. The root group row cannot be deleted.
  7. To remove everything, click Clear All. It always asks for confirmation.
  8. To read the criteria as one line, click Preview. The line uses the same words as the tree rows, and it is the same one-line summary the workflow program tree shows beside an IF. The button becomes Editor, which returns to the tree.
  9. Click OK. Where the criteria are required, OK refuses a tree with no condition row with "Please specify the criteria."

Del deletes the selected row, and Ctrl+Up and Ctrl+Down move a condition. The tree's right-click menu carries Add Condition, Add Group, Delete Row, Move Up, and Move Down, with these shortcuts shown.

Neither the number of conditions in a group nor how deeply groups nest is limited.

Choose a condition type

The Condition window lists the condition types on the left: Standard, Compare Two Date Fields, SQL Criterion, Check Relationship, and PowerShell Expression. Check Relationship is not offered on a service object, and PowerShell Expression is offered only for a function's condition. Each type has its own page of controls.

Type What it compares
Standard A field against a value, another field, a parameter, or a variable.
Compare Two Date Fields The interval between two date fields, or between one and the current moment, against a number of minutes, hours, days, weeks, months, or years, optionally through a work calendar.
SQL Criterion An SQL fragment you write, with field placeholders.
Check Relationship Whether an object is related to another object or class.
PowerShell Expression A script you write that must return one Boolean value.

Standard

The page carries Field:, Operator:, and Compare with:, where Compare with: is either Value or Field. The box beneath is a value editor built for the chosen field's type, labeled Value:, or, when Compare with: is Field, a field picker labeled Field: that offers the fields comparable with the first one. Both field boxes are read-only; their ellipsis button opens the Select Placeholder window. See Operators for the operators each field type offers.

  • The Field: box on the left offers no user macros. Old field values appear only where the workflow item keeps them. Activity fields appear only in the criteria of a create or modify trigger, not in an On-Condition trigger's criteria. Attachment parameters appear only in a function's condition. Image fields are always offered.
  • In a Create Action's condition, the object's own fields are not offered: a child object offers its parent's fields under Parent Object Fields, and any other object offers no object fields.
  • Encrypted fields are not offered in the criteria opened from a workflow item's own Criteria button. Every other condition offers the encrypted fields of the condition's own object, never those of a related table.
  • Compare with: is fixed to Value until a field, a parameter, or a variable is chosen on the left, and for operators that cannot compare against a field: the operators that take no value, the four day operators, and the two permission operators.
  • Five groups of operators have an editor of their own instead of the value editor: the two calendar-coverage operators take a Work Calendars lookup, the two technician working-hours operators take a Technicians lookup, has permission to and does not have permission to take a list of the item's own actions, is member of and is not member of take a User Groups lookup whose first entry is <any group>, and the four day operators take a number of days, shown with day(s) for in the past by no more than and in the future by no more than and with day(s) or more for is before today by and is after today by. When you compare against a field under the calendar-coverage, working-hours, or member operators, the field picker offers the Work Calendars, Technicians, or User Groups fields.
  • OK stays unavailable until the side in use holds something.

When the left side is a field, an old field value, or an activity field, the operator is does not equal, does not contain, does not begin with, or does not end with, and the field is not Boolean, read-only, or mandatory, a hint icon appears beside the operator. Its text reads "This operator gives a positive result only when the field value is not blank (not "null"). To handle possible blank values, add a condition with the "is blank" operator. Learn more here." The here link opens the online help.

Compare Two Date Fields

The page carries Date 1: and Date 2:, a direction list, a modifier list, an amount, a unit list, and an Options group with Do not use any Work Calendar, Use this Work Calendar:, and Use the default Work Calendar. See Work Calendars.

  • Date 1: and Date 2: offer date and date/time fields and the current moment, shown as Now. Only one of the two can be Now: setting one clears the other.
  • The amount is a number greater than zero, or a placeholder chosen from its box, which then shows underlined and read-only. The box's ellipsis offers only integer fields. Pressing Delete or Backspace in the box while it holds a placeholder sets it back to 1. A number that is not greater than zero is refused with "The entered time period is not valid. You can only enter numbers greater than zero."
  • With Do not use any Work Calendar, the units are Minute(s), Hour(s), Day(s), Week(s), Month(s), and Year(s). Either work calendar option narrows the list to Minute(s), Hour(s), and Day(s), and a coarser unit already chosen falls back to Day(s). Turning the calendar off again does not restore the unit you had picked.

How the amount is measured:

  • Without a work calendar, by at least, by more than, by at most, and by less than compare Date 1 with Date 2 shifted by exactly the amount. For example, is before by at least 2 Hour(s) is not met by a 90-minute gap. by at most and by less than also require Date 1 to lie before Date 2 (after it, for is after).
  • Without a work calendar, by exactly matches any Date 1 inside the whole clock minute, clock hour, calendar day, week, month, or year that lies the amount before (or after) the one that holds Date 2. is before by exactly 2 Hour(s) matches any Date 1 in the clock hour two before the hour of Date 2.
  • With a work calendar, minutes and hours are counted as working minutes, exact to the minute. Days are counted as whole working days from the date of Date 2, so a day amount compares dates, not 24-hour spans.

SQL Criterion

The page carries Name:, Expression:, and a Build Expression... button. OK stays unavailable until both boxes hold text. The Criteria window's row for this condition shows the name, not the expression.

  • Write the expression as a where clause without the word where. It is checked against the condition's own table, so an unqualified column refers to that table, and any other table has to be reached through a subquery. A condition with no table of its own, or on a service object such as a Mail Connector or a Scheduled Task, is checked against a single stand-in row.
  • Build Expression... opens the Build Expression window fixed to this type, with no Expression Type: choice and the line SQL logical condition.
  • The expression box's right-click menu offers Insert Placeholder..., which inserts a placeholder at the cursor, and Placeholder Definition..., which opens the definition of the user macro or global workflow parameter under the pointer.

See Validate your work for what OK checks.

Check Relationship

The page starts with a label that names the object you are authoring the condition for, such as Current Incident, then the operator list (is related to or is not related to), Object Class:, Object:, and Relationship:.

  • Object Class: lists the workflow objects that may take part in a relation and whose class is licensed, sorted by name, with <any object>. A new condition starts at <any object>, which empties and disables Object:.
  • Object: takes a picked object or a SQL expression. Its builder opens the Build Expression window fixed to SQL expression.
  • Relationship: lists <any relation> first. It is not narrowed to the class chosen above: a named class offers the same relationship types as <any object>.
  • For a Person's condition with the Groups class chosen, or a Group's condition with the Persons class chosen, choosing <any relation> shows the line "<any relation>" does not include "Member of".
  • OK refuses an incomplete page with "The object class for relation is not specified." or "The relation is not specified."

PowerShell Expression

The page carries Name:, Expression:, and a Build Expression... button. OK stays unavailable until both boxes hold text.

  • Write a script that returns a single Boolean value. See PowerShell context for where and how it runs.
  • Build Expression... opens the Build Expression window fixed to this type, with the line PowerShell logical condition.
  • The expression box's right-click menu offers Insert Placeholder... and Test PowerShell Script (Experimental).

Write an expression

A workflow value box carries a small icon button to its right that opens the Build Expression window. The button is hidden when the box accepts only a plain value, and unavailable when the box is read-only. In a box that also accepts a plain value, pressing Backspace or Delete in the expression clears it and returns you to the field's own editor.

The Build Expression window shows, from top to bottom:

  • A band with Object Class: and the name of the object in bold, and Set expression for: with a line naming the value, where both apply.
  • The Expression Type: choice, with Text, SQL expression, SQL script, and PowerShell script, where the box offers a choice. A box that takes only a SQL expression opens the window fixed to SQL expression, with no Expression Type: choice.
  • A prompt line above the box that says what you are completing, where one applies. Opened from a lookup field's Filter: box, it reads select * from <lookup table> where, you write the rest of that statement, and OK checks it against the lookup's table. See Filters and Parameters.
  • The expression box, with a button hinted Open in the new window that opens the text in a larger editor titled after the current type. What that editor returns on OK replaces the text.
  • The placeholder picker, with Category:, Item:, SubItem:, Description:, a read-only Placeholder: box, and Insert.
  • OK and Cancel.

To write an expression:

  1. Under Expression Type:, choose the type. The choice offered depends on the field:
    • Text is offered only for a character field (string, memo, or encrypted) that is neither a lookup nor multi-valued. For an HTML field it reads HTML and the box becomes an HTML editor. Switching away from HTML keeps the text as its plain text.
    • SQL expression is not offered for a field that holds an object, which leaves SQL script and PowerShell script.
    • The window starts on Text for a text field that does not already hold an expression, SQL script for a multi-valued field, PowerShell script for a field that holds an object, and SQL expression otherwise.
  2. Type the expression. To insert a placeholder, pick it in Category:, Item:, and, where one applies, SubItem:, and then click Insert or double-click it, which in a plain box replaces the selected text and leaves the placeholder selected, or drag it into the box, where it lands at the character under the pointer. Image fields and HTML user macros are offered only for an HTML field. Insert is unavailable while nothing with a placeholder is selected, for an item the picker marks as unresolvable, and for a field reached through a lookup where the box does not allow lookup fields. When the cursor or selection sits on a user macro or workflow parameter placeholder, the box's right-click menu offers Placeholder Definition..., which opens that macro's or parameter's own window.
  3. Click OK. The expression is checked by its type; see Validate your work.

Choosing SQL expression, SQL script, or PowerShell script makes the value a calculated one. Choosing Text or HTML turns it back into a text value with placeholders filled in. An expression left empty in a box that also accepts a plain value clears the box.

While a PowerShell box has the focus, whether the Build Expression box with PowerShell script chosen or the Condition window's PowerShell Expression box, its right-click menu offers Test PowerShell Script (Experimental). It checks that PowerShell is available, opens the Settings App's PowerShell console with the box's text, and writes the console's text back into the box when you leave the console and choose to save.

Validate your work

OK checks a SQL expression, a SQL script, and a SQL condition by running it against the database with a sample value of its type in place of each placeholder (1 for a number or a Boolean, {ts'2000-01-01 00:00:00'} for a date, 'string' for text, and an all-zero GUID for an identifier), inside a transaction that is always rolled back. The check has a 60-second timeout. It proves that the statement parses and runs, not that it returns what you meant. Checking a SQL script runs the script.

  • When the check fails with a database error, a warning shows "Expression validation error:", the server's message, and "In some cases, the actual expression syntax can be determined only at runtime, so it may run correctly. Are you sure you would like to save your expression?" Yes saves the expression as written. On the SQL Criterion page, No keeps the window open.
  • A foreign-key violation counts as a pass.
  • A placeholder or macro the checker cannot resolve stops OK with no override, before any statement runs.
  • On a service object, such as a Mail Connector or a Scheduled Task, a user macro whose text includes an object field, old field value, or activity field placeholder is refused with "The [macro] macro is not allowed here. The macro includes a placeholder for the [field] object field, which cannot be resolved outside the context of an object."
  • Where a box accepts a list of values, a query that returns more than one column is refused with "The SQL expression returns more than one column. It must return only one column." with no override. A query whose column has another type raises "The SQL expression returns data of an unexpected type. Expected data type: type." in the overridable warning.
  • On Text, text that begins with SELECT asks "Is this text a SQL-expression?" Yes checks it as a SQL value and switches the type to SQL expression when the check passes; No checks it as text.

A PowerShell script is never run or syntax-checked by OK, on the Build Expression window or on the PowerShell Expression page: only its placeholders are checked. A script with invalid PowerShell syntax saves, and an unresolved placeholder stops the save.

Empty expressions:

  • In the Build Expression window, an empty condition is always refused with "The expression is not specified." An empty SQL expression or SQL script is refused the same way unless the window was opened from a value box or from a field's Filter: box, which always allow an empty expression. An empty Text or PowerShell script is not refused.
  • On the SQL Criterion and PowerShell Expression pages, OK stays unavailable until the name and the expression both hold text.

Closing the Build Expression window any way but OK after an edit asks "Do you want to save the changes?" Yes does not run the check OK runs: a Text, HTML, or PowerShell script expression closes unchecked, and a condition gets the SQL command check instead of the condition check. A SQL expression, SQL script, or condition closes only when its check passes. No discards the edit, and Cancel keeps the window open.

Where conditions appear

Five different things in workflow are called a condition, and all of them use the same criteria format and the same Condition window:

  • A workflow item's own condition.
  • A program group's condition.
  • A form section's condition.
  • The criteria of an Act on Related Object step.
  • The selection criteria of an On-Condition trigger. See Workflow Overview.

How a condition is evaluated

Where a condition is evaluated decides whether it sees the current object of the run through the values held in memory or through the database:

  • A program group's condition and a create or modify trigger's condition are evaluated while the program runs, before anything is written. References to the current object's own non-calculated fields take their values from the object in memory, which is why a create trigger can test an object that has no row in the database yet.
  • Every part of a condition that must read a table reads the committed database: a lookup or parent-object reference, a SQL Criterion, and the criteria of an Action or an On-Condition trigger, which are run against the object's row.
  • An On-Condition trigger's criteria first run as a selection over the class's table, and each returned object is then tested again on its own before the trigger's program runs for it. An On-Condition trigger whose condition is empty does nothing.
  • After-Save triggers are evaluated after the run's changes are committed, and see everything the run wrote.

NOTE: An object that a Create Object step made earlier in the same run is not in the database until the run's changes are committed, so no part of a condition that reads a table finds it. An Act on Related Object step that follows a parent, child, or sibling reference, has no criteria, and whose reference does not go through a join table is the exception: besides the objects its query finds, it also reaches the objects the same run has created or changed and not yet written. With any criteria or a join-table reference, it acts only on what its query returns.

A PowerShell Expression node never matches in the condition of an Action, an On-Condition trigger, or an After-Save trigger, or in the criteria of an Act on Related Object step: there it is not run and counts as false. The Condition window offers this node only for a function's condition.

Operators

Which operators a Standard condition offers depends on the type of the field on its left side. Six operators are common: equals, does not equal, is blank, is not blank, has been changed, and has not been changed.

Field type Operators offered
Lookup (not a character type) The common six, is unique, is not unique
Numeric The common six, is less than, is less than or equal to, is greater than, is greater than or equal to, is unique, is not unique
Character The common six, contains, does not contain, begins with, does not begin with, ends with, does not end with, is unique, is not unique
Memo As for Character, without is unique and is not unique
Date/time The common six, the four inequalities, is today, is covered by calendar, is not covered by calendar, is working hours of technician, is not working hours of technician, is yesterday, is tomorrow, the nine last, this, and next week, month, and year operators, in the past by no more than, in the future by no more than, is before today by, is after today by
Boolean The common six
Multi-select The common six, contains, does not contain
Image is blank, is not blank
Attachments is blank, is not blank
Attachment files of an object contains, does not contain
Mail priority lookup, and any other type The common six

These rules then add or remove operators:

  • The asset tag field also offers is system wide unique and is not system wide unique.
  • A lookup to Persons also offers is member of and is not member of, and, when the item you are authoring has at least one action to name, has permission to and does not have permission to. See Permissions Reference.
  • A lookup to the locale list also offers the character operators.
  • A calculated field does not offer is unique and is not unique.
  • A field of a virtual object does not offer is blank, is not blank, has been changed, has not been changed, is unique, and is not unique.
  • When the left side is not an object field or an old field value (a parameter, a variable, or another value), has been changed, has not been changed, is unique, and is not unique are not offered.
  • has been changed and has not been changed are not offered for a field on a parent object, an old field value, a calculated field, or where no old values exist.

A Compare Two Date Fields condition has its own vocabulary: a direction, is before or is after, then a modifier, by exactly, by at most, by at least, by less than, or by more than, then an amount and a unit. A Check Relationship condition uses is related to and is not related to.

Expression types

A value in workflow is one of six expression types:

Type How it is evaluated
Text Not evaluated; placeholders are filled in.
SQL expression Run as a scalar SQL expression.
SQL script Run as a SQL batch.
SQL condition Run as a where clause, where a condition rather than a value is wanted.
PowerShell script Run as a PowerShell script. See PowerShell context.
Function The value is the return value of the workflow function chosen for it.

Placeholders are filled in before the expression is evaluated, and a placeholder value is escaped for the language the expression is written in, for example a quote in a value that goes into a SQL expression or a PowerShell script. See Macros Reference for placeholders and user macros.

You do not write the select around a SQL expression: the expression is run as the value of a select, so it can be a bare scalar expression such as 1 + 2 or a whole select statement that returns a single value. A SQL script runs as written, and its value is the first column of the first row of the last result set it returns, or, for a multi-valued field, the first column of every row, joined with ;. An expression that comes out empty gives a blank value. The result is converted to the type of the target field, and a result of a type the field does not accept raises a type-mismatch error.

A SQL expression, SQL script, or SQL condition is Transact-SQL, run on the database connection the application running the workflow already uses, under that connection's account. That account is the same for every author and every technician. A colon in the text is ordinary text.

A SQL expression or SQL script has a command timeout of 600 seconds, and so does the check of a workflow item's own condition. The check of a program group's condition or of a create or modify trigger's condition, and the statements of an Execute SQL step, take the timeout of the application's own database connection: 3600 seconds in the Web App, the Self Service Portal, and the REST API, and 600 seconds on the Automation Server. A statement that runs past its timeout fails like any other SQL error.

PowerShell context

A PowerShell script, whether a PowerShell Expression condition or a PowerShell script value, runs inside the application that is executing the workflow item, never on a dedicated server. The Settings App, the Web App, the Self Service Portal, the REST API, and the Automation Server all run workflow scripts.

A script runs under the identity of that application's process, and is never elevated. A script started from the Web App, the Self Service Portal, or the API runs as the IIS application pool identity of that application, and one started from the Automation Server or an agent runs as that service's account. The author of the workflow item and the technician who started the run have no bearing on it.

One PowerShell session is used for a whole workflow run, and it is reset before each later script. A script must publish its answer in a variable named $Result. In a condition, the script must return a single Boolean value; anything else is rejected with "PowerShell condition error:" and "Invalid condition. PowerShell expression must return a single Boolean value."

A script's output comes back as a string, an integer, a floating-point number, a Boolean, or a date/time value. Anything else comes back as an object whose properties and collections an expression can walk with a dotted path, and which an Iterate Collection step can loop over. Reading a property that does not exist, or whose value is an error, raises an error instead of returning a blank value. A GUID result is written in braces and upper case, not in PowerShell's own lower-case format.

A script is stopped when it runs past its timeout, with the message "The script execution was terminated because the timeout period has expired":

Where the script runs Timeout Configurable
An expression or an operation 600 seconds by default Yes
A condition 30 seconds No
The Settings App's PowerShell console 60 seconds No

Before the first script, the application checks that PowerShell 3.0 is present and that the product's PowerShell component is registered. It checks once per process. In the Settings App, a failed check reports the problem on your computer; in any other application, the message names the application the script runs in. A missing registration tells you to run that application's installer in Repair mode.