Web Apps – Resource Planning Search

I wanted to add some custom filters to the resource planning available grid. I converted our ‘Lookups’ to be a Bootstrap modal and by default most of the Resource Planning module uses the ‘PV’ to get values. Since I had already built out the Lookups piece to fit my needs, I converted Resource Planning to use Lookups_Partial rather than PV_Partial. To accomplish this, I altered the DSLLookup function which can be found in your ResourcePlanning.js. By adding an if statement, I am forcing all my possible FilterField_Strings to use DSLLookupPartial. Be warned, you have to write your own SQL Stored procs if you go this route!

DSLLookupPartial runs 2 important steps related to all Web Apps Lookups.

  1. GetLookupString – this is a function that gets the stored procedure that is used for the Lookup – along with the number of parameters for the proc
  2. GetSearchFields – this function returns the fields that you can actually use to do the searching

So, any custom lookup you add needs to be added here. These functions are usually found in the UIHelper of that particular project (so ResourcePlanningUIHelper)

I added all my custom lookups into these functions, but we’ll focus on the Business Unit Lookup. Since I am not passing any parameters to this stored procedure to begin with, Parm1 and Parm2 are ‘Search Only’ – you might add a parameter here if you wanted to limit the list even before the user searches (Ex. only tasks for the previously selected project)

We are going to allow the user to search for their results on either our ‘Business Unit number’ or ‘Business Unit name’. In the GetSearchFields(), I am defining the column names and which Parm they represent (must match the columns in your SQL stored proc) here.

Add your SQL Lookup

At this point, you need to create a stored procedure, if you haven’t already. Most lookups in Web Apps use dynamic SQL and I like to follow that pattern for any custom lookups. Below is the procedure I used for our Business Unit Lookup, which looks up values from PJCODE. All Web Apps Lookups parameters are prepended with @page, @size, and @sort, so you must add these before your own @parameters.

It may be frowned upon, but I allow our employees to search for both values in the @Parm1 search (so they don’t have to click over between the 2) .

And no, I would not normally use spaces in my column names, but this is how it appears for end users in Web Apps, so….

IF OBJECT_ID('zsp_BusinessUnitLookup') is not null drop proc zsp_BusinessUnitLookup;
go
CREATE PROCEDURE [dbo].[zsp_BusinessUnitLookup]
 @page  int
 ,@size  int
 ,@sort   nvarchar(200)
 ,@parm1 varchar (15) -- Buiness Unit ID
 ,@parm2 varchar (40) -- Business Unit Name
AS

--created 2/27/19 by MSDE to use in resource planning lookup 

--exec zsp_BusinessUnitLookup '1','20','','%','%'
  SET NOCOUNT ON
  DECLARE
    @STMT nvarchar(max), 
    @lbound int,
    @ubound int

	--select * from PJCODE where code_type = '0BUS'
    
    IF @sort = '' 
	BEGIN
		IF @parm2 IS NOT NULL AND LEN(@parm2) > 1 SET @sort = 'code_value_desc'
		ELSE SET @sort = 'code_value'
	END

	IF @sort = 'BusinessUnitID' SET @sort = 'code_value'
	ELSE IF @sort = 'BusinessUnitName' SET @sort = 'code_value_desc'

	SET @sort = 'code_value'
	
  IF @page = 0  -- Don't do paging
	  BEGIN
		SET @STMT = 
			'select c.code_value [BU Nbr], c.code_value_desc [BU Name]
				from PJCODE c
				where c.code_type = ''0BUS''
					and (c.code_value like ' + quotename(@parm1,'''') + ' OR c.code_value_desc like ''%' + @parm1 + '%'')
					and c.code_value_desc like ''%' + @parm2 + '%''
					and c.data4 <> ''123456''

			 ORDER BY ' + @sort
	  END		 
  ELSE
	  BEGIN
			IF @page < 1 SET @page = 1
			IF @size < 1 SET @size = 1
			SET @lbound = (@page-1) * @size
			SET @ubound = @page * @size + 1
			SET @STMT = 
				'WITH PagingCTE AS
				(
				SELECT TOP(' + CONVERT(varchar(9), @ubound-1) + ')  c.code_value, c.code_value_desc ,
				ROW_NUMBER() OVER(
				ORDER BY ' + @sort + ') AS row
				from PJCODE c
				where c.code_type = ''0BUS''
					and (c.code_value like ' + QUOTENAME(@parm1, '''') + ' OR c.code_value_desc like ''%' + @parm1 + '%'')
					and c.code_value_desc like ''%' + @parm2 + '%''
					and c.data4 <> ''123456''
			   )
				SELECT code_value [BU Nbr], code_value_desc [BU Name]
				FROM PagingCTE                     
				WHERE  row > ' + CONVERT(varchar(9), @lbound) + ' AND
					   row <  ' + CONVERT(varchar(9), @ubound) + '
				ORDER BY row'
	  END				
exec (@STMT) 
           
GO

Updating from standard search

That’s the basics for adding a new lookup into web apps. However, I have also changed the search interface in an attempt to make it easier on the end users to filter the results. I created a dropdown button in the vbhtml labelled ‘ Filter List By’ with all my options for filtering.

Each search item available in the dropdown list has its own function for ‘Search…..’ which runs a custom ClearFilter function (to remove any existing search data), populates that selection into the FilterField, and presses the lookup button to take the user directly to the Lookups. I have included the code to do this:

 /* MSDE added following custom JS 1/25/19*/
function ClearFilter() {
    grid = GetGrid('ResourceFilterGrid');
    grid.Clear();
}


function SearchBusinessUnit() {
    ClearFilter();
    ResourceFilterGrid.AddRow(true);
    ResourceFilterGrid._rows[0].Cells["FilterField"].Value("ResourceBusinessUnit");
    $('[data-gridfieldname="FilterValue"] a')[1].click();
}

When a user selects their filter from the lookup, I didn’t want them to have to come back to the Filter grid to hit submit, close, etc. I added code to the ValidateSuccess function in ResourcePlanning.js; again, forcing each lookup item to run ‘OnFilter’.

If someone wants to search on multiple items, I did add an ‘Add Another Filter’ option that opens the Filter Grid w/o clearing it.

My new custom search:

Feel free to share...

Leave a Reply

Close Menu