Question

Find usages of a K2 Object in rules

  • 11 August 2020
  • 1 reply
  • 102 views

Userlevel 2
Badge +4

Thought this might be useful for someone...

 

We're moving to K2 Cloud and I have a SmartObject that needs to be updated. K2 will tell you which views are built on a SmartObject but not the SmartObjects that are used in the rules. 

 

This SQL will return the usages to you but if you change the last line to a guid like a View or Form GUID, it will show the usages of those objects as well.

 

SELECT [ContextID], [view].NAME [View], Form.Name Form, A.DisplayValue, Value
FROM
(SELECT [ContextID], prop.query('data(Value)') as Value, prop.query('data(DisplayValue)') as DisplayValue
FROM [Form].[ActionItem] A
CROSS APPLY Properties.nodes('Property') as x(prop)) A
LEFT JOIN [Form].[View] ON [View].ID=A.ContextID
LEFT JOIN [Form].[Form] Form ON [Form].ID=A.ContextID
WHERE CONVERT(VARCHAR(MAX),A.VALUE)=
(SELECT TOP 1 CONVERT(VARCHAR(38),SmartObjectGUID)
FROM SmartBroker.SmartObject
WHERE DisplayName_XML LIKE 'MySmartObjectName'
ORDER BY Version DESC)

If you need to search checked out items, change 

FROM [Form].[ActionItem] A

to

FROM [Form].[ActionItem_Design] A

If the views have never been checked in you need to add _Design here as well

LEFT JOIN [Form].[View_Design] ON [View].ID=A.ContextID

LEFT JOIN [Form].[Form_Design] Form ON [Form].ID=A.ContextID

 

It's also helpful to be able to search the Parameters, this would let you find usages of the left and right side of a transfer data, for instance an environment field.

 

SELECT DISTINCT [ContextID], [view].NAME [View], Form.Name Form
FROM [Form].[ActionItem] A
LEFT JOIN [Form].[View] ON [View].ID=A.ContextID
LEFT JOIN [Form].[Form] Form ON [Form].ID=A.ContextID
WHERE try_convert(varchar(max),A.Parameters) LIKE '%SmartForms Runtime SSL (default)%' OR
try_convert(varchar(max),A.Properties) LIKE '%SmartForms Runtime SSL (default)%'


--The where's like could also be
LIKE (SELECT '%'+CONVERT(VARCHAR(38),ID)+'%' FROM Form.[View] WHERE DisplayName='MyNiceView')
LIKE (SELECT '%'+CONVERT(VARCHAR(38),ID)+'%' FROM Form.[Form] WHERE DisplayName='MyFantasticForm')

Find usages in Forms and Views

DECLARE @SearchTerm VARCHAR(128)='SSRS'

--Usages in Forms
SELECT a.ID, Name Form, Parameters, Properties, Expressions
FROM [Form].[Form] A
WHERE try_convert(varchar(max),A.Parameters) LIKE '%'+@SearchTerm+'%'
OR try_convert(varchar(max),A.Properties) LIKE '%'+@SearchTerm+'%'
OR try_convert(varchar(max),A.Expressions) LIKE '%'+@SearchTerm+'%'

--Usages in Views
SELECT a.ID, Name [View], Parameters, Properties, Expressions
FROM [Form].[View] A
WHERE try_convert(varchar(max),A.Parameters) LIKE '%'+@SearchTerm+'%'
OR try_convert(varchar(max),A.Properties) LIKE '%'+@SearchTerm+'%'
OR try_convert(varchar(max),A.Expressions) LIKE '%'+@SearchTerm+'%'

To fully search you need to look in the following tables and fields

[Form].[HandlerInstance] Properties and Functions

[Form].[State] Properties

[Form].[Control] Properties and Styles

[Form].[ConditionItem] Properties and Expressions

 

Update 18 Aug 20: Added wildcard search in Properties to second search.

Update 24 Aug 20: Search in Form and View definitions

 

If this helps, maybe hit the kudos. :)

 


1 reply

Thanks for sharing Nigel!


 


 

Reply