Composited filter OR and AND in NAV

(1) Share

Report

Posted on by Community Member

Hi friends,

I am facing with problem of how to do composited/complicated filter in NAV. For example

Rec.SETFILTER("Start Date",'((>=%1 & <=%2)|(%3>=%1 & %3<=%2)|(<=%1 & %3>=%2))',Date1,Date2,Date2);

It seems if the left part is not "Start date", it will pop error. Creating a new setfilter function will be "And" condition rather than "OR" condtion. How can I handle the above case? Thank you!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (1)

Report

You cannot use AND or OR operation as part of the filter. That will not work. You need to design it differently. What is your actual Requirement ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Suresh,

I want to filter record out by some condition, in SQL, it should be like

select * from table where ((rec."Start Date">=Date1 and "Start Date"<=Date2) or (rec."end date">=Date1 and rec."end date"<=Date2) or (Start Date<=Date1 and rec."end date">=Date2))

"Start date" and "End date" are the two record field going to filter

So I translate it as below but it seems formula invalid

rec.setFilter("Start Date",'((>=%1 & <=%2)|(%3>=%1 & %3<=%2)|(<=%1 & %3>=%2))',Date1,Date2,rec."End Date");

Do you have any solution for such filter? Thank you!

Was this reply helpful? Yes No
Verified answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

Unfortunately you cannot apply that filter in a single statement, you need to apply the filter and if that does not find the record then you reset the field filter and apply the other filter and so on...

Rec.SETFILTER("Start Date",'>=%1&<=%2',date1,Date20;

IF NOT Rec.FINDFIRST THEN

Rec.SETRANGE("Start Date");

next filter...

and so on...

Was this reply helpful? Yes No
subrata.bauri 1,144 on at

Like (0)

Report

I think "IF ELSE" Statement will be ideal in this case.

Was this reply helpful? Yes No
Suggested answer

Nareshwar Raju Vane... 5,596 on at

Like (0)

Report

Hi,

Are you looping after your SETFILTER or just a record check? if it is the latter part, you can use ISEMPTY, which is much handy.

Was this reply helpful? Yes No