List Control Search Part Enhancement

[MikeKincaid]

Seems like the "Search Part Field Map" of the "Search Part" of the "List Builder" of the "List Control" should allow us to specify per mapped field one of the following:

• Equal to
• Contains
• Begins With
• Ends With
• Less Than
• Less Than or Equal
• Greater Than
• Greater Than or Equal

So we have more control. Things like from and to dates, etc.

Also, defaults on search part mapped controls don't seem to apply on initial list rendering.

Or is there a way I can build my own dynamic where condition and apply that to the list control?

[CharlesParker]

Re: List Control Search Part Enhancement

You could filter your list by an argument and change the value of the argument with a button click and a refresh - just an idea...

[peteconway]

Re: List Control Search Part Enhancement

Arguments are the way to go.

[MikeKincaid]

Re: List Control Search Part Enhancement

In the case I'm testing I'm adding some optional date ranges filter over three date fields.

Q: When using arguments in a SQL select statement, is there a way to dynamically exclude conditions when filter conditions (arguments) are empty?
For example when someone does not provide some filter values like a begin date.

I guess I could switch to calling a stored procedure instead for more dynamic conditional logic.

Still seems like this feature might be a nice add.
Or, having the option for the SQL where condition be returned by a function call would supply maximum flexibility.

[frankiez]

Re: List Control Search Part Enhancement

Without knowing all your requirements, here is a technique I use to avoid dynamic SQL strings. You can create a list control and add some search fields. The goal is to optionally filter as needed. If you only enter a start date, you expect all records on or after that date. If you enter a range, you expect a between operation. You can customize it anyway your application requires. If you need a contain search for city, just modify the where clause below to ( City LIKE '%' + :whatCity + '%' OR :whatCity = '' ). I use {dialog.object}.refreshListData('EMPLOYEELIST'); to refresh the list based on the search. Opening the UX with no data or searching in disconnected environment is another topic.

I attached a simple UX as an example using Northwind sample database. See EMPLOYEELIST list control data properties and arguments. See java script for the search and clear buttons. Note: The key is wrapping each search field in parentheses with an OR operator. (City = :whatCity OR :whatCity ='') will filter by the city or return a True given that :whatCity is set to zero length string if no value is provided.

Hope this helps.

SELECT EmployeeID, LastName, FirstName, HireDate, City
FROM Employees
WHERE
( City = :whatCity OR :whatCity = '' ) AND
( HireDate >= :whatHireDateStart OR :whatHireDateStart = '' ) AND
( HireDate <= :whatHireDateEnd OR :whatHireDateEnd = '' )
ORDER BY LastName, FirstName

[MikeKincaid]

Re: List Control Search Part Enhancement

Yep, that would work. Although I usually try and avoid ORs per performance issues, I think this would optimize pretty well since it is comparing literals.
Still, improving the search part logic might be nice.