On adding via x++ a new DS to dynamic query (with more than 1 DS's), the query becomes empty

(4) Share

Report

Posted on by NNikolov

Hello,

I have a dynamic query (selections entered by the user), which is stored via query.pack() in a table field. At some later point in time - this table record needs to be processed and I need to add some extra conditions to the query – e.g. adding an non-existing join clause to another (not initially available in the query) table. This seems to work if the initial query is based only on ONE table, but if the initial query is a join of multiple tables, adding a new data source to the query “clears” the initial query. For exmample, if the initial oldQuery (with CustInvoiceJour as the first data source) - consists only of the CustInvoiceJour data source - the adding of the new data source works. But if the initial oldQuery has e.g. also SalesTable joined to the CustInvoiceJour - the adding of a new data source seems to "break":

newQuery = new Query (oldQuery.pack()); // cloning of the old query
qbdsCIJ = newQuery.dataSourceTable(tableNum(CustInvoiceJour)); // data source found
qbdsNSN = qbdsCIJ.addDataSource(tableNum(NSNProcessed)); // here the newQuery gets "empty" - with only the new NSNProcessed table as a datasource
qbdsNSN.relations(true);
qbdsNSN.joinMode(JoinMode::NoExistsJoin);
qbdsNSN.addRange(fieldNum(NSNProcessed, WorkRequest)).value(queryValue(WorkReqNo));

any ideas?

Thank you!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (14)

Answers (1)

Vahid Ghafarpour 12,228 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

If the query has multiple joined data sources, calling addDataSource() at the root level can unintentionally replace the root data source, because internally it adds the new data source to the root query and breaks the previous join hierarchy

https://community.dynamics.com/blogs/post/?postid=c3be6572-df11-43d7-8015-81dd644d3d5e

Was this reply helpful? Yes No
Suggested answer

Anton Venter 20,656 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@NNikolov

From your post I understand that the system clears the query only when users dynamically adds a joined datasource to the query. What about changing your code to enumerate the datasources of the packed query (old) and creating a new query in code based on the old query? Along with the field query values of course. Or perhaps you can stop simply them from adding datasources to the query.

Was this reply helpful? Yes No
NNikolov 37 on at

Like (1)

Report
Copy link

Link copied!

Hi Vahid and Anton,

Thank you for the quick answer!

The system clears at the moment of adding a new datasource when the initial query has some joined tables in it (independent of who added them - the user / or the developer).

In other words - if the query Q is based only on 1 table: A (with some ranges on the A table specified by the user), than adding via the above x++ code - a notexisting-join N to A, the construction works and the query looks OK. But if the query is already a join of 2 or more tables - e.g.: (root A -> child B -> child C))) (e.g. CustInvoiceJour with child SalesTable with child CustTable) - then trying to add a join of table N to the root A (a non-existing in this case, but the type seems not to matter) fails - and this is happening at the moment of adding new data source with addDataSource(N) - the initial query clears.

So - it is not clear to me how can someone add 2 data sources - both joined to the root table - if the adding of the second one clears the query.

What I would like to achieve is the following:

The initial query is something like:

select CustInvoiceJour where ... some ranges specified by the user

join SalesTable where SalesTable.SalesID == CustinvoiceJour.SalesID

&& ... some other ranges specified by the user

join CustTable where CustTable.AccountNum == SalesTable.InvoiceAccount

&& ... some other ranges specified by the user

This query is stored with .pack() into a table field. Later, I need to take this query and add to it the following condition to the root table:

notexists join NSNProcessedTable where NSNProcessedTable.CustInvoiceJour == CustInvoiceJour.RecID

&& NSNProcessedTable.WorkID == SomeWorkId.

If the original query consisted only of the root table (CustInvoiceJour) - then the adding of the notexists-join works fine, but if it consisted of a join of some tables - than at the moment of adding the new data source for NSNProcessedTable - it clears :(

Thank you and kind regards,

Nikolay

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

Like (0)

Report
Copy link

Link copied!

How do you check the resulting query?

I'm assuming that you can reproduce the problem by creating the same query that you get from new Query (oldQuery.pack()), right? If so, then you can give a piece of code that anyone can run and check the result, without having to create a table to hold the packed query etc.

Was this reply helpful? Yes No
NNikolov 37 on at

Like (1)

Report
Copy link

Link copied!

Hi Martin,

Indeed - the initial base query is created by code and passed to a user dialog for further specifications:

public void initParmDefault()
{
Query query = new Query();
QueryBuildDataSource qbds = SysQuery::findOrCreateDataSource(query, tableNum(SalesTable));
QueryBuildRange qrSalesStatus = qbds.addRange(fieldNum(SalesTable, SalesStatus));
qrSalesStatus.status(RangeStatus::Locked);
qrSalesStatus.value(queryValue(SalesStatus::Backorder));
QueryBuildRange qrWGKConversationId = qbds.addRange(fieldNum(SalesTable, WGKConversationId));
qrWGKConversationId.status(RangeStatus::Locked);
qrWGKConversationId.value(conversationId);
QueryBuildDataSource qbdsCustTable = SysQuery::findOrCreateDataSource(query, tableNum(CustTable), tableNum(SalesTable));
qbdsCustTable.relations(true);
qbdsCustTable.addRange(fieldNum(CustTable, CustGroup));
queryrun = new SysQueryRun(query);
}

so - the structure of the query is:

SalesTable

\_ CustTable

Based on (the above dynamic) query run- sales invoices are posted and the respective resulted CustInvoiceJour records later would have to be processed in some ways. So I built a new query based on the original one but with the structure (new root)

CustInvoiceJour

\_ SalesTable

\_ CustTable

and store it somewhere with the intention later to reuse it multiple times (some segmentation), but it should somehow have the following structure:

CustInvoiceJour

\_ SalesTable

\_ CustTable

\_ ProcessedWorkrequest (NotExistingJoin) --> adding of this (not-exists-join) clears the complete query structure.

As this was a rather urgent matter - I resolved it for the time being by transforming the query in a rather "linear-structure":

CustTable

\_ SalesTable

\_ CustInvoiceJour

\_ (NEJ) ProcessedWorkrequest

which seems to work, but somehow I am unable to add a join on a higher level in an already existing structure of joins in a query object (something which is obviously quite easily done with direct statement).

Thank you for your input on this!

Kind regards,

Nikolay

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

Like (0)

Report
Copy link

Link copied!

I'm still waiting for the answer about how you check the query. My guess is that you have a problem more with the visualization of the query than the query itself and you could fix that by setting the fetch mode to 1:1, but I really can't do more then guessing until I get more information from you.

Was this reply helpful? Yes No
NNikolov 37 on at

Like (1)

Report
Copy link

Link copied!

Hi Martin,

I just look at the query in the debugger and did also some info(query.toString()) at different stages of the query build. Initially I figured something was wrong when I saw the count of number of records of the result set.

Is this what you are asking?

Kind regards,

Nikolay

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

Like (0)

Report
Copy link

Link copied!

Repeating my feedback:

What about changing your code to enumerate the datasources of the packed query (old) and creating a new query in code based on the old query? Along with the field query values of course. Or perhaps you can stop simply them from adding datasources to the query.

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

Like (0)

Report
Copy link

Link copied!
Okay, that's roughly what I thought. Now let's create code that we can use for testing. Do you mean something like this:

Query query = new Query(); QueryBuildDataSource salesTableQbds = query.addDataSource(tableNum(SalesTable)); QueryBuildDataSource custTableQbds = salesTableQbds.addDataSource(tableNum(CustTable)); qbdsCustTable.relations(true); QueryBuildDataSource salesLineQbds = salesTableQbds.addDataSource(tableNum(SalesLine)); qbdsCustTable.relations(true); info(query.toString());

Was this reply helpful? Yes No
NNikolov 37 on at

Like (0)

Report
Copy link

Link copied!
Yes - I tried this:

public static void TestQuery()
{
Query query = new Query();
QueryBuildDataSource qdbsSalesTable = query.addDataSource(tableNum(SalesTable));

QueryBuildDataSource qbdsCustTable = qdbsSalesTable.addDataSource(tableNum(CustTable));
qbdsCustTable.relations(true);

info(strFmt('Step 1.: %1', query.toString()));

QueryBuildDataSource qdbsSalesLine = qdbsSalesTable.addDataSource(tableNum(SalesLine));
qdbsSalesLine.relations(true);

info(strFmt('Step 2.: %1', query.toString()));
}

and the resulting info log is:

Step 2.: Query object 3ecb550

Step 1.: Query object 3ecb550: SELECT * FROM SalesTable(SalesTable_1) JOIN * FROM CustTable(CustTable_1) ON SalesTable.InvoiceAccount = CustTable.AccountNum

Was this reply helpful? Yes No