Searchlists - Filter Retaining Nulls

We have created a searchlist called PLANPANEL. We have come up against two problems:
1. The dropdown filter where you select a planning agent (ie. Abby/Judith) doesn't filter the results.
2. We are creating a filter called PLANAGENT. We want to include null values in the results.

When selecting (not specified) in the drop down filter we want to return null values. We are currently using: planagent = COALESCE(@PLANAGENT, planagent) In the Query. How do we go about solving either issue?
__________________________________________________________________________________________________________________________________
Hi Cliff
Do you mean it doesn't filter when you select it even if you hit the Search button? We sorted that by creating a local object in the script WithEvents, referencing the DDL and when the selectedvalue changed forced a refresh of the search list.
If the filter doesn't work even after you press search then check that you are passing the value of the drop down to the search list as the parameter WHERE (planagent=@planagent or planagent IS NULL)

NB make sure the Or'd clause is in brackets if you have any other restrictions G
__________________________________________________________________________________________________________________________________
Graham, the second part of the where statement works but we can't seem to reference the dropdown box properly.
The search list is called PLANPANEL and the form is called DOCCHASERHEADER.
__________________________________________________________________________________________________________________________________
What's the name of the dropDown list? (I'm assuming you've attached the filter form to the search list) G
__________________________________________________________________________________________________________________________________
Hi,
Just re-read the original, if the value of @PLANAGENT then you would need to change the where clause to be

Code:


WHERE ( (@PLANAGENT IS NULL) OR (@PLANAGENT=PLANAGENT))


or if You only want ones that are 'not specified' then

Code:


WHERE Planagent IS NULL AND @PLANAGENT IS NULL G


__________________________________________________________________________________________________________________________________
I'm assuming that the DDL is linked to a DataList, in which case in your search list the linked value of the Parameter

Code:


@PLANAGENT should be %CBOAgent%


G
__________________________________________________________________________________________________________________________________
The name of the dropdown list (control name is CBOAgent) but it refers to planagent. The form just doesn't seem to be able to pick anything up from the dropdown box.
__________________________________________________________________________________________________________________________________
I've renamed the control to planagent to keep all the syntax correct. However, it appears as if the SQL query in Data Builder just isn't recognising the value the dropdown selection. The value is in a separate table from File and Client but the SQL query does specify those values.
__________________________________________________________________________________________________________________________________
Hi
Is the value you want passed to the filtered query the value member of the datalist rather than the display value?
G