Category: Datasources
Resource links
Updated

This solution is summarized from an archived support forum post. This information may have changed. If you notice an error, please let us know in Discord.

How to Use an array in query (where based on checkbox)

Issue

In my query, i want to filter the where clause based on a checkbox. To transform the array to a readable list in the query i use the selectedValues.join(', ') method. For one value this works, but as soon as i check multiple values this fails. My datasource is MS sql server

SELECT  
locatie,  
“sdf potmaat”,  
teelt,  
uitgangsmateriaal,  
leverancier
FROM stamgegevens.producten
WHERE locatie IN ({{CheckboxGroup1.selectedValues.toString()}});
Resolution

Turn off Prepared Statements, and use code similar to this:

WHERE locatie IN ({{“'” + CheckboxGroup1.selectedValues.join(“', '”) + “'”}});

There is a known bug where the IN and ANY clauses do not work with Prepared Statements turned ON for MS SQL. The workaround for now is to turn off the Use Prepared Statement option. See related issue.