Finance | Project Operations, Human Resources, ...

how can I create custom query in x++ and store results in a view?

(0) Share

Report

Posted on by Community Member

Hello Everyone,

I am new to dynamics 365 and need your help. I want to retrieve specific records from one of the Dynamics 365 Fin-Ops tables. I want to use some conditions on the data hence; I'm trying it using x++ query instead of using drag-and-drop UI.

Here's the scenario. I have order table where all the orders are being placed and I want to only retrieve orders which are recent under specific Id and componentId.

The SQL query is

"SELECT *

FROM OrderTable O

LEFT JOIN OrderTable O2 ON O.Id = O2.Id AND O.ComponentId = O2.ComponentId

AND O.OrderDateTime <= O2.OrderDateTime

WHERE O2.LineId IS NULL"

I have tried GUI in visual studio by creating Query using Add Item > Query but not sure where I can above conditions. I know we can do it in x++ bu don't know how! Also want to store all these records in a view. Can anyone please guide me?

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Naga Kiran on at

Like (0)

Report

Hi Harshil,

To my understanding, you are trying to build a query based on join to same table. I am bit not sure of the business scenario for framing the query this way and sure there can be a better alternative for this scenario.

How ever you can try the below example and please note that I have not tested this-

Query query = new Query();

QueryBuildDataSource queryOrderTable1;

QueryBuildDataSource queryOrderTable2;

QueryBuildRange queryBuildRange;

QueryRun queryRun;

OrderTable locOrdTable;

queryOrderTable1 = query.addDataSource(tablenum(OrderTable));

queryOrderTable2 = queryOrderTable1.addDataSource(tablenum(OrderTable));

queryOrderTable2.addLink(fieldNum(OrderTable , Id), fieldNum(OrderTable , Id));

queryOrderTable2.addLink(fieldNum(OrderTable , ComponentId ), fieldNum(OrderTable , ComponentId ));

queryOrderTable2.joinmode(JoinMode::LeftJoin)

queryOrderTable2.fetchMode(QueryFetchMode::One2One);

queryOrderTable2.addRange(fieldnum(OrderTable,LineId )).value('0');

queryRun = new QueryRun(query); //queryrun will have the resultset

while(queryRun.next())
{

//play with the result set

locOrdTable = queryRun.get(tablenum(OrderTable));

}

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

Like (0)

Report

You can't do exactly the same in F&O. For instance, F&O doesn't support NULL values. Therefore you must first think about a query that makes sense in the context of F&O.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Harshil,

You can create 2 views:

1st view: Aggregated view with Max(OrderDateTime) with grouping by Id and ComponentId

2nd view: select form OrderTable with existing join with view1 with relation by Id, ComponentId, and OrderDateTime

Was this reply helpful? Yes No