Persist List Box selections after content refresh

Good afternoon, I have been working on case status page that allows the user to select one or many people from a list box.   This list box selection feeds SQL that updates a table which displays activities that the selected users created.   It works great, but I'm wondering if there is a way to persist the selections so the user does not have to re-select their entries.   This doesn't have to be stored outside of sessions, but it would be nice if I could "remember" the selections for the active session.


For example, if I select 50 of the 200 people in my list and load the report, the page refreshes and the report is displayed but the List Box defaults to no names selected.   So if I change the time frame filters on my report I then have to go back and re-select all 50 names so the report can be re-run.


Also, in my SQL query I am utilizing the 'IN' operator.   I have a comma delimited list that I pass in like "'A','B','C'".   If I use the EncodeSQL() function on my input the query fails but if I remove it and just pass in the comma delimited list the query runs perfectly, but there is a warning about security vulnerabilities due to not using EncodeSQL.  Any ideas?


Hi Josh Herron,

For example, if I select 50 of the 200 people in my list and load the report, the page refreshes and the report is displayed but the List Box defaults to no names selected.   So if I change the time frame filters on my report I then have to go back and re-select all 50 names so the report can be re-run. 

Here you might have button to fetch report right ? while calling reports pass all selected record id's ( may be comma separated ) and while loading report you can select records based on input ids sent from fetch report button. so that you will not lose the selection.


Also, in my SQL query I am utilizing the 'IN' operator.   I have a comma delimited list that I pass in like "'A','B','C'".   If I use the EncodeSQL() function on my input the query fails but if I remove it and just pass in the comma delimited list the query runs perfectly, but there is a warning about security vulnerabilities due to not using EncodeSQL.  Any ideas?

Can you try using query like below ?


Sravan

Hey Sravan,

What version of SQL are you running? (You can find this out by running SELECT @@version)

If you are using MSSQL server 2017 and above you can use a couple of new functions, one of which is the awesome STRING_SPLIT

You can add a comma seraparted string "color, size" as an input in your advanced sql.
And use it as follows:

WHERE Name IN (SELECT value FROM STRING_SPLIT(@input, ','))


You will only have to build a comma seperated list yourself, but there are actions in outsystems which can help you with that. I haven't tried it out myself yet (running on 2015 myself), but perhaps you can even avoid the security warnings with this.

Source: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Sraven, yes I am passing the IDs that were selected when the button is pressed and it works fine in the query.   However, when the page loads the selection box defaults to no names selected.  (nothing is highlighted in the list box)   I would like to persist all of the selected names during the session so if the user wants to change the dates of the report, or the categories they don't have to reselect the names again.    In the background I still have the names saved so if they hit the button to load the report the selected names are still included in the query... but this is confusing to the end user because they don't know that.  


Sraven and Joey, if I hard code my list as 'A','B' it works with the IN clause in my query.   It's the warning about SQL injection that I'm fighting with.   Unfortunately we are not on SQL Server 2016 so I can't use STRING_SPLIT.

I found a way to persist the selected list box values.   I created a structure with the name and isSelected attribute then I created a local variable of type List of structure.   In the preparation I check to see if this variable is empty and if so I populate it with the list from my aggregate, if it is not I leave the local variable set which keeps the selected list box items set to isSelected = True.


I also have my SQL query working with the IN operator, but I still have the SQL injection warnings.  I am checking my input with the VerifySQLLiteral action but I can't find a way to use EncodeSQL() and keep the appostrophe's in place on my comma delimited string list.   I believe the VerifySQLLiteral check is sufficient so I will probably leave the warnings in place and add comments in the action letting other developers know what's up.

Nice solution Josh!

It's also very nice you add comments to let other devs know about it, I wish more people thought like that, haha.

Though if a user refreshes the screen the user will lose the selections, you could prevent this by setting the local variable up as a session (yugh), or keep a seperate entity where you store the selections for each user, which has a specific lifetime (or gets cleared upon login?)

Joey Moree wrote:

Nice solution Josh!

It's also very nice you add comments to let other devs know about it, I wish more people thought like that, haha.

Though if a user refreshes the screen the user will lose the selections, you could prevent this by setting the local variable up as a session (yugh), or keep a seperate entity where you store the selections for each user, which has a specific lifetime (or gets cleared upon login?)

Yep, I'm working on the session variable now.    That seems like the preferable solution for this problem.   :)