MySql: how to set an argument for a dynamic grid filter to NULL

[cptutrecht]

I need the exact same grid with 2 different filters, so i don't want to duplicate it.

On the a5w page in with the grid i created a session variable:

session.__protected__arg_berichtenmail_verzonden = null_value()

In the grid i defined an argument that gets populated with with this sessionvariable: arg_berichtenmail_verzonden

In the mysql query builder of the grid i have als where clause:

WHERE berichtenmail.BerichtenMail_Verzonden = :arg_berichtenmail_verzonden

It just doesn't show any records for which berichtenmail.BerichtenMail_Verzonden has NULL value's


If i set the session variable to "X" i get the correct results (records with berichtenmail.BerichtenMail_Verzonden = "X")

Thanks,

[aburningflame]

Re: MySql: how to set an argument for a dynamic grid filter to NULL

WHERE (berichtenmail.BerichtenMail_Verzonden = :arg_berichtenmail_verzonden OR :arg_berichtenmail_verzonden IS NULL)

[cptutrecht]

Re: MySql: how to set an argument for a dynamic grid filter to NULL

WHERE (berichtenmail.BerichtenMail_Verzonden = :arg_berichtenmail_verzonden OR :arg_berichtenmail_verzonden IS NULL)

Thanks Scott, but this solution is exactly my problem :-)

For one grid i need WHERE (berichtenmail.BerichtenMail_Verzonden = :arg_berichtenmail_verzonden
And for the other i need: WHERE (berichtenmail.BerichtenMail_Verzonden = :arg_berichtenmail_verzonden IS NULL)

So i was looking for a solution to pass NULL as argument OR to set the where clause dynamically in the grid. That would prevent the need to create exactly identical grids just because of the difference in where statement....

[aburningflame]

Re: MySql: how to set an argument for a dynamic grid filter to NULL

Oh ok sorry. I've done this once before. And I had to do it again today.
It's not well documented but here you go:

Take the filter off the grid and in your a5w page add:

If condition then
tmpl_x.base_filter="field=:arg"
Else
Tmpl_x.base_filter="field is null"
End if

[cptutrecht]

Re: MySql: how to set an argument for a dynamic grid filter to NULL

Thanks Scott this might exactly be what i was looking for! Will experiment with that

[aburningflame]

Re: MySql: how to set an argument for a dynamic grid filter to NULL

Here is a working example, the restaurant.food_category is "Chinese,Halal,Indian" etc.
The csvkeywords string in session is something like 'Desert,Chinese,Breakfast'

'compute the where string for this grid (heavily dynamic where clause with dynamic # of conditions)
dim wherestring as C=""
if eval_valid("session.__protected__csvkeywords") then

    'get keyword array
	dim ret as P=split(session.__protected__csvkeywords,",")
	for each keywd in ret.items
		if wherestring<>"" then
		    wherestring=wherestring+" OR "
		else
			wherestring="("
		end if
		wherestring=wherestring+"restaurant.food_category LIKE '%"+keywd+"%'"
	next
	wherestring=wherestring+") AND "
end if
wherestring=wherestring+"omit_list_online=0"

Here is the filter applied to the grid

dim componentAlias as c 
componentAlias = "mygrid"
Delete tmpl_mygrid
DIM tmpl_mygrid as P
tmpl_mygrid = a5w_load_component("mygrid") 'SYSTEM GENERATED COMMENT - PLEASE DO NOT REMOVE (LOAD:restaurant_list)
tmpl_mygrid.base_filter=wherestring ' do not remove this line !!!! Dynamic Filter Scott REA INC 2011-12-01