Finance | Project Operations, Human Resources, ...

Adding an OR range in a query based on 2 different table fields

(1) Share

Report

Posted on by Jason0810M

Hello,

I have to add a certain range in a query object like the following:

((Table1.Field1 == 0 AND Table2.Field2 == valueX) OR (Table1.Field1 == 1 OR Table2.Field2 == valueY))

Does anyone know how this can be achieved?

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Layan Jwei 8,282 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Jason,

is this an AOT query or x++ query? and where are you using this query?

Thanks,
Layan Jweihan

Was this reply helpful? Yes No
Jason0810M 8 on at

Like (1)

Report
Copy link

Link copied!

Hello Layan,

It is an x++ query.

I have created a custom view to get the fields from the two tables in one entity.

Technically, I have now a view XXXItemView with fields IsCalculated, DateFrom and DateTo (and ItemdRecId to do the join).

I have added the custom view XXXItemView to my query by code.

I need to filter records as follows :

date calculatedDate = some calculated date as per a custom method;

((XXXItemView.IsCalculated == 0 && XXXItemView.DateFrom <= utcNow() && XXXItemView.DateTo >= utcNow()) || (XXXItemView.IsCalculated == 1 && XXXItemView.DateFrom <= calculatedDate && XXXItemView.DateTo >= calculatedDate)

Was this reply helpful? Yes No
Suggested answer

Layan Jwei 8,282 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!
Hi Jason,

How is the calculatedDate filled? can you show us the logic in the custom method? to be able to tell if we can do the range using a computed column or not.
And how did you add your view to a query by code can you show us please?

but if let's say we can't. and if I understood you correctly, then maybe try sth like this:

QueryBuildDataSource queryBuildDataSource = query.dataSourceTable((tablenum(XXXItemView))); queryBuildDataSource.name('XXXItemView'); queryBuildDataSource.addRange(fieldNum(XXXItemView, IsCalulated)).value ( strFmt('(((%1.%2 == %3)&&(%1.%4 <= %5)&&(%1.%6 >= %5))||((%1.%2 == %7) && (%1.%4 <= %8) && (%1.%6 >= %8)))', queryBuildDataSource.name(), fieldStr(XXXItemView, IsCalulated), 0, fieldStr(XXXItemView,DateFrom), utcNow(), fieldStr(XXXItemView,DateTo), 1, calculatedDate) );

Was this reply helpful? Yes No