Populating Control Set entity pickers with custom data

Create custom database stored procedures to populate Control entity item pickers

Applies to

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

Overview

When an entity picker is added to a Control Set, there are a number of pre-configured options that can be set that will determine how the control will be populated. This will change depending on the type of entity being displayed.

For example, a client picker control can use the following options:

  • 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.

Client/site picker population via custom stored procedure

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 definition of the Control Set for the StoredProcName property.

Example stored procedure

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

  • stpControlSetCustom_Clients_Managers
  • stpControlSetCustom_Sites_TopLevelSites
  • stpControlSetCustom_Assets_AvailableForLoan

Use these examples as the basis of creating your own stored procedures. Update the “Where” clause as appropriate. For assistance with creating custom stored procedures, please contact PRD Software

-- This procedure will return a list of clients that are managers (they are nominated in other client's "Manager" field)

CREATE PROCEDURE [dbo].[stpControlSetCustom_Clients_Managers] 
	@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
	@PrimaryClientLink int, --- The primary Client of the entity
	@PrimarySiteLink int, --- The primary Site of the entity
	@PrimaryAssetLink int --- The primary Asset of the entity
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 -- 1=Client
	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 -- Non-deleted clients only

	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 -- 1=Client
	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 -- Non-deleted clients only

		-- 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
-- This procedure will return a top-level sites only

CREATE PROCEDURE [dbo].[stpControlSetCustom_Sites_TopLevelSites] 
	@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
	@PrimaryClientLink int, --- The primary Client of the entity
	@PrimarySiteLink int, --- The primary Site of the entity
	@PrimaryAssetLink int --- The primary Asset of the entity
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 site selection and display
	SELECT 
		PKID,
		Site AS Display,
		'' AS DisplayRight, -- NOT USED, BUT REQUIRED
		'' AS DisplayRightType, -- NOT USED, BUT REQUIRED
		EntityType=2 -- 2=Site
	FROM tblSite

	-- 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 
		SelfLink IS NULL -- Top level
		AND ISNULL(tblSite.IsDeleted,0) = 0 -- Non-deleted sites only
	ORDER BY Site -- Change according to preference

ELSE

	SELECT PKID,
		Site AS Display,
		'' AS DisplayRight, 
		'' AS DisplayRightType, 
		EntityType=2  -- 2=Site
	FROM tblSite

	-- Customize the filter to select the clients here
	-- IMPORTANT!!  The "WHERE" clause here should match the "WHERE" clause above
	WHERE 
		SelfLink IS NULL -- Top level
		AND ISNULL(tblSite.IsDeleted,0) = 0 -- Non-deleted sites only

	-- DO NOT EDIT THE LINES BELOW.  These are used to filter according to the user input
	AND (
		(Site Like @FilterText + '%') OR (CHARINDEX(' ' + @FilterText, Site) > 0)
		)
	ORDER BY Site -- Change according to preference
-- This procedure will return an asset list for any assets that have the status "Available for loan"

ALTER PROCEDURE [dbo].[stpControlSetCustom_Assets_AvailableForLoan] 
	@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
	@PrimaryClientLink int, --- The primary Client of the entity
	@PrimarySiteLink int, --- The primary Site of the entity
	@PrimaryAssetLink int --- The primary Asset of the entity
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 Asset selection and display
	SELECT 
		AssetPKID AS PKID, 
		ISNULL(qryAssetDetails_Base.AssetID, '') + ' - ' + ISNULL(qryAssetDetails_Base.AssetType , '') + ' (' + AssetStatus + ')' AS Display,
		'' AS DisplayRight, -- NOT USED, BUT REQUIRED
		'' AS DisplayRightType, -- NOT USED, BUT REQUIRED
		EntityType=4
	FROM qryAssetDetails_Base

	-- 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 
		qryAssetDetails_Base.AssetPKID IN 
			(Select PKID FROM tblAssets Where AssetStatusLink =385) -- "Available for loan"
		AND ISNULL(qryAssetDetails_Base.IsDeleted, 0) = 0

	ORDER BY qryAssetDetails_Base.AssetID  -- 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 Asset selection and display
	SELECT
		AssetPKID AS PKID, 
		ISNULL(qryAssetDetails_Base.AssetID, '') + ' - ' + ISNULL(qryAssetDetails_Base.AssetType , '') + ' (' + AssetStatus + ')' AS Display,
		'' AS DisplayRight, -- NOT USED, BUT REQUIRED
		'' AS DisplayRightType, -- NOT USED, BUT REQUIRED
		EntityType=4
	FROM qryAssetDetails_Base

	-- Customize the filter to select the Assets here
	-- IMPORTANT!!  The "WHERE" clause here should match the "WHERE" clause above
	WHERE 
		qryAssetDetails_Base.AssetPKID IN 
			(Select PKID FROM tblAssets Where AssetStatusLink =385) -- "Available for loan"
		AND ISNULL(qryAssetDetails_Base.IsDeleted, 0) = 0

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

	ORDER BY AssetID -- Change according to preference

v24 breaking change

If upgrading to v24 (or higher) from a previous version AND you use custom stored procedures, please follow these instructions to correct a breaking change.