Set filter in report using Text variable in NAV - Tips, Tricks & Facts #10

Hello everyone, It's been a month since I have written any post. Well, sometimes the work is just as much to occupy all your time that you barely get time to do other stuff.

The complete title of this post must be "Set filter in report using Text variable in which the user can use .. (aka range) and | (aka separator) in NAV" but never mind. 😉

I encountered this situation where I wanted to set filter on the report's dataitem using a text variable. So, I asked this question in some forums and got the answer which helped me resolve my problem and I want to share it with you guys too!


We know that filters can be added using ReqFilterFields in dataitem of a Report in NAV but sometimes the system design is as such that the records we want to lookup and select, do not show in the page which is there in LookupPageID of the table we want to Lookup into.

In my case,
DataItem is Fixed Asset.
LookupPageID on Fixed Asset table is Fixed Asset List.

I have a field "Equipment Asset" in fixed asset table and SourceTableView in Fixed Asset List is 'WHERE(Equipment Asset=CONST(No))'.

Therefore, if I want to filter on a record which is "Equipment Asset"=TRUE then these records will not show in the page when we lookup in the field which is available via ReqFilterFields in the report's request page.

Now, there is another list page named Equipment FA in the system and SourceTableView in this list page is 'WHERE(Equipment Asset=CONST(Yes))'.

So, to filter on the records where "Equipment Asset"=TRUE, I did the following:

1. In my report I took a variable FANoFilter and added in Request page. In the OnLookup Trigger of this variable, I have written the code as shown below,

CLEAR(EquipmentFAList);
FixedAsset.RESET;
FixedAsset.SETRANGE("Equipment Asset",TRUE);
EquipmentFAList.LOOKUPMODE(TRUE);
EquipmentFAList.SETTABLEVIEW(FixedAsset);
IF EquipmentFAList.RUNMODAL = ACTION::LookupOK THEN BEGIN
    EquipmentFAList.GETRECORD(FixedAsset);
    FANoFilter := FixedAsset."No.";
END;

2. I added the code shown below is on Fixed Asset - OnPreDataItem() in the report,

IF FANoFilter <> '' THEN
    "Fixed Asset".SETFILTER("No.",'%1',FANoFilter);

If I select FA0001 in FANoFilter and execute the report, the report works fine!

Now coming to the actual problem! >>>
If I apply filter like FA0001..FA0010 or FA0001|FA0002 in this text variable (FANoFilter), the report does not consider this as a range or two FA nos., but it takes these filters as one FA no. and the report doesn't execute.

3. So to achieve the desired solution I replaced my code in Fixed Asset - OnPreDataItem()

IF FANoFilter <> '' THEN
    "Fixed Asset".SETFILTER("No.",STRSUBSTNO('%1',FANoFilter));

Modify your SETFILTER code and use STRSUBSTNO as shown in the line above. This will do the job for you. Please comment if you do not get any part of this post. I will help you to clear your doubts.

Keep Learning guys!
Ishwar 👍