Small and medium business | Business Central, N...

filter on multiple fields in a statement

(0) Share

Report

Posted on by GER_Nico

117

Hello,

can someone explain me how to combine filters across multiple fields?

How do I filter for the following records in a query?
record 1: field A: value C, field B: value D
record 1: field A: value E, field B: value F
...

When I use SetFilter("Field A"; 'C | E') and SetFilter("Field B"; 'D | F'). I also get the record where field A has value E and field B has value D.

Thank you and best regards
Nico

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

What your are using is an "or" filter so to me it looks like the system is behaving exactly as designed.

What is the result you want to achieve with your filtering?

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,739 Super User 2025 Season 2 on at

Like (0)

Report

Hi, hope the following helps.

Cross-column search (OR filters on multiple fields) ★FilterGroup(-1)

https://yzhums.com/28943/

Thanks.

ZHU

Was this reply helpful? Yes No
GER_Nico 117 on at

Like (0)

Report

Sorry, I didn't explain my problem properly.

I have a table whose primary key consists of two fields. This means that I can only uniquely select a record with a combination of the two fields. But I want to select multiple records at the same time, which means I have to specify multiple of these combinations as well.

In my case it is about the "Job Task" table, which primary key is clustered by the fields "Job No." and "Job Task No.". I would now like to select job task 1000 from job 12345 and job task 1200 from job ABCD. But I don't know how to specify the filter in order to only get these two job tasks and not also the job task 1200 from job 12345 and the job task 1000 from job ABCD.

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,708 Moderator on at

Like (0)

Report

Hi,

You can try two method below

Try with

Record.Get("Primary Key first field","Primary Key second field");

or

Record.reset;

Record.setrange("Primary Key first field",<Value>)

Record.setrange("Primary Key SEcond field",<Value>)

Record.findfirst;

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-get-find-and-next-methods

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/fieldref/fieldref-setrange-method

Thanks.

Was this reply helpful? Yes No
GER_Nico 117 on at

Like (0)

Report

Using the two methods, I retrieve each record individually. Unfortunately that doesn't solve my problem.

I am working in a report with a dataitem. I can't pass many individual records there, just a filtered list.

Was this reply helpful? Yes No