Recipe - Find Where Workflows Are Being Used

Skill level: Advanced

Organization: The Well Community Church

Requires Rock: 1.12.0

---

{# strip images & classes from the HTML but otherwise leave structure #}

Description

This recipe adds a new "References" area to the Workflow Configuration page that displays a list of entities that reference the current workflow type, similar to the Report and Data View detail pages.

Disclaimers

This should not be considered an exhaustive list of every single reference. If nothing else, this recipe does not include Lava workflowactivate references. You may also have to modify this recipe as new entity types are added or existing entities are modified in future versions of Rock, or if any of your installed plugins have references to workflow types.

The only reason this recipe is marked "Advanced" is that it involves adding a new SQL Table-Value Function in the database. This functionality doesn't necessarily have to be placed inside a SQL function. It could pretty easily be refactored to remove the need for a SQL function if desired. The only reason I created a function for it is because I have reused it in several other places in our Rock instance.

Adding a SQL function is not difficult, but it is a little sketchy for anyone not comfortable with working directly with the database. Please don't mess with this if you're not sure what you're doing.

How To

1. Run the following SQL code on the database to create the ufnWell_GetEntityDetails table-value function. This function allows you to pass in an entity type ID and an entity ID and get back some very basic details for the matching entity. Currently the function only supports the entity types that I have needed most frequently, but it wouldn't be too difficult to add additional entities if you need them. The function is basically just a series of else if statements with a specific query for each entity type.

   An earlier version of this function is also used in my Security Role Permissions Inspector recipe, so if you have already implemented that one, then you may just need to alter your existing ufnWell_GetEntityDetails function.

   CREATE FUNCTION [dbo].[ufnWell_GetEntityDetails](@EntityTypeID int, @EntityID int)
   RETURNS @Entity TABLE 
       (
           [Name] varchar(500), LongName varchar(1000),
           Parent1ID int, Parent1Name varchar(500),
           Parent2ID int, Parent2Name varchar(500),
           Parent3ID int, Parent3Name varchar(500),
           LinkableEntityTypeName varchar(50), LinkableEntityID int,
           TypeID int, TypeName varchar(500), 
           EntityIconCssClass varchar(100),
           IconCssClass varchar(100), IsActive bit
       )
   AS
   BEGIN
       DECLARE @Name AS varchar(1000)
       DECLARE @EntityType AS varchar(100)
       
       SELECT @EntityType = [Name] FROM EntityType WHERE ID = @EntityTypeID
   
       IF @EntityType = 'Rock.Model.Page' BEGIN
           INSERT INTO @Entity
           SELECT ISNULL(P.InternalName, P.PageTitle) AS [Name],
               CASE WHEN S.ID IS NOT NULL THEN S.[Name] + ' > ' ELSE '' END + ISNULL(P.InternalName, P.PageTitle) AS LongName,
               PP.ID AS Parent1ID, ISNULL(PP.InternalName, PP.PageTitle) AS Parent1Name,
               L.ID AS Parent2ID, L.[Name] AS Parent2Name,
               S.ID AS Parent3ID, S.[Name] AS Parent3Name,
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'far fa-file' AS EntityIconCssClass,
               ISNULL(P.IconCssClass, PP.IconCssClass) AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM [Page] P 
               LEFT JOIN [Page] PP ON PP.ID = P.ParentPageId 
               LEFT JOIN Layout L ON L.ID = P.LayoutID 
               LEFT JOIN [Site] S ON S.ID = L.SiteID 
           WHERE P.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Site' BEGIN
           INSERT INTO @Entity
           SELECT [Name], [Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 
               'fas fa-desktop' AS EntityIconCssClass,
               'fas fa-desktop' AS IconCssClass, IsActive
           FROM [Site]
           WHERE ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Block' BEGIN
           INSERT INTO @Entity
           SELECT B.[Name], 
               CASE WHEN S.ID IS NOT NULL THEN S.[Name] + ' > ' ELSE '' END
                + CASE WHEN P.ID IS NOT NULL THEN ISNULL(P.InternalName, P.PageTitle) + ' > ' WHEN L.ID IS NOT NULL THEN L.[Name] + ' > ' ELSE '' END + B.[Name]
                + CASE WHEN T.[Name] <> B.[Name] THEN ' [' + T.[Name] + ']' ELSE '' END AS LongName,
               ISNULL(P.ID, ISNULL(L.ID, S.ID)) AS Parent1ID, 
               ISNULL(ISNULL(P.InternalName, P.PageTitle), ISNULL(L.[Name], S.[Name])) AS Parent1Name, 
               CASE WHEN L.ID IS NOT NULL THEN S.ID ELSE NULL END AS Parent2ID, 
               CASE WHEN L.ID IS NOT NULL THEN S.[Name] ELSE NULL END AS Parent2Name, 
               CASE WHEN P.ID IS NOT NULL THEN S.ID ELSE NULL END AS Parent3ID, 
               CASE WHEN P.ID IS NOT NULL THEN S.[Name] ELSE NULL END AS Parent3Name,
               CASE WHEN B.SiteID IS NOT NULL THEN 'Site' 
                   WHEN B.LayoutID IS NOT NULL THEN 'Layout'
                   ELSE 'Page' END AS LinkableEntityTypeName, 
               ISNULL(P.ID, ISNULL(B.LayoutID, ISNULL(B.SiteID, 0))) AS LinkableEntityID,
               T.ID AS TypeID, T.[Name] AS TypeName, 'fas fa-cube' AS EntityIconCssClass,
               'fas fa-cube' AS IconCssClass, CAST(1 AS bit) AS IsActive
           FROM [Block] B 
               INNER JOIN BlockType T ON T.ID = B.BlockTypeID 
               LEFT JOIN [Page] P ON P.ID = B.PageID 
               LEFT JOIN Layout L ON L.ID = B.LayoutID OR L.ID = P.LayoutID 
               LEFT JOIN [Site] S ON S.ID = B.SiteID OR S.ID = L.SiteID
           WHERE B.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Group' BEGIN
           INSERT INTO @Entity
           SELECT G.[Name], T.[Name] + ' > ' + G.[Name] AS LongName,
               PG.ID AS Parent1ID, PG.[Name] AS Parent1Name, 
               GPG.ID AS Parent2ID, GPG.[Name] AS Parent2Name, 
               GGPG.ID AS Parent3ID, GGPG.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.[Name] AS TypeName, 
               'fas fa-users' AS EntityIconCssClass, T.IconCssClass, 
               CAST(CASE WHEN G.IsActive = 1 AND G.IsArchived = 0 THEN 1 ELSE 0 END AS bit) AS IsActive
           FROM [Group] G 
               INNER JOIN GroupType T ON T.ID = G.GroupTypeID 
               LEFT JOIN [Group] PG ON PG.ID = G.ParentGroupId 
               LEFT JOIN [Group] GPG ON GPG.ID = PG.ParentGroupId 
               LEFT JOIN [Group] GGPG ON GGPG.ID = GPG.ParentGroupId
           WHERE G.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.WorkflowType' BEGIN
           INSERT INTO @Entity
           SELECT W.[Name], W.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-random' AS EntityIconCssClass,
               ISNULL(W.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
               W.IsActive
           FROM WorkflowType W 
               INNER JOIN Category C ON C.ID = W.CategoryId 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryId 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryId
           WHERE W.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.WorkflowActivityType' BEGIN
           INSERT INTO @Entity
           SELECT A.[Name], W.[Name] + ' > ' + A.[Name] AS LongName,
               W.ID AS Parent1ID, W.[Name] AS Parent1Name, 
               C.ID AS Parent2ID, C.[Name] AS Parent2Name, 
               PC.ID AS Parent3ID, PC.[Name] AS Parent3Name, 
               'Workflow Type' AS LinkableEntityTypeName, W.ID AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-cubes' AS EntityIconCssClass,
               ISNULL(W.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
               A.IsActive
           FROM WorkflowActivityType A 
               INNER JOIN WorkflowType W ON W.ID = A.WorkflowTypeId 
               INNER JOIN Category C ON C.ID = W.CategoryId 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryId 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryId
           WHERE A.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.WorkflowActionType' BEGIN
           INSERT INTO @Entity
           SELECT A2.[Name], W.[Name] + ' > ' + A1.[Name] + ' > ' + A2.[Name] AS LongName,
               A1.ID AS Parent1ID, A1.[Name] AS Parent1Name, 
               W.ID AS Parent2ID, W.[Name] AS Parent2Name, 
               C.ID AS Parent3ID, C.[Name] AS Parent3Name, 
               'Workflow Type' AS LinkableEntityTypeName, W.ID AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-cube' AS EntityIconCssClass,
               ISNULL(W.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
               A1.IsActive
           FROM WorkflowActionType A2 
               INNER JOIN WorkflowActivityType A1 ON A1.ID = A2.ActivityTypeId 
               INNER JOIN WorkflowType W ON W.ID = A1.WorkflowTypeId 
               INNER JOIN Category C ON C.ID = W.CategoryId 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryId 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryId
           WHERE A2.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Workflow' BEGIN
           INSERT INTO @Entity
           SELECT W.[Name], WT.[Name] + ' > ' + W.[Name] AS LongName,
               WT.ID AS Parent1ID, WT.[Name] AS Parent1Name, 
               C.ID AS Parent2ID, C.[Name] AS Parent2Name, 
               PC.ID AS Parent3ID, PC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-random' AS EntityIconCssClass,
               ISNULL(WT.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
               WT.IsActive
           FROM Workflow W 
               INNER JOIN WorkflowType WT ON WT.ID = W.WorkflowTypeId 
               INNER JOIN Category C ON C.ID = WT.CategoryId 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryId 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryId
           WHERE W.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.DataView' BEGIN
           INSERT INTO @Entity
           SELECT V.[Name], V.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-filter' AS EntityIconCssClass,
               ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM DataView V 
               INNER JOIN Category C ON C.ID = V.CategoryId 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryId 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryId
           WHERE V.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Attribute' BEGIN
           INSERT INTO @Entity
           SELECT TOP 1 A.[Name], ISNULL(T.FriendlyName, '[Global]') + ' > ' + A.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, ISNULL(T.FriendlyName, '[Global]') AS TypeName, 'fas fa-list' AS EntityIconCssClass,
               ISNULL(A.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
               A.IsActive
           FROM Attribute A LEFT JOIN
               EntityType T ON T.ID = A.EntityTypeID LEFT JOIN
               AttributeCategory AC ON AC.AttributeID = A.ID LEFT JOIN
               Category C ON C.ID = AC.CategoryID LEFT JOIN
               Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
               Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE A.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.BinaryFileType' BEGIN
           INSERT INTO @Entity
           SELECT T.[Name], ST.FriendlyName + ' > ' + T.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               ST.ID AS TypeID, ST.FriendlyName AS TypeName, 
               'far fa-file-alt' AS EntityIconCssClass,
               T.IconCssClass, CAST(1 AS bit) AS IsActive
           FROM BinaryFileType T 
               INNER JOIN EntityType ST ON ST.ID = T.StorageEntityTypeID
           WHERE T.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Category' BEGIN
           INSERT INTO @Entity
           SELECT C.[Name], T.FriendlyName + ' > ' + C.[Name] AS LongName,
               PC.ID AS Parent1ID, PC.[Name] AS Parent1Name, 
               GPC.ID AS Parent2ID, GPC.[Name] AS Parent2Name, 
               GGPC.ID AS Parent3ID, GGPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.FriendlyName AS TypeName, 'fas fa-folder' AS EntityIconCssClass,
               ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, ISNULL(GPC.IconCssClass, GGPC.IconCssClass))) AS IconCssClass,
               CAST(1 AS bit) AS IsActive
           FROM Category C 
               INNER JOIN EntityType T ON T.ID = C.EntityTypeID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID 
               LEFT JOIN Category GGPC ON GGPC.ID = GPC.ParentCategoryID
           WHERE C.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.DefinedType' BEGIN
           INSERT INTO @Entity
           SELECT T.[Name], CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + T.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-book' AS EntityIconCssClass,
               ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass,
               T.IsActive
           FROM DefinedType T 
               LEFT JOIN Category C ON C.ID = T.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE T.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.FinancialAccount' BEGIN
           INSERT INTO @Entity
           SELECT [Name], [Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-piggy-bank' AS EntityIconCssClass,
               'fas fa-piggy-bank' AS IconCssClass,
               IsActive
           FROM FinancialAccount
           WHERE ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.FinancialBatch' BEGIN	
           INSERT INTO @Entity	
           SELECT [Name], [Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-archive' AS EntityIconCssClass,
               'fas fa-archive' AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM FinancialBatch
           WHERE ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.FinancialPersonSavedAccount' BEGIN
           INSERT INTO @Entity
           SELECT A.[Name], CASE WHEN P.ID IS NOT NULL THEN P.NickName + ' ' + P.LastName + ' > ' ELSE '' END + A.[Name] AS LongName,
               P.ID AS Parent1ID, CASE WHEN P.ID IS NOT NULL THEN P.NickName + ' ' + P.LastName END AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 
               'fas fa-money-check' AS EntityIconCssClass,
               'fas fa-money-check' AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM FinancialPersonSavedAccount A 
               LEFT JOIN PersonAlias PA ON PA.ID = A.PersonAliasID 
               LEFT JOIN Person P ON P.ID = PA.PersonID
           WHERE A.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.GroupType' BEGIN
           INSERT INTO @Entity
           SELECT T.[Name], T.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               IT.ID AS TypeID, IT.[Name] AS TypeName, 
               'fas fa-sitemap' AS EntityIconCssClass,
               ISNULL(T.IconCssClass, IT.IconCssClass) AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM GroupType T 
               LEFT JOIN GroupType IT ON IT.ID = T.InheritedGroupTypeID
           WHERE T.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Location' BEGIN
           INSERT INTO @Entity
           SELECT L.[Name], CASE WHEN PL.ID IS NOT NULL THEN PL.[Name] + ' > ' ELSE '' END + L.[Name] AS LongName,
               PL.ID AS Parent1ID, PL.[Name] AS Parent1Name, 
               GPL.ID AS Parent2ID, GPL.[Name] AS Parent2Name, 
               GGPL.ID AS Parent3ID, GGPL.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-map-marker-alt' AS EntityIconCssClass,
               'fas fa-map-marker-alt' AS IconCssClass, L.IsActive
           FROM [Location] L 
               LEFT JOIN [Location] PL ON PL.ID = L.ParentLocationID 
               LEFT JOIN [Location] GPL ON GPL.ID = PL.ParentLocationID 
               LEFT JOIN [Location] GGPL ON PL.ID = GPL.ParentLocationID
           WHERE L.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Metric' BEGIN
           INSERT INTO @Entity
           SELECT TOP 1 M.Title, C.[Name] + ' > ' + M.Title AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'fas fa-signal' AS EntityIconCssClass,
               ISNULL(M.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM Metric M 
               LEFT JOIN MetricCategory MC ON MC.MetricID = M.ID 
               LEFT JOIN Category C ON C.ID = MC.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE M.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.NoteType' BEGIN
           INSERT INTO @Entity
           SELECT N.[Name], T.FriendlyName + ' > ' + N.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.FriendlyName AS TypeName, 
               'far fa-sticky-note' AS EntityIconCssClass,
               N.IconCssClass, CAST(1 AS bit) AS IsActive
           FROM NoteType N 
               INNER JOIN EntityType T ON T.ID = N.EntityTypeID
           WHERE N.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Report' BEGIN
           INSERT INTO @Entity
           SELECT R.[Name], CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + R.[Name] + ' [' + T.FriendlyName + ']' AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.FriendlyName AS TypeName, 'fas fa-clipboard-list' AS EntityIconCssClass,
               ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass,
               CAST(1 AS bit) AS IsActive
           FROM Report R 
               INNER JOIN EntityType T ON T.ID = R.EntityTypeID 
               LEFT JOIN Category C ON C.ID = R.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE R.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Tag' BEGIN
           INSERT INTO @Entity
           SELECT T.[Name], ET.FriendlyName + ' > ' + T.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               ET.ID AS TypeID, ET.FriendlyName AS TypeName,
               'fas fa-tag' AS EntityIconCssClass, 
               T.IconCssClass, T.IsActive
           FROM Tag T 
               INNER JOIN EntityType ET ON ET.ID = T.EntityTypeID 
               LEFT JOIN Category C ON C.ID = T.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE T.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.RestController' BEGIN
           INSERT INTO @Entity
           SELECT [Name], [Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name,
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 
               NULL AS TypeID, NULL AS TypeName, 
               'fas fa-exchange-alt' AS EntityIconCssClass,
               'fas fa-exchange-alt' AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM RestController
           WHERE ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.Badge' BEGIN
           INSERT INTO @Entity
           SELECT B.[Name], B.[Name] + ' [' + T.FriendlyName + ']' AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.FriendlyName AS TypeName, 
               'fas fa-icons' AS EntityIconCssClass,
               'fas fa-icons' AS IconCssClass, B.IsActive
           FROM Badge B 
               INNER JOIN EntityType T ON T.ID = B.EntityTypeID
           WHERE B.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.RestAction' BEGIN
           INSERT INTO @Entity
           SELECT A.Method + ' ' + A.[Path] AS [Name], C.[Name] + ': ' + A.Method + ' ' + A.[Path] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-exchange-alt' AS EntityIconCssClass,
               'fas fa-exchange-alt' AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM RestAction A 
               INNER JOIN RestController C ON C.ID = A.ControllerID
           WHERE A.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.ContentChannelItem' BEGIN
           INSERT INTO @Entity
           SELECT I.Title AS [Name], C.[Name] + ' > ' + I.Title AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.[Name] AS TypeName,
               'far fa-file-code' AS EntityIconCssClass,
               C.IconCssClass, CAST(1 AS bit) AS IsActive
           FROM ContentChannelItem I 
               INNER JOIN ContentChannel C ON C.ID = I.ContentChannelID 
               INNER JOIN ContentChannelType T ON T.ID = C.ContentChannelTypeID
           WHERE I.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.ContentChannel' BEGIN
           INSERT INTO @Entity
           SELECT TOP 1 CH.[Name], CH.[Name] + ' [' + T.[Name] + ']' AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.[Name] AS TypeName, 'fas fa-bullhorn' AS EntityIconCssClass,
               ISNULL(CH.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM ContentChannel CH 
               INNER JOIN ContentChannelType T ON T.ID = CH.ContentChannelTypeID 
               LEFT JOIN ContentChannelCategory CA ON CA.ContentChannelID = CH.ID 
               LEFT JOIN Category C ON C.ID = CA.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE CH.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.RegistrationTemplate' BEGIN
           INSERT INTO @Entity
           SELECT RT.[Name], CASE WHEN PC.ID IS NOT NULL THEN PC.[Name] + ' > ' ELSE '' END + CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + RT.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName, 'far fa-clipboard' AS EntityIconCssClass,
               ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass, 
               RT.IsActive
           FROM RegistrationTemplate RT 
               INNER JOIN Category C ON C.ID = RT.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE RT.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.ConnectionOpportunity' BEGIN
           INSERT INTO @Entity
           SELECT O.[Name], T.[Name] + ' > ' + O.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               T.ID AS TypeID, T.[Name] AS TypeName,
               'fas fa-link' AS EntityIconCssClass,
               ISNULL(O.IconCssClass, T.IconCssClass) AS IconCssClass, 
               O.IsActive
           FROM ConnectionOpportunity O 
               INNER JOIN ConnectionType T ON T.ID = O.ConnectionTypeId
           WHERE O.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.ConnectionType' BEGIN
           INSERT INTO @Entity
           SELECT T.[Name], T.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-plug' AS EntityIconCssClass,
               T.IconCssClass, T.IsActive
           FROM ConnectionType T
           WHERE T.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.ContentChannelType' BEGIN
           INSERT INTO @Entity
           SELECT T.[Name], T.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-project-diagram' AS EntityIconCssClass,
               NULL AS IconCssClass, CAST(1 AS bit) AS IsActive
           FROM ContentChannelType T
           WHERE T.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.EventCalendar' BEGIN
           INSERT INTO @Entity
           SELECT C.[Name], C.[Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-calendar-alt' AS EntityIconCssClass,
               C.IconCssClass, C.IsActive
           FROM EventCalendar C
           WHERE C.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.CommunicationTemplate' BEGIN
           INSERT INTO @Entity
           SELECT CT.[Name], CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + CT.[Name] AS LongName,
               C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
               PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
               GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'far fa-list-alt' AS EntityIconCssClass,
               ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass, 
               CT.IsActive
           FROM CommunicationTemplate CT 
               INNER JOIN Category C ON C.ID = CT.CategoryID 
               LEFT JOIN Category PC ON PC.ID = C.ParentCategoryID 
               LEFT JOIN Category GPC ON GPC.ID = PC.ParentCategoryID
           WHERE CT.ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.SignalType' BEGIN
           INSERT INTO @Entity
           SELECT [Name], [Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fas fa-flag' AS EntityIconCssClass,
               SignalIconCssClass AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM SignalType
           WHERE ID = @EntityID
       END
       ELSE IF @EntityType = 'Rock.Model.ServiceJob' BEGIN
           INSERT INTO @Entity
           SELECT [Name], [Name] AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fa fa-clock-o' AS EntityIconCssClass,
               NULL AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM ServiceJob
           WHERE ID = @EntityID
       END
       ELSE BEGIN
           INSERT INTO @Entity
           SELECT FriendlyName + ' ID ' + CAST(@EntityID AS varchar) AS [Name], FriendlyName + ' ID ' + CAST(@EntityID AS varchar) AS LongName,
               NULL AS Parent1ID, NULL AS Parent1Name, 
               NULL AS Parent2ID, NULL AS Parent2Name, 
               NULL AS Parent3ID, NULL AS Parent3Name, 
               NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID,
               NULL AS TypeID, NULL AS TypeName,
               'fa fa-box' AS EntityIconCssClass,
               'fa fa-box' AS IconCssClass, 
               CAST(1 AS bit) AS IsActive
           FROM EntityType 
           WHERE ID = @EntityTypeID
       END
       
       RETURN
   END

2. Create a new Lava Shortcode called "Entity Details (JSON)". This shortcode is simply a Lava wrapper for the SQL function above. Again, I only did this for easy reusability in our Rock instance. You can definitely refactor the code, making this shortcode unnecessary as well.

   • Name: Entity Details (JSON)
   • Tag Name: entityjson
   • Tag Type: Inline
   • Description: Returns a JSON string with the results of the Get Entity Details SQL function

   Documentation:

   <p><strong>Usage:</strong></p>
   <pre>{[ entityjson typeid:'16' entityid:'159717' ]}</pre>
   <ul>
       <li><strong>typeid</strong> (integer, required) – The entity type ID of the entity</li>
       <li><strong>entityid</strong> (integer, required) – The ID of the entity</li>
   </ul>
   <p><strong>Output:</strong></p>
   <pre>{
     "Name": "Communications",
     "LongName": "Staff Department &gt; Communications",
     "Parent1ID": 159710,
     "Parent1Name": "Staff Directory",
     "Parent2ID": null,
     "Parent2Name": null,
     "Parent3ID": null,
     "Parent3Name": null,
     "TypeID": 103,
     "TypeName": "Staff Department",
     "EntityIconCssClass": "fas fa-users",
     "IconCssClass": "fas fa-project-diagram",
     "IsActive": true
   }</pre>

   Shortcode Markup:

   {%- sql typeid:'{{ typeid }}' entityid:'{{ entityid }}' -%}
       SELECT * FROM dbo.ufnWell_GetEntityDetails(@typeid, @entityid) 
   {%- endsql -%}
   {{ results | First | ToJSON }}

   Parameters:

   • typeid 0
   • entityid 0

   Enabled Lava Commands:

   Check the Sql box.

3. Create a new Lava Shortcode called "Entity Admin Link". This shortcode generates a link to the administrative page for the given entity.

   This shortcode supports the Room Management plugin by BEMA. However, you may need to adjust the reservation page URLs in the markup below, since the page numbers likely will not match your Rock instance.

   • Name: Entity Admin Link
   • Tag Name: entityadminlink
   • Tag Type: Inline
   • Description: Attempts to return a button link to the administrative page for the given entity

   Documentation:

   <p><strong>Usage:</strong></p>
   <pre style="position: relative;">{[ entityadminlink entityid:'485' entitytypename:'Page' buttonclass:'btn-info btn-sm' icon:'fa fa-file-alt' ]}</pre>
   <ul>
       <li><strong>entityid</strong> (integer, required, default:<code>0</code>) – ID of the entity to generate a link for</li>
       <li><strong>entitytypeid</strong> (integer, optional, default: <code>0</code>) – Entity type ID of the entity to generate a link for. Not necessary if <code>entitytypename</code> is provided.</li>
       <li><strong>entitytypename</strong> (string, optional, default: <code>blank</code>) – Entity type friendly name of the entity to generate a link for. If left blank, <code>entitytypename</code> will be found using <code>entitytypeid</code>.</li>
       <li><strong>parententityid</strong> (integer, optional, default:<code>0</code>) – ID of the parent entity if the entity link requires it</li>
       <li><strong>buttonclass</strong> (string, optional, default: <code>btn-default</code>) – CSS class(es) to add to the button link</li>
       <li><strong>icon</strong> (string, optional, default: <code>fa fa-pencil</code>) – CSS class of the icon to display in the button link</li>
       <li><strong>urlonly</strong> (boolean, optional, default: <code>false</code>) – Should the admin URL be returned without any HTML formatting?</li>
   </ul>

   Shortcode Markup:

   {%- if entityid != '0' -%}
   {%- if entitytypename == empty and entitytypeid != 0 -%}
       {%- entitytype id:'{{ entitytypeid }}' securityenabled:'false' -%}
           {%- assign entitytypename = entitytype.FriendlyName -%}
       {%- endentitytype -%}
   {%- endif -%}
   {%- capture componentURL -%}
       {%- case entitytypename -%}
           {%- when 'Attribute' -%}                    {{- '/admin/system/entity-attributes' -}}
           {%- when 'Attribute Matrix Template' -%}    {{- '/admin/general/attribute-matrix/' }}{{ entityid -}}
           {%- when 'Binary File Type' -%}             {{- '/admin/general/file-types/' }}{{ entityid -}}
           {%- when 'Category' -%}                     {{- '/admin/system/category-manager' -}}
           {%- when 'Connection Opportunity' -%}       {{- '/people/connections/types/' }}{{ parententityid }}/opportunity/{{ entityid -}}
           {%- when 'Connection Type' -%}              {{- '/people/connections/types/' }}{{ entityid -}}
           {%- when 'Content Channel' -%}              {{- '/admin/cms/content-channels/' }}{{ entityid -}}
           {%- when 'Content Channel Type' -%}         {{- '/admin/cms/content-channel-type/' }}{{ entityid -}}
           {%- when 'Data View' -%}                    {{- '/reporting/dataviews?DataViewId=' }}{{ entityid -}}
           {%- when 'Defined Type' -%}                 {{- '/admin/general/defined-types/' }}{{ entityid -}}
           {%- when 'Lava Shortcode' -%}               {{- '/admin/cms/lava-shortcodes/' }}{{ entityid -}}
           {%- when 'Layout' -%}                       {{- '/admin/cms/sites/layouts/' }}{{ entityid -}}
           {%- when 'Giving Automation Config' -%}     {{- '/finance/giving-alerts/configuration' }}
           {%- when 'Group' -%}                        {{- '/people/groups?GroupId=' }}{{ entityid -}}
           {%- when 'Group Type' -%}                   {{- '/admin/general/group-types/' }}{{ entityid -}}
           {%- when 'Note Type' -%}                    {{- '/admin/system/note-types/' }}{{ entityid -}}
           {%- when 'Page' -%}                         {{- '/admin/cms/pages?Page=' }}{{ entityid -}}
           {%- when 'Person' -%}                       {{- '/person/' }}{{ entityid -}}
           {%- when 'Phone Number' -%}                 {{- '/admin/communications/sms-numbers' -}}
           {%- when 'Registration Instance' -%}        {{- '/web/event-registrations/' }}{{ entityid -}}
           {%- when 'Registration Template' -%}        {{- '/web/event-registrations?RegistrationTemplateId=' }}{{ entityid -}}
           {%- when 'Report' -%}                       {{- '/reporting/reports?ReportId=' }}{{ entityid -}}
           {%- when 'Service Job' -%}                  {{- '/admin/system/jobs/' }}{{ entityid -}}
           {%- when 'Sms Pipeline' -%}                 {{- '/admin/communications/sms-pipeline/' }}{{ entityid -}}
           {%- when 'Site' -%}                         {{- '/admin/cms/sites/' }}{{ entityid -}}
           {%- when 'Step Type' -%}                    {{- '/steps/type/' }}{{ entityid -}}
           {%- when 'Tag' -%}                          {{- '/admin/general/tags/' }}{{ entityid -}}
           {%- when 'Workflow' -%}                     {{- '/workflow/' }}{{ entityid -}}
           {%- when 'Workflow Trigger' -%}             {{- '/admin/general/workflow-triggers/' }}{{ entityid -}}
           {%- when 'Workflow Type' -%}                {{- '/admin/general/workflows?workflowTypeId=' }}{{ entityid -}}
           {%- when 'Reservation' -%}                  {{- '/page/761?ReservationId=' }}{{ entityid -}}
           {%- when 'Reservation Type' -%}             {{- '/page/770?ReservationTypeId=' }}{{ entityid -}}
       {%- endcase -%}
   {%- endcapture -%}
   {%- if componentURL != '' -%}
       {%- assign urlonly = urlonly | Default:'false' | AsBoolean -%}
       {%- if urlonly == false -%}
   <a href="{{ componentURL | Trim }}" class="btn {{ buttonclass }}"><i class="{{ icon }}"></i></a>
       {%- else -%}
   {{- componentURL | Trim -}}
       {%- endif -%}
   {%- endif -%}
   {%- endif -%}
   

   Parameters:

   • entityid 0
   • entitytypeid 0
   • entitytypename (blank)
   • icon fa fa-pencil
   • buttonclass btn-default
   • urlonly false

   Enabled Lava Commands:

   Check the Rock Entity box.

4. Add a new HTML Content block to the Workflow Configuration page. Enable the Sql and Rock Entity Lava commands in the block properties.

   Depending on your version of Rock, you may need to add or remove some of the sub-queries. If you are using the Room Management plugin by BEMA, then you'll want to uncomment the Reservation Workflow Trigger sub-query near the bottom of the SQL query.

   {%- assign singleFieldTypeID = 36 -%} //- "Workflow Type" field type
   {%- assign multiFieldTypeID = 73 -%} //- "Workflow Types" field type
   
   {%- assign workflowTypeID = PageParameter['workflowTypeId'] | Default:'0' | AsInteger -%}
   
   {%- if workflowTypeID > 0 -%}
       {%- sql workflowtypeid:'{{ workflowTypeID }}' -%}
           SELECT * FROM
           (
               -- Acheivement Types
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-clipboard' AS EntityIconCSSClass,
                   A.[Name] + ' > ' +
                       CASE WHEN A.AchievementFailureWorkflowTypeId = @WorkflowTypeID THEN 'Achievement Fail Workflow Type'
                           WHEN A.AchievementStartWorkflowTypeId = @WorkflowTypeID THEN 'Achievement Start Workflow Type'
                           WHEN A.AchievementSuccessWorkflowTypeId = @WorkflowTypeID THEN 'Achievement Success Workflow Type' END AS [Name],
                   A.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM AchievementType A CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '0e99356c-0dea-4f24-944e-21cd5fa83b9e') X
               WHERE A.AchievementFailureWorkflowTypeId = @WorkflowTypeID
                   OR A.AchievementStartWorkflowTypeId = @WorkflowTypeID
                   OR A.AchievementSuccessWorkflowTypeId = @WorkflowTypeID
   
               UNION ALL
   
               -- Benevolence
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-hand-holding-heart' AS EntityIconCSSClass,
                   BT.[Name],
                   BT.ID AS LinkableEntityID,
                   'Benevolence Type' AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM BenevolenceWorkflow BW LEFT JOIN
                   BenevolenceType BT ON BT.ID = BW.BenevolenceTypeID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '86a4bcfa-b3ca-4602-8f2a-cb531359e219') X
               WHERE BW.WorkflowTypeId = @WorkflowTypeID
   
               UNION ALL
   
               -- Connection Opportunities
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-plug' AS EntityIconCSSClass,
                   ISNULL(O.[Name], CT.[Name]) +
                   CASE WT.TriggerType
                       WHEN 0 THEN ' > Request Started'
                       WHEN 1 THEN ' > Request Connected'
                       WHEN 2 THEN ' > Status Changed'
                       WHEN 3 THEN ' > State Changed'
                       WHEN 4 THEN ' > Activity Added'
                       WHEN 5 THEN ' > Placement Group Assigned'
                       WHEN 6 THEN ' > Manual'
                       WHEN 7 THEN ' > Request Transferred'
                       WHEN 8 THEN ' > Request Assigned'
                       WHEN 9 THEN ' > Future Followup Date Reached'
                   END AS [Name],
                   ISNULL(O.ID, CT.ID) AS LinkableEntityID,
                   CASE WHEN O.ID IS NULL THEN 'Connection Type' ELSE 'Connection Opportunity' END AS LinkableEntityTypeName,
                   CT.ID AS ParentEntityID
               FROM ConnectionWorkflow WT LEFT JOIN
                   ConnectionOpportunity O ON O.ID = WT.ConnectionOpportunityID LEFT JOIN
                   ConnectionType CT ON CT.ID = WT.ConnectionTypeID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '4eb8711f-7301-4699-a223-0505a7ceb20a') X
               WHERE WorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
   
               -- Financial Transaction Alerts
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-money' AS EntityIconCSSClass,
                   'Giving Alert Type' AS [Name],
                   NULL AS LinkableEntityID,
                   'Giving Automation Config' AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM FinancialTransactionAlertType AT CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '1b1ea42d-4e00-427e-98f0-4cb9e9120542') X
               WHERE WorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
               
               -- Group Member Workflow Triggers
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-users' AS EntityIconCSSClass,
                   ISNULL(G.[Name], GT.[Name]) + ' > ' + WT.[Name] AS [Name],
                   ISNULL(G.ID, GT.ID) AS LinkableEntityID,
                   CASE WHEN G.ID IS NULL THEN 'Group Type' ELSE 'Group' END AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM GroupMemberWorkflowTrigger WT LEFT JOIN
                   [Group] G ON G.ID = WT.GroupID LEFT JOIN
                   GroupType GT ON GT.ID = WT.GroupTypeID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '3ce3406a-1ffe-4cca-a8d5-916eef800d76') X
               WHERE WorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
               
               -- Group Requirement Type
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-check-square-o' AS EntityIconCSSClass,
                   RT.[Name] + ' > ' + 
                       CASE WHEN RT.DoesNotMeetWorkflowTypeId = @WorkflowTypeID THEN 'Does Not Meet Workflow Type'
                           WHEN RT.WarningWorkflowTypeId = @WorkflowTypeID THEN 'Warnings Workflow Type'END AS [Name],
                   RT.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM GroupRequirementType RT CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '8e67e852-d1bf-485c-9898-09f19998cc40') X
               WHERE RT.DoesNotMeetWorkflowTypeId = @WorkflowTypeID
                   OR RT.WarningWorkflowTypeId = @WorkflowTypeID
   
               UNION ALL
   
               -- Group Types
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-users' AS EntityIconCSSClass,
                   GT.[Name] + ' > Schedule Cancellation Workflow Type' AS [Name],
                   GT.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM GroupType GT CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '0dd30b04-01cf-4b38-8e83-be661e2f7286') X
               WHERE ScheduleCancellationWorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
   
               -- Media Folders
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-play-circle' AS EntityIconCSSClass,
                   F.[Name],
                   F.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM MediaFolder F CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '84bd0062-b05a-4ee7-843e-6f0b266ca377') X
               WHERE F.WorkflowTypeId = @WorkflowTypeID
   
               UNION ALL
   
               -- Registration Instances
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-file-o' AS EntityIconCSSClass,
                   RT.[Name] + ' > ' + RI.[Name] AS [Name],
                   RI.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM RegistrationInstance RI INNER JOIN
                   RegistrationTemplate RT ON RT.ID = RI.RegistrationTemplateID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '5cd9c0c8-c047-61a0-4e36-0fdb8496f066') X
               WHERE RI.RegistrationWorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
   
               -- Registration Templates
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-clipboard' AS EntityIconCSSClass,
                   RT.[Name] + ' > ' +
                       CASE WHEN RT.RegistrationWorkflowTypeID = @WorkflowTypeID THEN 'Registration Workflow Type'
                           WHEN RT.RegistrantWorkflowTypeID = @WorkflowTypeID THEN 'Registrant Workflow Type' END AS [Name],
                   RT.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM RegistrationTemplate RT CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = 'a01e3e99-a8ad-4c6c-baac-98795738ba70') X
               WHERE RT.RegistrationWorkflowTypeID = @WorkflowTypeID
                   OR RT.RegistrantWorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
   
               -- Reminder Types
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'far fa-clock' AS EntityIconCSSClass,
                   R.[Name] + ' > Notification Workflow Type' AS [Name],
                   R.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM ReminderType R CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = 'b2b0b6f3-0e3b-40cf-ba93-fbb99d50788c') X
               WHERE R.NotificationWorkflowTypeId = @WorkflowTypeID
   
               UNION ALL
   
               -- Step Workflow Triggers
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-map-marked-alt' AS EntityIconCSSClass,
                   P.[Name] + ' > ' + ST.[Name] AS [Name],
                   ST.ID AS LinkableEntityID,
                   'Step Type' AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM StepWorkflowTrigger WT INNER JOIN
                   StepType ST ON ST.ID = WT.StepTypeID INNER JOIN
                   StepProgram P ON P.ID = ST.StepProgramID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '909cd1c7-c7a0-4691-83df-038470bd9016') X
               WHERE WorkflowTypeID = @WorkflowTypeID
   
               UNION ALL
   
               -- Workflow Triggers
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fa fa-magic' AS EntityIconCSSClass,
                   ET.FriendlyName +
                   CASE WT.WorkflowTriggerType
                       WHEN 0 THEN ' (Pre Save)'
                       WHEN 1 THEN ' (Post Save)'
                       WHEN 2 THEN ' (Pre Delete)'
                       WHEN 3 THEN ' (Post Delete)'
                       WHEN 4 THEN ' (Immediate Post Save)'
                       WHEN 5 THEN ' (Post Add)'
                   END AS [Name],
                   WT.ID AS LinkableEntityID,
                   X.EntityTypeName AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM WorkflowTrigger WT INNER JOIN
                   EntityType ET ON ET.ID = WT.EntityTypeID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = '3781c82a-7f40-4d88-b3db-1b9589d73d3d') X
               WHERE WorkflowTypeID = @WorkflowTypeID
   
   /*
               UNION ALL
   
               -- PLUGIN: Reservation Workflow Triggers
               SELECT X.EntityTypeID, X.EntityTypeName,
                   'fas fa-chess-rook' AS EntityIconCSSClass,
                   RT.[Name], RT.ID AS LinkableEntityID,
                   'Reservation Type' AS LinkableEntityTypeName,
                   NULL AS ParentEntityID
               FROM _com_bemaservices_RoomManagement_ReservationWorkflowTrigger WT INNER JOIN
                   _com_bemaservices_RoomManagement_ReservationType RT ON RT.ID = WT.ReservationTypeID CROSS APPLY
                   (SELECT ID AS EntityTypeID, FriendlyName AS EntityTypeName
                       FROM EntityType WHERE GUID = 'cd0c935b-c3ef-465b-964e-a3ab686d8f51') X
               WHERE WorkflowTypeID = @WorkflowTypeID
   */
           ) AS A
           GROUP BY EntityTypeID, EntityTypeName, EntityIconCSSClass, Name, LinkableEntityID, LinkableEntityTypeName, ParentEntityID
           ORDER BY EntityTypeName
       {%- endsql -%}
   
       {%- assign propertyReferenceCount = results | Size -%}
   
       {%- assign workflowTypeGUID = '' -%}
       {%- workflowtype id:'{{ workflowTypeID }}' securityenabled:'false' -%}
           {%- assign workflowTypeGUID = workflowtype.Guid -%}
       {%- endworkflowtype -%}
       {%- attributevalue where:'Value *= "{{ workflowTypeGUID }}"' expression:'Attribute.FieldTypeId == {{ singleFieldTypeID }} || Attribute.FieldTypeId == {{ multiFieldTypeID }}' securityenabled:'false' -%}
           {%- assign attributeValues = attributevalueItems | OrderBy:'Attribute.EntityType.FriendlyName' -%}
       {%- endattributevalue -%}
   
       {%- assign attributeReferenceCount = attributeValues | Size -%}
       {%- assign referenceCount = attributeReferenceCount | Plus:propertyReferenceCount -%}
   
       {%- if referenceCount > 0 -%}
   <div class="panel">
       <div class="panel-body">
           <div class="row">
               <div id="workflowtype-reference-list" class="col-md-6">
                   <div class="mb-2"><strong>References</strong> ({{ referenceCount }})</div>
                   <dl>
           {%- if attributeReferenceCount > 0 -%}
               {%- assign prevEntityTypeID = 0 -%}
               {%- for value in attributeValues -%}
                   {%- attribute id:'{{ value.AttributeId }}' securityenabled:'false' -%}
                       {%- assign id = value.EntityId -%}
                       {%- assign type = attribute.EntityType -%}
                       {%- assign name = attribute.Name -%}
                       {%- if type and id != '0' -%}
                           {%- capture entityDetailsJSON %}{[ entityjson typeid:'{{ type.Id }}' entityid:'{{ id }}' ]}{% endcapture -%}
                           {%- assign entityDetails = entityDetailsJSON | FromJSON -%}
                       {%- endif -%}
                       {%- assign linkableEntityID = id | AsString -%}
                       {%- assign linkableEntityID = entityDetails.LinkableEntityID | Default:linkableEntityID -%}
                       {%- assign linkableEntityTypeName = entityDetails.LinkableEntityTypeName | Default:type.FriendlyName -%}
                       {%- capture adminURL %}{[ entityadminlink entityid:'{{ linkableEntityID }}' entitytypename:'{{ linkableEntityTypeName }}' urlonly:'true' ]}{% endcapture -%}
                       {%- if type.Id != prevEntityTypeID -%}
                           {%- if forloop.first == false %}
                               {%- if 1 == 2 %}<dd>{% endif %} //- trick Rock into not showing the missing HTML tag message on save
                       </dd>
                           {%- endif -%}
                       <dt>
                           <i class="{{ entityDetails.EntityIconCssClass | Default:'fas fa-cube' }} fa-fw"></i>
                           {{ type.FriendlyName | Pluralize }}
                       </dt>
                       <dd>
                           {%- assign prevEntityTypeID = type.Id -%}
                       {%- endif -%}
                       {%- if adminURL != '' -%}
                           <a href="{{ adminURL }}" class="text-small"><small>{{ entityDetails.LongName }} &gt; {{ name }}</small></a><br>
                       {%- else -%}
                           <small>{{ entityDetails.LongName }} &gt; {{ name }}</small><br>
                       {%- endif -%}
                       {%- if forloop.last == true -%}
                       </dd>
                       {%- endif -%}
                   {%- endattribute -%}
               {%- endfor -%}
           {%- endif -%}
   
           {%- if propertyReferenceCount > 0 -%}
               {%- assign prevEntityTypeID = 0 -%}
               {%- for reference in results -%}
                   {%- assign entityTypeID = reference.EntityTypeID -%}
                   {%- assign entityTypeName = reference.EntityTypeName -%}
                   {%- assign linkableEntityID = reference.LinkableEntityID -%}
                   {%- assign linkableEntityTypeName = reference.LinkableEntityTypeName -%}
                   {%- capture adminURL %}{[ entityadminlink entityid:'{{ linkableEntityID }}' entitytypename:'{{ linkableEntityTypeName }}' urlonly:'true' ]}{% endcapture -%}
                   {%- if entityTypeID != prevEntityTypeID -%}
                       {%- if forloop.first == false %}
                           {%- if 1 == 2 %}<dd>{% endif %} //- trick Rock into not showing the missing HTML tag message on save
                       </dd>
                       {%- endif -%}
                       <dt>
                           <i class="{{ reference.EntityIconCSSClass | Default:'fas fa-cube' }} fa-fw"></i>
                           {{ entityTypeName | Pluralize }}
                       </dt>
                       <dd>
                       {%- assign prevEntityTypeID = entityTypeID -%}
                   {%- endif -%}
                   {%- if adminURL != '' -%}
                           <a href="{{ adminURL }}" class="text-small"><small>{{ reference.Name }}</small></a><br>
                   {%- else -%}
                           <small>{{ reference.Name }}</small><br>
                   {%- endif -%}
                   {%- if forloop.last == true -%}
                       </dd>
                   {%- endif -%}
               {%- endfor -%}
           {%- endif -%}
                   </dl>
               </div>
           </div>
       </div>
   </div>
       {%- endif -%}
   
   <script>
       // Run script on partial post backs.
       Sys.WebForms.PageRequestManager.getInstance().add_endRequest(function() { moveReferenceList(); });
   
       // Run script on document ready
       $(document).ready(moveReferenceList);
   
       function moveReferenceList()
       {
           var $refList = $('#workflowtype-reference-list');
           var $refPanel = $refList.closest('.panel');
           
           $refList.detach();
           $refPanel.remove();
           
           $('.description').siblings('.row').children().first()
               .removeClass('col-md-12')
               .addClass('col-md-6')
               .after($refList);
               
           $refList.show();
       }
   </script>
   {%- endif -%}

5. Save the HTML block and reload the page. When viewing a Workflow Type on the Workflow Configuration page, you should now see a nice list of other entities that reference it.

Follow Up

Please don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe.

If you come up with better or more efficient ways of doing anything in this recipe, please let me know. Thanks!

---

Change Log

• 2023-09-22 - Initial Version
• 2023-11-20 - Removed parts of the SQL function script that are not supported by Rock's SQL Command page

Screenshots

• /GetImage.ashx?guid=fc830c7c-50aa-46b6-8a72-5b103fd64cff
• /GetImage.ashx?guid=8448534e-6a49-4f84-9466-ecf3fd09dfbd