Apply filter on form to determine the view of the source tab

nalinee

Hi,

Can anyone tell me how to apply a filter on a form to exclude lines where for example Column 1 and Column 2 are null?

On the SourceTableView Property, when I am filtering
Column 1-Filter-<>'', Column 2-Filter-<>'',
it is filtering with OR condition and not with AND condition.

Thx for quick reply.

Rgds

Nalinee

lubost

Your first sentence "Can anyone tell me how to apply a filter on a form to exclude lines where for example Column 1 and Column 2 are null? " says AND and next senetences say OR.
What do you exactly want to do?

nalinee

I want to exclude lines with AND condition (Column 1 and Column 2 = null) not OR condition.

But the while I m trying to filter with SourceTableView Property, it is doing it with OR condition.

gulamdastagir

did you consider setting the required filter using SETFILTER in the Form - OnAfterGetRecord() trigger?

Mbad

nalinee wrote:

it is filtering with OR condition and not with AND condition.

It is filtering like it would if you apply filters to your table.

gulamdastagir wrote:

did you consider setting the required filter using SETFILTER in the Form - OnAfterGetRecord() trigger?

When applying as a standard view it should be coded on open form. That way you can still see all entries if you remove the filter.

gulamdastagir

Mbad wrote:

nalinee wrote:

it is filtering with OR condition and not with AND condition.

It is filtering like it would if you apply filters to your table.

gulamdastagir wrote:

did you consider setting the required filter using SETFILTER in the Form - OnAfterGetRecord() trigger?

When applying as a standard view it should be coded on open form. That way you can still see all entries if you remove the filter.

yeah you can aslo do that to see two empty columns on the form

muksha

Please see if table filters on the form with value not equal to null works.

nalinee

What I need to do is to be able to set filter on two fields to test that both are null.

SETFILTER(column1, <> '')
SETFILTER(column2, <> '')
has an OR operand.

What I need is an AND operand. How can this be done?

gulamdastagir

OnAfterGetRecord Trigger:

if (column1= '')and(column2='') then
Message('%1','C/AL is a Piece of Cake');

nalinee

What is required is not to display those records which have column1 AND column2 empty in a FORM.

I tried to put the filter on the form property with the settableview attribute BUT navision is not dispalying all records which has EITHER column1 OR colum2 as empty which not what I need. Only records which have both colum1 AND column 2 as empty should be filtered.


Is there a command which I can use to skip those records if column1 = '' AND column 2 = '' in the onaftergetrecord trigger?

muksha

Please use the following filter on your source view table:-

WHERE("First condition"=FILTER(<>0),second condition=FILTER(<>0))

gulamdastagir

Nalinee

your setting up wrong filters [-X

instead of using '<>' u shud go for '=',in the SourceTableView property of the form

WHERE(Column1=FILTER(=''),Column2=FILTER(=''))

bab

nalinee wrote:

What is required is not to display those records which have column1 AND column2 empty in a FORM.

You cannot do it with filters. You can do it with mark.
In the OnOpenForm trigger you can loop through the Rec, and MARK the records, which have to be displayed.

IF FINDFIRST THEN
  REPEAT
    MARK((Field1 <> '') AND (Field2 <> ''));
  UNTIL NEXT = 0;
MARKEDONLY(TRUE)

muksha

Nalinee, is your problem sorted out??

nalinee

Hi,

The solution is fine for a small database. The filter column 1 = empty AND column 2 = empty represent less than 1% of the records that need to be excluded in the display.

So marking the 99% of the records in a huge table is not practical and very time consuming.

please advise.

bab

Hello,
that's right... An other solution could be, to put a new field in to the table ("Fields Empty" - Boolean), and fill the field in the OnValidate triggers of your fields. Than you can create an index on this field, and filter on the form.

nalinee

Can anyone propose something on this issue plz???

My problem is still pending.

Thx

AS

have you tried:

setfilter(field1,'<>' + '''''');
setfilter(field2,'<>' + '''''');

in the OnOpenForm-Trigger?