Add multiple values to query range

(1) Share

Report

Posted on by SKO

470

Hello

I have a query range on a RecId field and i need to supply multiple values to it from a table. I tried to put the values in a container and convert it into one single string and use it but no luck. Any ideas would be appreciated.

Thanks

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (11)

Answers (1)

Suggested answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
I can't comment on your current problem, because you described neither your implementation nor the problem. But I can give you some information in general.

If "from a table" means that you have the values in another table (it may even be an TempDB one), you can join the two tables.

If you have to use ranges, you can add several ranges for the same value:

FieldId fieldId = fieldNum(MyTable, MyField); ds.addRange(fieldId).value(queryValue(value1)); ds.addRange(fieldId).value(queryValue(value2));

The last options is using a single range with comma-separated range values. But I prefer multiple ranges.

Was this reply helpful? Yes No
Suggested answer

Saif Ali Sabri 2,354 Moderator on at

Like (1)

Report
Copy link

Link copied!
In Dynamics 365 Finance and Operations, when working with query ranges and needing to supply multiple values (e.g., for a RecId field), the query framework requires specific handling since direct assignment of multiple values isn't straightforward. Below are several approaches to achieve this:

Option 1: Use the strFmt Function with an "OR" Statement

The most common way to supply multiple values in a query range is to use an OR condition by formatting the values into a single string. Here's how you can do it:

Prepare the Query Range:
Use the strFmt function to build a single query range string with an "OR" condition for each value.

QueryBuildRange queryRange; RecId recId1 = 5637144576; // Example RecId RecId recId2 = 5637144577; // Another RecId RecId recId3 = 5637144578; // Additional RecId // Assuming you are working with a QueryBuildDataSource object queryRange = queryBuildDataSource.addRange(fieldNum(MyTable, RecId)); // Set the range with OR conditions queryRange.value(strFmt('(%1,%2,%3)', recId1, recId2, recId3));

This approach builds a query like:
(RecId = 5637144576 OR RecId = 5637144577 OR RecId = 5637144578)

When Working With More Values:

If you need to build a query range dynamically for many values (e.g., from another table), use a loop to construct the "OR" condition.

Example:

QueryBuildRange queryRange; RecId recId; MyTable myTableBuffer; List values = new List(Types::Integer); str rangeString = ''; boolean firstValue = true; while select RecId from myTableBuffer where myTableBuffer.SomeCondition { recId = myTableBuffer.RecId; if (!firstValue) { rangeString += ','; } rangeString += int2str(recId); firstValue = false; } queryRange = queryBuildDataSource.addRange(fieldNum(MyTable, RecId)); queryRange.value(strFmt('(%1)', rangeString));

Option 2: Use the Query::ValueIn Helper Method

When dealing with multiple values for a single field, you can use the Query::ValueIn method to build an "IN" clause for the query range.

Example Using ValueIn:

QueryBuildRange queryRange; RecId recIdArray[3] = [5637144576, 5637144577, 5637144578]; queryRange = queryBuildDataSource.addRange(fieldNum(MyTable, RecId)); queryRange.value(Query::ValueIn(recIdArray));

The Query::ValueIn function formats the range into an "IN" clause that looks like:
(RecId IN (5637144576, 5637144577, 5637144578)).

When Values Come from Another Table:
If you want to retrieve values dynamically from another table, build an array or container first and then pass it to Query::ValueIn.

QueryBuildRange queryRange; MyTable myTableBuffer; List recIdList = new List(Types::Int64); RecId recId; while select RecId from myTableBuffer where myTableBuffer.SomeCondition { recIdList.addEnd(myTableBuffer.RecId); } queryRange = queryBuildDataSource.addRange(fieldNum(MyTable, RecId)); queryRange.value(Query::ValueIn(recIdList.toContainer()));

Option 3: Use ExistsJoin or a Related Query Data Source

If your goal is to filter records based on RecIds that come from another table, using an ExistsJoin can be a cleaner solution instead of manually building a range.

Example with ExistsJoin:

Query query = new Query(); QueryBuildDataSource qbdsMain = query.addDataSource(tableNum(MyTable)); QueryBuildDataSource qbdsRelated = qbdsMain.addDataSource(tableNum(RelatedTable)); qbdsRelated.joinMode(JoinMode::ExistsJoin); qbdsRelated.addRange(fieldNum(RelatedTable, RecId)).value('!0'); // Filter non-zero RecIds QueryRun queryRun = new QueryRun(query); while (queryRun.next()) { MyTable myTableBuffer = queryRun.get(tableNum(MyTable)); info(myTableBuffer.RecId); }

The ExistsJoin ensures that records in MyTable are filtered by the existence of related records in RelatedTable based on the relationship or a condition.

Option 4: Use a Temporary Table

If the list of RecId values is large, or the source data comes from multiple tables, a temporary table can simplify the process.

Insert Values into a Temporary Table:

TmpMyTable tmpTable; MyTable myTableBuffer; while select RecId from myTableBuffer where myTableBuffer.SomeCondition { tmpTable.clear(); tmpTable.RecId = myTableBuffer.RecId; tmpTable.insert(); }

Join with the Temporary Table in Your Query:

Query query = new Query(); QueryBuildDataSource qbdsMain = query.addDataSource(tableNum(MyTable)); QueryBuildDataSource qbdsTmp = qbdsMain.addDataSource(tableNum(TmpMyTable)); qbdsTmp.joinMode(JoinMode::InnerJoin); qbdsTmp.relations(true); QueryRun queryRun = new QueryRun(query); while (queryRun.next()) { MyTable myTableBuffer = queryRun.get(tableNum(MyTable)); info(myTableBuffer.RecId); }

The temporary table allows you to easily handle and join large datasets without manually constructing ranges.

Recommendations:

Use Query::ValueIn (Option 2) for simple cases where you have a manageable number of values.

Use ExistsJoin (Option 3) if filtering depends on related data from another table.

For large datasets or complex filtering, consider temporary tables (Option 4).

Was this reply helpful? Yes No
Anton Venter 20,656 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

In addition to the good answers already provided, I highly suggest to use a tempory table (type TempDB) and then join your query/select statement on RecID. Use insert_record to populate the temp table if possible. The TempDB type of temp table actually exists in the database. That means SQL Server can make joins with your query on SQL server level. With this design pattern, your solution can scale up to many "filter" records (RecIDs) if necessasary.

Was this reply helpful? Yes No
Layan Jwei 8,165 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Can you show us your current code

Was this reply helpful? Yes No
SKO 470 on at

Like (0)

Report
Copy link

Link copied!

My goal here is to join these 3 tables and provide the ECOResValue table field values as lookup.

I only have one range with multiple values and the range values are all RecId's.

I have this code, but it is not filtering out by these 3 values but showing everything.

Was this reply helpful? Yes No
Martin Dráb 239,029 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Look at the code I gave you to see how to add a range for each value. I'd change your code accordingly if you didn't give us a mere picture; now it's up to you.

Regarding your current code, I think the bug is in calling SysQuery::value(); it'll escape the commas. You should have looked at SQL the query you generated by your X++ and compare it with what you wanted to produce.

1 people found this reply helpful.

Was this reply helpful? Yes No
SKO 470 on at

Like (0)

Report
Copy link

Link copied!
My code gives the output likes this upon debugging. The commas are still there but it is somehow adding up the backslash.

Name Value Type

qbr Value "68719549719\\,\\ 68719549720\\,\\ 68719549721" string

The requirement is this. I am filtering the table EcoResAttributeType for a specific value on field Name.

Then I am using this EcoResAttributeType record as a filter criteria for the join between tables table EcoResEnumerationAttributeTypeValue and EcoResValue.

The problem is that here the 3rd table EcoResValue has to be filtered for different RecId values that are returned from the table EcoResEnumerationAttributeTypeValue which brings the scenario of dynamically assigning multiple values for single range.

I can't use the syntax you showed because here the range values are generated dynamically and I would not know how many times to repeat the range value assignment

ds.addRange(fieldId).value(queryValue(value1));

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Yes, "it is somehow adding up the backslash" is what I meant when saying "the bug is in calling SysQuery::value(); it'll escape the commas". You confirmed that identified your bug correctly and you've learned how to find it by yourself in future, which is great.

The solution I recommend - adding a range for each value - has several benefits; one of them is that you can't write such a bug there.

Saying that you can't use multiple ranges because you don't know the number of values in advance makes no sense. You already have code for iterating any number of values (by using an enumerator), we're talking about what you'll do inside. The solution will be not just better, but even simpler than your current code. Take a look:

while (enumerator.moveNext()) { qbds.addRange(fieldNum(EcoResTextValue, RecId)) .value(queryValue(enumerator.current())); }

Was this reply helpful? Yes No
SKO 470 on at

Like (0)

Report
Copy link

Link copied!

Thanks. I already tried this code but the problem here is that it only takes the last value which replaces all the previous values and does not add up all the values with commas.

Thanks

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You're right that this solution "does not add up all the values with commas", because it doesn't need anything like that. But you're wrong saying that it takes the last value only.

I'm saying the whole time that there are two approaches. Either you add a single range with comma-separated values, or you add a range for every value. My code below is the latter approach; it calls addRange() for every value. If you have ten values, you'll get ten ranges (each with a single value) and F&O will apply OR operator automatically when construction SQL query from this Query object.

Please try my code and see if for yourself.

Was this reply helpful? Yes No