Control Set / Entity Item Custom Data

Applies to

This information only applies to the Client and Site picker controls for a Control Set, or Entity Items.

Overview

When a client or site picker is added to a Control Set or an Entity Item, there are a number of pre-configured options that can be set that will determine how the control will be populated. By default, you can select from the following set values:

• Clients of logged in users site:  Will only display clients from the same site of the current logged-in user
• Staff members:  Will display all staff members
• Clients only: Will display all clients
• All staff clients: Will display both clients and staff

The last option is “CustomStoredProcedure”

When the “Selector type” is set to “CustomStoredProcedure”, the control will be populated from a database stored procedure that can be developed to suit the needs of the business case. The format and structure of the stored procedure needs to adhere to a known format as stated below, or the control will not work, and will result in an application error being thrown.

Using a custom stored procedure

When using a custom stored procedure to populate the drop-down list for either a client or a site, the database stored procedure should strictly adhere to the following format. The “Where” clause and other sections of the SQL may be adjusted to suit the population requirements. Adjust the SQL template below, and feel free to reach out for assistance from PRD Software for further information.

The stored procedure has 2 main parts - based around the @FilterText parameter. This parameter is automatically passed into the stored procedure by HelpMaster, and contains the input text that the user is typing into the client/site picker control. This is used to filter the results.

The stored procedure should use the same “Where” clause in each part of the IF/ELSE block to “find” the correct clients/sites.

Refer to the comments in the SQL below for further explanation on how the stored procedure works.

Naming the stored procedure

You can create as many stored procedures as you require and call them whatever you like. Use the name in the definiton of the Control Set for the “StoredProcName” property.

Example stored procedure

The HelpMaster database contains 2 sample stored procedures that may be copied and/or modified for use. There is 1 for clients, and 1 for sites. They are named:

• stpControlSetCustom_Clients_Managers
• stpControlSetCustom_Sites_TopLevelSites

The following sample stored procedure will query clients and return only those that are managers.

-- For further information about how to create custom stored procedures for Control Sets, please refer to the online documentation at https://docs.helpmasterpro.com/

ALTER PROCEDURE [dbo].[stpControlSetCustom_Clients_ManagersOnly] 
	@FilterText nvarchar(255), -- If the user starts typing into the control, their text is passed through here and is used as a filter
	@EntityType int, -- The entity type that this control is being used on.  Values are: 1=Client, 2=Site, 4=Asset, 5=Job
	@EntityLink int, -- The unique ID of the entity
	@LoggedInUser int -- The unique ID of the user accessing this control
AS
IF (@FilterText IS NULL) -- No user input/filter has been passed in yet.  Populate the control without any filtering.  This is the default data set
	
	-- Base client selection and display
	SELECT 
		PKID, 
		ISNULL(tblClient.FirstName, '') + ' ' + ISNULL(tblClient.LastName, '') + ' (' + ClientID + ')  -  ' +  ISNULL(tblClient.JobTitle,'') AS Display,
		'' AS DisplayRight, -- NOT USED, BUT REQUIRED
		'' AS DisplayRightType, -- NOT USED, BUT REQUIRED
		EntityType=1
	FROM tblClient

	-- Customize the filter to select the results here.
	-- IMPORTANT!!  The "WHERE" clause here should match the "WHERE" clause used in the ELSE section below
	WHERE 
		PKID IN 
			(Select ManagerLink FROM tblClient) 
		AND ISNULL(tblClient.IsDeleted, 0) = 0

	ORDER BY FirstName  -- Change according to preference

ELSE -- The user has started typing / filtering the list.  @FilterText will be used in the "Where" clause to filter the results according to what the user has typed
	
	-- Base client selection and display
	SELECT
		PKID, 
		ISNULL(tblClient.FirstName, '') + ' ' + ISNULL(tblClient.LastName, '') + ' (' + ClientID + ')' AS Display,
		ISNULL(tblClient.JobTitle,'') AS DisplayRight,
		'Job title' AS DisplayRightType,
		EntityType=1
	FROM tblClient

	-- Customize the filter to select the clients here
	-- IMPORTANT!!  The "WHERE" clause here should match the "WHERE" clause above
	WHERE 
		PKID IN 
			(Select ManagerLink FROM tblClient) 
		AND ISNULL(tblClient.IsDeleted, 0) = 0

		-- DO NOT EDIT THE LINES BELOW.  These are used to filter according to the user input
		AND (
			(FirstName Like @FilterText + '%') OR (charindex(' ' + @FilterText, FirstName) > 0)
			OR (LastName Like @FilterText + '%') OR (charindex(' ' + @FilterText, LastName) > 0)
			OR (ClientID Like @FilterText + '%') OR (charindex(' ' + @FilterText, ClientID) > 0)
			)

	ORDER BY FirstName -- Change according to preference