AL SETRAGE OR SETFILTER for filtering on SET of values

(0) Share

Report

Posted on by Samantha73

3,247

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.)

I have the same question (0)

All responses (6)

Answers (2)

Suggested answer

Inge M. Bruvik 1,105 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Robert Jolliffe 885 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Akshay_Panchal 402 on at

Like (2)

Report

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.

Was this reply helpful? Yes No
Samantha73 3,247 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Robert Jolliffe 885 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Neoster 10 on at

Like (0)

Report

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!

Was this reply helpful? Yes No