Custom WebApp Page - Filter page / column based on comma separated values

Answered

Joseph Flesche

• January 17, 2023 12:48

I have received quite a few requests from end users to be able to filter on a list of values (comma, semicolon, or carriage return separated). As far as I know, this is not possible within the DSP framework.

For the time being, I have told the end users to download the data and then use a VLOOKUP in Excel. Not an ideal (or fast) solution, but it does work.

Is there a way to paste a list of values and allow DSP to perform an IN statement as opposed to a LIKE statement in the filter section?

The only solution that I can think of would be custom and as follows.

1. Create a page that allows for end users to enter the required values.
2. Create a function, stored procedure, table valued function, etc, that will transform this data into a (temp or permanent) table
3. Click button (or something similar) to navigate to another page that will join back to this data set.

Not pretty, but it would work. The problem is that it is not dynamic and the fields available for filtering would need to be hard coded into a view.

As I type this, I realize that there's a lot here. Just trying to get my thoughts out on a potential solution and see if there is something else available out there within the DSP framework.

0

• Best answer

  

  Ben Bauer

  • January 17, 2023 16:44

  Hi Joe,

  Sadly, I can only confirm your analysis that there is no framework feature that permits IN filtering (versus LIKE filtering). I'm sure a more complex custom add-on could provide something more dynamic, but you'd still have to register specific pages and columns into that add-on. In general, a feature like this is something you should request (or upvote) in the Syniti Ideas Portal.

  0
• 

  Guillaume Brochet

  • January 17, 2023 19:11

  Hi Joseph,

  I have submitted an "Idea" on Syniti portal, regarding enhancing the filtering framework : "filter out" meaning being able to exclude a result.

  https://syniti.ideas.aha.io/ideas/MIGRATE-I-256

  This idea is currently in status "Future consideration", dont hesitate to comment it with your feature, maybe it adds up ;)

  0
• 

  Joseph Flesche

  • August 09, 2023 15:57
  • Edited

  I decided to creating a custom solution for this requirement.

  The page / functionality is presented to an end user like this.

  

  This custom solution allows for an end user to do the following.

  1. Select a page on the top window and save the record.
  2. Field names are populated and displayed to the end user in the bottom window.
  3. User chooses to display a field or not (default to display all).
  4. User chooses to change the display order for a field (default to view display order).
  5. User chooses filter type (=, LIKE, IN) including a NOT statement.
  6. User clicks on magnifying glass to dynamically create a page for the end users to display.

  The magnifying glass button is setup as Dynamic View.

  

  The ViewPage button is setup to query the tables and dynamically create the required query. I use FOR XML because of the SQL Server version that we use here. Otherwise you can use STRING_AGG.

  SELECT boaStatus,
       FilterID,
       PageID,
       FilterName,
       [User],
       'SELECT '
       + COALESCE( Stuff(COALESCE( (SELECT COALESCE(',[' + d.FieldName + ']', '') FROM [SAP_FilterDetails] AS d WHERE d.FilterID = f.FilterID AND Display = 1 ORDER BY SortOrder FOR XML PATH(''), TYPE).value('.', N'nvarchar(max)'), ''), 1, 1, ''), '*')
       + ' FROM '
       + (SELECT TOP 1 ViewName
          FROM   [dbo].[webPMAppsFilterPagesList] AS p
          WHERE  p.PageID = f.PageID)
       + ' WHERE 1 = 1 '
       + COALESCE( (SELECT COALESCE('AND ' + CASE WHEN [Not] = 1 THEN ' NOT ' ELSE '' END + '[' + d.FieldName + '] ' + d.[Type] + ' ' + d.Criteria, '') FROM [SAP_FilterDetails] AS d WHERE d.FilterID = f.FilterID ORDER BY 1 FOR XML PATH(''), TYPE).value('.', N'nvarchar(max)'), '') AS ViewPage,
       AddedOn,
       AddedBy,
       ChangedBy,
       ChangedOn
  FROM   [dbo].[SAP_Filter] AS f 

  Pros

  • Allows an end user to "save" a search.
  • Allows an end user to "modify" a search easily.

  
  Cons

  • Page functionality (buttons, stored procedures, etc) do not come over to the dynamically created page.

  0

Please sign in to leave a comment.