Queries

To enable a workflow to determine which list elements are to be processed, a corresponding query of these list elements must be sent to SharePoint.

CAML

SharePoint experts know the SharePoint query language as "CAML" (Collaborative Application Markup Language), an XML-based structure that describes which elements SharePoint returns to kenaflow and then get processed by it, by calling the workflow scripts for each of these elements.

CAML is very complicated. Here you will find an introduction to the topic:

https://msdn.microsoft.com/en-us/library/ms467521.aspx (Englisch)

You can specify CAML queries in kenaflow, but you must ensure that the CAML syntax is correct.

Moderation State

If you want to filter for the SharePoint "ModerationState" field than you have to be careful. The string in this field is localized. In a multi language environment you will have problems with this: <Eq><FieldRef Name="_ModerationStatus" /><Value Type="ModStat">Approved</Value></Eq>.

Instead use this CAML: <Eq><FieldRef Name="_ModerationStatus" /><Value Type="ModStat">0</Value></Eq> with these values:

  • 0 = Approved
  • 1 = Rejected
  • 2 = Pending
  • 3 = Draft
  • 4 = Scheduled

kenaflow Query Language

Furthermore, kenaflow offers its own query language, which was kept very simple. This allows for the most common queries to be built.

Each query is composed of individual or combined sections of the following structure:

<sharepoint-field> <logical-operator> <data-type> <comparable>

For exemple:

{{Title}} == string "Element"

(This query returns all SharePoint list items whose title is "Element" exactly.)

Various such sections can be linked with the logical operators & for AND-operations or | for OR-operations.

{{Title}} == string "Titel 1" | {{Title}} == string "Titel 2"

(This query returns all SharePoint list items whose title is exactly "Title 1" OR "Title 2".)

Parentheses can and must also be used to make the query meaningful.

({{NumberField}} == integer 0 | {{NumberField}} == int 1) & {{Title}} != string 'Element'

(This query returns all SharePoint list items whose title is NOT "Element" (!=) and has the value 0 or the value 1 in the "NumberField" field.)

The following logical operators are possible:

Operator Meaning Description
== Equality operator Both sides must be equal.
!= Inequality operator Both sides must be different.
<= Less-or-equal to operator The left side must be smaller than the right side. Or both sides are equal.
>= Larger-or-equal operator The left side must be larger than the right side. Or both sides are equal.
< Less operator The left side must be smaller than the right side.
> Larger operator The left side must be larger than the right side.
|= Starts-with-operator The string of the left side starts with the string of the right side.
~= Contains operator The string of the left side is contained in the string of the right side.

There is no "Ends With" operator in SharePoint.

When editing a "Multiple lines of text" column with the addition "Rich Text" in SharePoint, the operators ==, != and |=may reach their limits. For an alignment of strings, the operator ~= should be used. For proper use of the operators, it is recommended to use the "Text only" option or the "Single line of text" column.

On the left side, only fields of the SharePoint list, on which the workflow works, can be used. You must note that the "technical" field names are used here, which do not always match those in the SharePoint interface. If necessary, you can find out the technical name of a field by clicking on the field in the list settings and writing out the field name in the URL.

On the right side, the data type must be specified first:

Data type Description
string The right side of the comparison is a string.
int The right side of the comparison is a number.
date The right side of the comparison is a date without time in the time zone of the website. The format for the following value must always be: yyyyy-MM-dd'T'00:00:00'Z'.
dateutc The right side of the comparison is a date without time but in the time zone UTC. The format for the following value must always be: yyyy-MM-dd’T’00:00:00’Z’.
datetime The right side of the comparison is a date with a time in the time zone of the website. The format for the following value must always be: yyyyy-MM-ddTHH:mm:ssZ.
datetimeutc The right side of the comparison is a date with a time specification in the time zone UTC. The format for the following value must always be: yyyyy-MM-ddTHH:mm:ssZ.
userid The right side of the comparison is the ID of a user on the SharePoint site.
bool The right side of the comparison is a boolean. Allowed for the following value are: TRUE or FALSE or 0 or 1
lookupid The right side of the comparison is the ID of a value that appears in a lookup field.
lookuptext The right side of the comparison is the text of a lookup field.
modstat The right side of the comparison is a number 0 to 4. THIS IS ONLY VALID FOR THE FIELD _ModerationStatus OF SHAREPOINT
null For querying empty or non-empty fields. See below.

The right side of the comparison can be one of the following options:

  • a fixed value, e.g. "element title" for a string;

  • a dynamic value, for example [[MyTitleField]] for a string, that comes from the workflow configuration stored in SharePoint.

  • [[Today]] for the current date and time.

  • [[UserId]] for the SharePoint ID of the current user. The "current User" is, however, in case of execution of a query in a workflow script of the workflow user, that is, the account in whose context kenaflow is currently executed.

If the data type null is used, only the operators == and != are allowed. No value is specified after null. This can be used to check whether a field is empty (==) or not (!=).

Example:

{{NumberField}} == null & {{Title}} != null

The kenaflow query language is internally translated to CAML.

You can use the ConvertTo-KFCAML cmdlet to view the result of the translation.

To work with queries in kenaflow's own language and with CAML, kenaflow provides the following cmdlets:

+ [`ConvertoTo-KFCAML`](/scriptreference/cmdlets/convertto-kfcaml)
+ [`Get-KFNormalizedCAML`](/scriptreference/cmdlets/get-kfnormalizedcaml)

Discussion