Is there a database search by user to see how many Processes they are attached to?

asked on February 7, 2024

I am trying to find a way to search by Team User to see what Forms Processes they are associated with.

Looking for something below like what @Matthew Tingey replied back in January of 2022.

DECLARE @TeamNameSearch VARCHAR(100), @TeamID INT
SET @TeamNameSearch = 'Business Services'   --Enter the name of the team you want to search here
SET @TeamID = -1

SELECT @TeamID = [id]
FROM LF_Forms.dbo.teams
WHERE [name] = @TeamNameSearch;

SELECT
  bp.[name] AS process_name,
  step.[step_type],
  step.[name] AS step_name,
  step.[definition_json]
FROM [LF_Forms].[dbo].[cf_bp_steps] AS step
LEFT JOIN [LF_Forms].[dbo].[cf_bp_processes] AS bpp ON bpp.[process_id] = step.[process_id]
LEFT JOIN [LF_Forms].[dbo].[cf_business_processes] AS bp ON bp.[bp_id] = bpp.[bp_id]
WHERE step.[definition_json] LIKE CONCAT('%"teamId":', @TeamID, '%')
  AND step.[definition_json] LIKE '%"teamEnabled":true%'
  AND step.[is_deleted] = 0
  AND bp.[is_deleted] = 0
ORDER BY process_name asc;

Thanks for any ideas!

Pete

0 0

SELECT bp.[name] AS [process_name], CASE WHEN uir.[role_id] = 1 THEN 'Process Admin' WHEN uir.[role_id] = 3 THEN 'Submitter' WHEN uir.[role_id] = 7 THEN 'Business Manager' END AS [role], 'User' AS [type], u.[username] AS [username], u.[displayname] AS [displayname] FROM [LFForms].[dbo].[cf_users_in_role] AS uir LEFT JOIN [LFForms].[dbo].[cf_users] AS u ON u.[user_id] = uir.[user_id] LEFT JOIN [LFForms].[dbo].[cf_business_processes] AS bp ON bp.[bp_id] = uir.[bizprocess_applied] WHERE uir.[role_id] <> 0 UNION ALL SELECT bp.[name] AS [process_name], CASE WHEN ugir.[role_id] = 1 THEN 'Process Admin' WHEN ugir.[role_id] = 3 THEN 'Submitter' WHEN ugir.[role_id] = 7 THEN 'Business Manager' END AS [role], 'Group' AS [type], ug.[full_group_name] AS [username], ug.[full_group_name] AS [displayname] FROM [LFForms].[dbo].[cf_usergroups_in_role] AS ugir LEFT JOIN [LFForms].[dbo].[cf_usergroups] AS ug ON ug.[group_id] = ugir.[group_id] LEFT JOIN [LFForms].[dbo].[cf_business_processes] AS bp ON bp.[bp_id] = ugir.[bizprocess_applied] WHERE ugir.[role_id] <> 0 ORDER BY 1, 2, 3, 4

Question

Question

Is there a database search by user to see how many Processes they are attached to?

Replies

Sign in to reply to this post.