6 Find Where Workflows Are Being Used Shared by Jeff Richmond, The Well Community Church one year ago 12.0 Workflow Advanced 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 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 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 > 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. 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. 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 }} > {{ name }}</small></a><br> {%- else -%} <small>{{ entityDetails.LongName }} > {{ 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 -%} 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