How to use "IN" operator in QueryBuildRange

Hello everyone,

I have filtered out some employee IDs and stored them in an array. Now, I need to use QueryBuildRange to filter the records in PurchReqTable based on this employee array.

Just like this:

SELECT * FROM PurchReqTable WHERE PurchReqTable.Originator IN employeeArray

But I don’t know how to use “IN” operator in QueryBuildRange.

I once tried using the select statement to test:

while select purchReqTable where purchReqTable.Originator in employeeArray
{
    Info(purchReqTable.PurchReqId);
}

But I received an error: Types ‘int64(PurchReqPreparerRefRecId)’ and ‘Array’ are not compatible with operator ‘in’.

So, can I use the ‘IN’ operator in QueryBuildRange? If possible, how should I use it.

Any suggestions would be appreciated.

X++ supports the in operator in a single scenario: a container of element values (you might want to read my blog post about it). Your scenario is different and therefore it’s not supported.

But it doesn’t mean you can’t create a query with a filter for values in your array. Use Query* classes to create the query at runtime, iterate your array and add a range for each value. For example:

Query query = new Query();
QueryBuildDataSource reqDs = query.addDataSource(tableNum(PurchReqTable));

for (int i = 1; i <= employeeArray.lastIndex(); i++)
{
    reqDs.addRange(fieldNum(PurchReqTable, Originator)).value(queryValue(employeeArray.value(i)));
}

Alternatively, you could use SysDa framework instead of Query* classes.

1 Like

@MartinDrab

Your suggestion is very useful. Thanks a lot.