Skip to main content

Notifications

Announcements

No record found.

Business Central forum
Answered

Two Properties filter condition by and condition

Posted on by 44

I need to check two field filters condition by and condition in the Query Page.


Example

Sno Product  Cost  Qty
1  A 100 5
2 B 200 6
3 C 0 0
4 D 900 10

Filter by = (Cost != 0 and Qty != 0)

We want the result is

Sno Product  Cost  Qty
1  A 100 5
2 B 200 6
3 C 0 0
4 D 900 10

Remove to Sno - 3 item data

Categories:
  • Verified answer
    Govinda Kumar Profile Picture
    Govinda Kumar 2,197 Super User on at
    RE: Two Properties filter condition by and condition

    Hi Thiran,

    to do so you have to write it like this in the query filter

    var
        MyQuery: Query "You Query Name";
    begin
        MyQuery.SetFilter(Quantity,<>,0);
        MyQuery.SetFilter(Discount,<>,0); 
        MyQuery.Open;  
        while MyQuery.Read do  
        begin
        // populate the temp table
        end;   
        Myquery.Close; 
    end;

    And in Query Designer you can do it like this
    query 50100 "Your Query Name"
    {
        QueryType = Normal;
    
        elements
        {
            dataitem(C; Customer)
            {
                column(Customer_Number; "No.")
                {
                }
    
                dataitem(SL; "Sales Line")
                {
                    DataItemLink = "Sell-to Customer No." = c."No.";
                    SqlJoinType = InnerJoin;
                    DataItemTableFilter = Quantity = filter(<> 0), Discount = filter(<> 0);
    
                    column(Qty; Quantity)
                    {
                    }
                }
            }
        }
    }

    Hope it helps
    Regards

  • Thiran Profile Picture
    Thiran 44 on at
    RE: Two Properties filter condition by and condition

    I need to check two fields.. with and condition 

    example:

    if (Quantity not qual 0 and discount not qual 0) then show the data

  • Suggested answer
    Govinda Kumar Profile Picture
    Govinda Kumar 2,197 Super User on at
    RE: Two Properties filter condition by and condition

    Hi Thiran,

    You can either filter the query like this

    var
        MyQuery: Query "You Query Name";
    begin
        MyQuery.SetFilter(Quantity,<>,0);  
        MyQuery.Open;  
        while MyQuery.Read do  
        begin
        // populate the temp table
        end;   
        Myquery.Close; 
    end;

    or you can also add filters dynamically by using in Query Designer, as shown in the example on this Microsoft Learn page:

    query 50100 "Your Query Name"
    {
        QueryType = Normal;
    
        elements
        {
            dataitem(C; Customer)
            {
                column(Customer_Number; "No.")
                {
                }
    
                dataitem(SL; "Sales Line")
                {
                    DataItemLink = "Sell-to Customer No." = c."No.";
                    SqlJoinType = InnerJoin;
                    DataItemTableFilter = Quantity = filter(<> 0);
    
                    column(Qty; Quantity)
                    {
                    }
                }
            }
        }
    }

    learn.microsoft.com/.../devenv-query-filters

    I hope it helps

    Regards

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans