Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / AL SETRAGE OR SETFILTE...
Small and medium business | Business Central, N...
Answered

AL SETRAGE OR SETFILTER for filtering on SET of values

(0) ShareShare
ReportReport
Posted on by Samantha73 2,663

Hi Guys

I couldn't find a way to pass a SET of values to a filter. Setrange as the name says only takes a range. See below example:

                        ItemRec.Reset();
                    ItemRec.SetRange(MyValue, true);
                    if ItemRec.FindSet() then
                        Message('item found %1', ItemRec.Count());//found 2
                   
                    PriceLine.Reset();
                    PriceLine.SetRange("Source Group", PriceLine."Source Group"::Vendor);
                    PriceLine.SetRange(Status, PriceLine.Status::Active);
                    PriceLine.SetRange("Source No.", Rec."Buy-from Vendor No.");
                    PriceLine.SetFilter("Asset No.", '=%1', ItemRec."No.");
                    if PriceLine.FindSet() then
                        Message('priceline found %1', PriceLine.Count());//found 1
Here I want to find a set of items and then filter PriceLine records if the item exist. (In new Pricing feature Asset No = Item No.)
  • Neoster Profile Picture
    Neoster 10 on at
    RE: AL SETRAGE OR SETFILTER for filtering on SET of values

    Just wanted to let you know I had exactly the same challenge to solve and this answer solved it.

    I knew this was a possible solution but I actually thought there would have been a more build-in solution.

    Anyway, thanks!

  • Robert Jolliffe Profile Picture
    Robert Jolliffe 859 on at
    RE: AL SETRAGE OR SETFILTER for filtering on SET of values

    NAV / BC was originally written before they were using SQL Databases and this is part of the legacy of that.  You're not actually directly accessing SQL Data - it's abstracted into the RECORD type which is not what you or I would be familiar with as a RECORDSET or DATASET type object.

    Occasionally frustrating but given you can write 20 lines of code and have a functioning page and table that persist your data and you never add your own Commit or Rollbacks I'm ok with it.

  • Samantha73 Profile Picture
    Samantha73 2,663 on at
    RE: AL SETRAGE OR SETFILTER for filtering on SET of values

    Thanks everyone..got it working with 123|345|453 pattern..

    GETFILTER does not work as this gives you the filter itself rather than the resulting record set. In SQL its easy to apply this kind of set operation but I'm surprised we do not have a predefined method to handle this..something like

    Item.Setrange(Type, Inventory);

    ItemFilter := GETRECORDSET

  • Verified answer
    Akshay_Panchal Profile Picture
    Akshay_Panchal 402 on at
    RE: AL SETRAGE OR SETFILTER for filtering on SET of values

    Hii,

    You can concatenate the items nos with separate '|' in the text variable and pass that variable as a filter in pricelist.

    e.g.

    If Item.findset then

    Repeat

      Textvar += Item."No."+'|';

    Until item.next =0;

    Thanks

    Verify, If this answer helpful to you.

  • Verified answer
    Robert Jolliffe Profile Picture
    Robert Jolliffe 859 on at
    RE: AL SETRAGE OR SETFILTER for filtering on SET of values

    If I understand what you want to do, you want to filter on a group of items.

    to do that for Item No.s 1234 and 2345 then would do a "No.", '=1234|2345' and that should work

    If you do it in the client it works. Not 100% sure of the syntax but I think the Set Filter with this structure will work

    You would just build a | separated list somewhere in a variable and pass it to the SetFilter.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 999 Moderator on at
    RE: AL SETRAGE OR SETFILTER for filtering on SET of values

    ItemRec.No will only hold one item no and not a filter expression.

    So what you can do is:

    myfilter string := Itemrec.GETFILTER("No.") ;

    This will give you the filter that is currently set on your item record.

    Then use that in the line you marked yellow.

    PriceLine.Setfilter("Asset No.",myfilter);

    Then the filter on the "No." filed on the item table will match the filter on the "Asset No." in your price line table.

    Hope this helps you.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,884 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,758 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans