Blanks in Native DB/SQL Server

davidec

Hello!
I have a question about how to filter data (in a report) , so that only records with some field set to blank are considered.

I mean, suppose I needed all Item records where "No. 2" is blank.
What should I do when specifying DataItemTableView? And using SETFILTER?


I found a solution which works using SQL Server, but when I use it on a native Db, results are wrong. When specifying DataItemTableView I use something like ... WHERE(No. 2=CONST(" ")) but it's not consistent on different DBs...

I know the question sounds silly, but it tok me some time even to figure out the bugged version!!!

Thanks in advance!

matttrax

If you want to filter for a blank just use two single quotes

SETFILTER("No. 2", '')

or

WHERE(No. 2=CONST(''))

in a report. This does not catch fields that may have a bunch of spaces in them. Only fields where nothing has been typed in.

davidec

Thanks for the reply, but isn't that the way to remove previosuly set FILTERS?

That is, I thought if I used

Item.SETFILTER("No. 2",'1234');
...
Item.SETFILTER("No. 2",'');

the second SETFILTER removed the previously set one. If not, then how can I remove previously set filters? With Item.SETRANGE("No. 2")? But does it work if I previously set filters instead of ranges???

Thanks again!

einsTeIn.NET

Item.SETRANGE("No. 2");

kine

davidec wrote:

Thanks for the reply, but isn't that the way to remove previosuly set FILTERS?

That is, I thought if I used

Item.SETFILTER("No. 2",'1234');
...
Item.SETFILTER("No. 2",'');

the second SETFILTER removed the previously set one. If not, then how can I remove previously set filters? With Item.SETRANGE("No. 2")? But does it work if I previously set filters instead of ranges???

Thanks again!

1) When filtering to one value, use

SETRANGE("No. 2",'1234');

or

SETFILTER("No. 2",'%1','1234');

Your code can bring problems when you learn to use it in this way...

2) Setting filter to empty string will set empty filter if used with SETRANGE. In your example, there is logical problem, because if you write it in this way, you are not sure if it means empty filter or filer to empty string, and this can be confusing. It is why the SETRANGE(MyField); is used to clear the filters on the selected field. It is clear...

;-)