Microsoft Dynamics AX 2012 query

(1) Share

Report

Posted on by Community Member

Dear Sir,

I ask about Microsoft Dynamics AX 2012 query, my task is to join two tables (Salesline and Inventsum) with sum (SalesQty and PostedQty). group by Salesline.Itemid

i try a dynamics query and job with x++ code the result is incorrect.

finally i want to design a report with sum of sold quantity and sum of on hand group by item id in sales line.

please advice

Thanks..

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report
Dear Essam,

From what i understand from your question, you need to create a query like following:

Just a few notes:

All Datasource should have property Relations = Yes

All Datasource should have property Fetchmode = 1:n

All Datasource should have property JoinMode = innerJoin

I hope this help you in your project.

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

Like (0)

Report

It would help if you explained what you did and what's the problem. You can't seriously expect any specific advice if you just say that you tried a query but it's incorrect.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Mr. Ashkan for your support.

i will try this query.

Thanks again.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Mr. Martin.

i will post my project code. i am sorry.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Dear Sir

i want get the sum(inventsum.postedqty) and sum(salesline.salesqty) group by itemid

this is my query

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

Like (0)

Report

So what's the problem? We can't give you any solution if you don't explain your problem.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Dear Mr. Martin

this is a true result with no relation between Salesline and inventsum where group by Inventsum.itemid,Salesline.itemid and sum(inventsum.postedqty),sum(salesline.qty) but i should insert this search criteria in two item id columns to get the true result.

this is a false result when i try to join 2 tables "Inner join" with relation inventsum.itemid==salesline.itemdid and group by salesline.itemid and sum(inventsum.postedqty),sum(salesline.qty).

Thanks for your support

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

Like (0)

Report

I don't know data in your database, therefore the numbers say nothing to me.

By the way, what are you trying to achieve by your query (joining SalesLine and InventSum over ItemId only, completely ignoring InventDimId)?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Mr. Martin

i want to get the sum for sold qty and sum of on hand quantity for the salesline items.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

I assume you have 4 sales lines with this item and one invent sum. When you select inner join you combine every combination possible, resulting in this:

Item      Qty

SM1A    14

SM1A    14

SM1A    14

SM1A    14

And then you group by item and sum the qty resulting in 56.

Your requirement is not clear, but to me it seems like you dont need the saleslne in your query. You just need to add the itemId as a range on the inventSum

Was this reply helpful? Yes No