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

nalinee
nalinee Member Posts: 55
edited 2007-03-29 in NAV/Navision Classic Client
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

Comments

  • lubost
    lubost Member Posts: 633
    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
    nalinee Member Posts: 55
    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
    gulamdastagir Member Posts: 411
    did you consider setting the required filter using SETFILTER in the Form - OnAfterGetRecord() trigger?
    Regards,

    GD
  • Mbad
    Mbad Member Posts: 344
    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
    gulamdastagir Member Posts: 411
    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
    Regards,

    GD
  • muksha
    muksha Member Posts: 274
    Please see if table filters on the form with value not equal to null works.
    Mukesh Sharma
  • nalinee
    nalinee Member Posts: 55
    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
    gulamdastagir Member Posts: 411
    OnAfterGetRecord Trigger:

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

    GD
  • nalinee
    nalinee Member Posts: 55
    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
    muksha Member Posts: 274
    Please use the following filter on your source view table:-

    WHERE("First condition"=FILTER(<>0),second condition=FILTER(<>0))
    Mukesh Sharma
  • gulamdastagir
    gulamdastagir Member Posts: 411
    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(=''))
    Regards,

    GD
  • bab
    bab Member Posts: 65
    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
    muksha Member Posts: 274
    Nalinee, is your problem sorted out??
    Mukesh Sharma
  • nalinee
    nalinee Member Posts: 55
    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
    bab Member Posts: 65
    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
    nalinee Member Posts: 55
    Can anyone propose something on this issue plz???

    My problem is still pending.

    Thx
  • AS
    AS Member Posts: 3
    have you tried:

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

    in the OnOpenForm-Trigger?

Categories