Good day all-
I'm working with a table that's generated from a query, and this query can accept one of three different variables at a time: Creator, Photo Code, or Program ID. The table displays incentive program details including Creator, Photo Code, Program ID, Image, Date, and Approval status.
My goal is to filter the table based on one of these three fields—Creator, Photo Code, or Program ID—but only one filter should be active at a time. I'm running into trouble especially with Photo Code, since it's an integer and I can't seem to apply wildcard filtering to it.
Would it make sense to create three separate queries and link each to its own table, then use an If widget (or similar logic) to display the appropriate table based on the selected filter type? Or is there a cleaner way to handle this?
Thank you
Andy
I'm not sure if I understand correctly, but I believe the solution would be to use a single query that takes three optional parameters, but applies only the selected one, while the others will be null or empty.
For best practices, it's better to have one query than three database calls.
Hello.
That's two questions.
1. Usually you search by exact number, not LIKE. A simple trick is to do a calculated field with IntegerToText(PhotoCode) and then apply Like to it.
2. Regarding the multiple filters. You need 3 variables and 3 filters. All of them allowing null.
When you change one input field, you clear the other 2 variables so only one will have values.
This should fix both your problems.
Hi,One query that handles all three filters (Creator, PhotoCode, ProgramID) and only applies one filter at a time:
SELECT Creator, PhotoCode, ProgramID, ImageUrl, Date, ApprovalStatusFROM ProgramsWHERE ( @Creator IS NOT NULL AND Creator = @Creator ) OR ( @Creator IS NULL AND @PhotoCode IS NOT NULL AND CAST(PhotoCode AS VARCHAR(20)) LIKE '%' + @PhotoCode + '%' ) OR ( @Creator IS NULL AND @PhotoCode IS NULL AND @ProgramID IS NOT NULL AND ProgramID = @ProgramID )
How this works
If the user enters Creator, it filters by Creator.
If Creator is empty and user enters PhotoCode, it filters by PhotoCode (wildcard search).
If both above are empty and user enters ProgramID, it filters by ProgramID.
In OutSystems
Create one Advanced SQL node.
Add input parameters: Creator (Text), PhotoCode (Text), ProgramID (Text).
Paste the query above.
Bind the results to a single Table widget.