Search for Text within K2 SmartForms Rules

By |

I’m supporting an application that has a large number of rules inside the SmartForms. My task is to change some text. Instead of combing through all of the views and forms to find the text I need to change, I thought I’d try to work a little smarter.

Comparisons, like the kind used in rules, are stored in the K2 database using a
series of tables.

Before I continue, I’d like to offer a suggestion and a warning: the suggestion is to back up the K2 database before you start playing in it. The warning is to make absolutely certain you ONLY execute SELECT statements against the K2 database. If you modify anything, you may need that backup I’d suggested.

Important Database Tables

The database tables which are important to this query are:

  • [Category].[Data]
  • [Form].[ConditionItem]
  • [Form].[ConditionInstance]

 

[Category].[Data]

The [Category].[Data] table houses all of the essential information
about every K2 object you have in the instance. The [ObjectName] column contains its system name — that is, the name it was given when it was originally created. If you make a new view and call it “X”, the system will still know it as “X” after you’ve renamed it to “Y” and later to “Z.” The [DataType] column identifies the type of K2 object it is — form, view, or SmartObject. Finally, the [Data] column contains a GUID that acts as its identifier throughout the system. Note that the column may not be typed as a GUID; in my query, actual GUIDs joining on this table at the [Data] column must first be CAST as NVARCHAR.

Now let’s get into the tables that house the comparison conditions. These are
small XML elements that describe how object “A” is to be compared to object “B” — for example, whether the field txtDescription contains the value “slappy,” or the “ID” column in the currently selected list row is equal to 1.

[Form].[ConditionItem]

The examples I gave in the preceding paragraph describe very neatly for us a condition item: a single comparison of an object to another object, like the “ID” column in the currently selected list row being equal to 1. The table has two XML columns — one called [Properties] and another called [Expressions] which contain the salient code describing the conditions.

The [Property] column has data that resembles this:


<Property>
  <Name>Location</Name>
  <Value>View</Value>
</Property>
<Property>
  <Name>Name</Name>
  <Value>AdvancedCondition</Value>
</Property>

The [Expression] column has data that details the comparison:


<And>
  <NotEquals>
    <Item SourceType="ViewField" SourceID="c3ba4798-9bfb-4463-fe09-a1bd9773a841" DataType="Text" />
    <Item SourceType="Value" DataType="Text">
      <SourceValue xml:space="preserve">PMO</SourceValue>
    </Item>
  </NotEquals>
</And>

These two columns are the ones we’re actually mining in the completed query.

[Form].[ConditionInstance]

The [Form].[ConditionInstance] table essentially sequences all of the individual [ConditionItem] records in the correct order, and associates the conditions with the correct K2 object. [ConditionInstance] is the where to [ConditionItem]‘s what.
Important columns in this table are ContextID, which identifies the K2 object (this column joins on [Category].[Data].[Data], and IsEnabled, which refers to whether the rule has been disabled.

The Query

The query, in its current state, will return the names and datatypes of K2 objects whose associated enabled conditions contain your search string.

If you want to see the actual XML values, feel free to uncomment the [Property] and [Expression] columns. You’ll have to remove the DISTINCT keyword from the query if you choose to include those columns, and run the query in Microsoft SQL Server Management Studio.


USE [K2]
GO

/* Find the objects that contain specific strings in expressions */
DECLARE @strFind NVARCHAR(100);

SET @strFind = 'Find me'; -- change this value to your search string

SELECT DISTINCT
  CD.[ObjectName] AS [ObjectName],
  CD.DataType AS [DataType]
  --CI.[Properties].query('(/)') AS Property,
  --CI.[Expressions].query('(/)') AS Expression
FROM [Form].[ConditionItem] CI
JOIN [Category].[Data] CD
  ON (
  CD.Data = CAST(CI.ContextID AS NVARCHAR(250))
  )
JOIN [Form].[ConditionInstance] CN
  ON (
  CI.ID = CN.ConditionID
  )
WHERE (
  CN.IsEnabled = 1
  )
  AND
  (
  CI.[Expressions].value('(/)', 'nvarchar(max)') LIKE '%' + @strFind + '%'
  OR
  CI.[Properties].value('(/)', 'nvarchar(max)') LIKE '%' + @strFind + '%'
  )
ORDER BY CD.ObjectName, CD.DataType ASC