Query editor : "In" clause with parameter [Done]

Hi,

It is possible to have an equal filter with parameter but impossible to have the same with the “IN” clause. Then “IN” clause requires only constants. What could be simple is to have a parameter syntax like (A,B,C) and in the query, the IN clause generate IN (A,B,C). More complex but suitable : deal with check list parameters instead of simple input textbox. Then you could have an IN clause with parameter.

Of course it is still possible to have a list outside the query editor and to join with to make filter but when you have a big table and you absolutely want to filter on SQL values to parameter before importing, in the current version you have to import the whole table first and then join to the list which is a problem.

It’s also possible to type SQL code “IN {parameter}” with the parameter but it’s not comfortable for users who don’t know about SQL : they won’t be able to do it alone or to change it after.

How would IN ({A},{B},{C}) be different from x={A} OR x={B} OR x={C} ? What’s the advantage?

What if you have one hundrer values in the list :sweat_smile: ?
Of course if you have 2 or 3 values you could create 2 or 3 parameters. But with 100 …

Just to make sure I understand you – are you implying that all 100 values should be provided by a single parameter?

Also check out the “Select matching database rows” action in EasyMorph. It allows filtering records against a list of values right in the database. It requires write access permissions as it creates a temporary table.

There can be many ways to do it. You could have one parameter that contains every values like parameter = {A,B,C …} and you just enable to have a filter “IN {parameter}”. Or you could deal with parameters as list of values instead of simple text field, it would be cleaner. A parameter can be a string yes but also a list of string, why not :slight_smile: And other solutions i don’t know. The goal is to filter by list of values defined by the user, whatever the way to do it.

Is there a solution to this? It’s been idle for 18 months…

This issue will be addressed in one of the following releases with introduction of new parameter types.

You can also create an expressions in the Query builder with the IN condition and a parameter and then use that expression in the visual mode of the condition builder.

1 Like

The capability has been added in version 4.5.1. It works as follows:

1) Create a parameter of the “Multiple choice” type

In the Parameter Editor, create a new “Multiple choice” parameter and populate the list of acceptable values.

:bulb: Tip: To quickly populate the list of acceptable parameter values from a database field, you can copy it from the list of field values and paste it in the parameter properties.

image

2) Use the parameter in a query condition

Create a condition of type “In list”. In the condition properties specify that a parameter should be used.

Note that in this condition mode only parameters of type “Multiple choice” can be used.