Using Variable to filter List

rfernandez

Hi,
In a Form I'm filtering the records of a list using a variable of type relation/multiple values

I open the Form, and as expected nothing loads in the list, as the contents of the variable is NULL and the filter of the list does not match.
I select a value in the variable (which displays a list of values selected from the relation), and I get this instantly:

I tested it only changing the variable to a relation/single value, and it works fine.
What is the cause of the error when using a multiple values variable?

This is the filter of the list component:

Thank you !
Warm regards

gabriel

Your comparing an integer (book.id) against an array (multi-value variable), which is not compatible.

You have 2 options:

Keep the multi-value variable and use a array operator ( =ANY or <>ALL) to filter for all/none of the book IDs in the variable.
Change the variable to single value and keep your current equal operator = to filter for a single book ID in the variable.

rfernandez

Yes I realized the variable is returning an array element and the filter expecting an integer, so type mismatch.
But none of the operators work. They all fail just when trying to preview the SQL in the builder.
I'd already tried them and also the array operators just in case.

Shouldn't the value be passed instead of the array element? Or this is something that can just be solved in code?

gabriel

The variable is not returning an array element (single value) - it is returning an array (list of many values). If it were just an element, the comparison against a single integer would be valid. But because it´s an array, you can only use array operators.

We are using this kind of filter regularly without issue. Ignoring the SQL preview for a second, does the filter with an array operator work in the regular form? (ie. book.id = ANY variable XY).

rfernandez Shouldn't the value be passed instead of the array element? Or this is something that can just be solved in code?

If you want to deal with a single value, you need a single value variable. If you want to use multi-values, you want an array to be passed so that you can filter for all values in this array.

rfernandez

Ok.
I followed your advice and ignored the SQL preview.

On opening the Form I got an error due to the null value of the variable.

And I solved it with this:

So when the form opens it displays all the records, and after you start selecting from the variable, it starts filtering.

Thank you very much!!
Have a great weekend!!

gabriel

A NULL value is valid as comparison and also works in array operators - though it would result in the expression being FALSE as comparison against NULL is always FALSE. This is why a OR IS NULL filter can make sense depending on the desired list query.

I will have a look at whether the SQL preview function has an issue. But the filter must work as many r3 apps depend on it.

The only reason I know, why a comparison could fail, is if the variable value is defined as something that it is not. Like saysing you have a multi-value variable but setting the value to 12 instead of [12]. Then the query would expect an array but would get a single value.

rfernandez

Hi Gabriel,

I fully agree, the isnull condition should suffice, and in fact I've just tested without the true=true condition and it still working.
At the time I added true=true, I was rewriting the whole condition so probably something else was preventing the records to show at form load, but now the condition is like this, and it works as expected for a where condition in an SQL statement.

This is the SQL statement generated:

SELECT "r0"."id" AS "e0", "r0"."name" AS "e1", "_r0"."id" AS r0id
FROM "desarrollo_sostenible"."book" AS "r0"
WHERE (((NULL IS NOT NULL
AND "_r0"."id" = ANY ($1))
OR NULL IS NULL ))

If I understood correctly, variables are NULL until a value is assigned. So when the form loads the variable is NULL, and that is why the isnull condition succeeds.
I guess that something went wrong with my REI3 instance yesterday, because now I've delete the isnull condition and its not showing the error on form load nor in the SQL preview anymore, it just doesn't load the records of the list, as the condition resolves to false, and when I put it back records are shown in the list even before selecting any value for the variable.

Anyway, now its working according to what should be expected. What happened yesteday and that strange behavior, I can't say why.

Thank you!

gabriel

Thank you for checking again. I do not get the error in the SQL preview either.

Always fun trying to recreate issues that only appear in very specific configuration combinations.

rfernandez If I understood correctly, variables are NULL until a value is assigned. So when the form loads the variable is NULL, and that is why the isnull condition succeeds.

Yes, this is the case. NULL is preassigned until overwritten by anything.