Dynamic Filtering and Filter Creation during for multiple record selections in Page Lookup in NAVISION

 

Introduction:

I have been working in NAV/BC for a couple of years to master the craft and to some extent all of us are successful.

But a single scenario/ user error/misunderstanding can create a big question mark.

This blog is mainly divided into two parts

1. Why does NAV/BC support few small things which are not supported when analyzing the data?

2. Workaround and its implications.

So the use case is the NAV/BC Administrator creates an Item with the number in the 'ABC-1039-(A)'. He and other users do all the manufacturing, trading activities.

When he tries to run reports having Item No. Lookup using Table Relation, he is given the following error.

Pre-requisites:

Microsoft Dynamics NAV / BC

References:

https://community.dynamics.com/nav/b/moxie4nav/posts/convert-setselectionfilter-to-setfilter

Demonstration:

1. Checking if this scenario is supported by spinning up some test code. Turns out when I hard-code Item No in SETFILTER, the report is working precisely as expected.

1	"Item".SETFITLER("No.,'TLR0001206A(P)');

2. Checking if this issue can be resolved if I get Item No. through a code-based lookup. Also, this could be done using running the page in Runmodal with  LookupMode. Refer code below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Item No. - OnLookup(VAR Text : Text) : Boolean CLEAR(Rec_Item); CLEAR(Page_ItemList); IF Rec_Item.FINDSET THEN BEGIN Page_ItemList.SETTABLEVIEW(Rec_Item); Page_ItemList.LOOKUPMODE(TRUE); Page_ItemList.SETRECORD(Rec_Item); IF Page_ItemList.RUNMODAL = ACTION::LookupOK THEN BEGIN Page_ItemList.SetSelection(Rec_Item); IF Rec_Item.FINDSET THEN Text := Rec_Item."No."; END; END; EXIT(TRUE);

3. Here comes the fun bit, using LookupMode, can I  select multiple records and create a dynamic filter.

I know the append primary key with ' |  ' in the format stated by Franz (https://community.dynamics.com/nav/b/moxie4nav/posts/convert-setselectionfilter-to-setfilter) as I was following this for quite some time. Just that this is not an optimized version to get the filter.

For example, if I select ID1, ID3, and ID10-ID50 and if I get with the method of appending with | and creating filter will be out of scope as we are limited with field length in NAV. Instead, the filter should be  ID1| ID3 | ID10..ID50

Turns out there is a Codeunit 46 FilterManagement which allows you to pass record selection as var parameter and spits out the dynamic filter as per the example.

This FilterManagement has only filter selection for important masters such as Customers, Vendors, Items, etc. If you want for any tables other than you might have to create an appropriate method. Refer source code below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Item No. - OnLookup(VAR Text : Text) : Boolean CLEAR(Rec_Item); CLEAR(Page_ItemList); IF Rec_Item.FINDSET THEN BEGIN Page_ItemList.SETTABLEVIEW(Rec_Item); Page_ItemList.LOOKUPMODE(TRUE); Page_ItemList.SETRECORD(Rec_Item); IF Page_ItemList.RUNMODAL = ACTION::LookupOK THEN BEGIN Page_ItemList.SetSelection(Rec_Item); IF Rec_Item.FINDSET THEN Text := CU_SelectionFilterMgmt.GetSelectionFilterForItem(Rec_Item); END; END; EXIT(TRUE);

Now, that we have the filter in a variable, we can simply apply SETFILTER ( "No.", Itemfiltervar) and it works like a charm.

Multi Selecting of the Items

Dynamics filter created by Filter Management Codeunit

Spoiler alert: Customer ended up discarding this and took the HIGHWAY of changing all item numbers and transactions 😆

Conclusion:

This blog is merely one step progress over the creation of advanced filters. Although this blog helps optimize filter creation in NAVISION / Business Central.
The thing that surprised me the most was the creation of Item with '(' in Item No throws no error but using it in Filter throws an error 😂 and what could be a possible workaround for the same.

I hope this helps! Cheers.