Skip to main content

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

Answered

Comments

3 comments

  • Best answer
    Ben Bauer

    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

    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

    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.