I Have an Idea! Web and Mobile application, we have prepared a screen design that will perform a filtering function in line with certain conditions on the Idea List screen. Now let's create a SQL action to make this screen functionally functional.
I Have an Idea! Web and Mobile application, we have prepared a screen design that will perform a filtering function in line with certain conditions on the Idea List screen. Now let's create a SQL action to make this screen functionally functional.
In our application, we have added three controls to our screen so that users can find ideas more easily. Thanks to these controls, we can filter the screen. In the first text entry, we search by idea title. Through the Select Box, we select and filter by category. In the second text entry, we filter by the name of the person who created the idea.
An SQL action is required to perform these filtering operations.
The screen design and functions are similar to the Dashboard screen we prepared before. At this point, let's copy the content of the SQL query in the custom action we created for the Dashboard screen to make the process easier and faster.
We need to add some conditions to the SQL query to execute filtering operations. As seen on the screen, we add the relevant condition structure to the action.
WHERE
(LOWER(i.IdeaTitle) LIKE LOWER(CONCAT('%', @IdeaTitle, '%')) OR @IdeaTitle IS NULL OR @IdeaTitle = ''') AND
(i.IdeaCategoryID = @IdeaCategoryID OR @IdeaCategoryID IS NULL) AND
(LOWER(p.FullName) LIKE LOWER(CONCAT('%', @IdeaUser, '%')) OR @IdeaUser IS NULL OR @IdeaUser = ''')
The purpose of these conditions is to make the query dynamic according to the values users enter.
The first condition allows us to filter by the idea title. The text entered by the user is searched in the idea title and matching records are retrieved. If the user does not enter a value or leaves it blank, this condition covers all records and all ideas are fetched.
The second condition allows us to filter by category. If the user selects a category from the SelectBox, only ideas belonging to that category will be retrieved. If the category is not selected or the value is empty, this condition also covers all categories.
The third and last condition allows us to filter by the name of the idea creator. The name entered by the user is searched against the name of the idea owner and matching records are retrieved. If the user does not enter a name or leaves it blank, this condition also covers all records and all idea owners are retrieved.
In these conditions, the LOWER function is used to eliminate case sensitivity and the LIKE operator and the '%' character provide a flexible search, so that a match anywhere in the text is sufficient.
With this query, a dynamic data extraction process is realized by taking into account the filtering choices made by the users on the screen. We have added these filtering features to improve the user experience in our application and enable them to easily access the information they are looking for. We complete the process by updating and saving the action. Now in our app, users are ready to filter ideas by title, category and the name of the idea owner.
