aot query with groupby on purchline table with sum causing extra rows

(0) Share

Report

Posted on by jason.bouchard

I've created an aot query against the PurchLine table with a sum of LineAmount grouped by PurchId and am getting multiple rows (one per purchline record) when I check a view against the query. Please advise. Below is the query. DataSource set to no Dynamic Fields.

I have the same question (0)

All responses (20)

Answers (2)

Martin Dráb 237,829 Most Valuable Professional on at

Like (0)

Report

Can you please elaborate what you mean by "I am getting multiple rows (one per purchline record)" and "I check a view against the query"?

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

So can you please tell me the exact issue you face here? As per given query it is giving 1 record per purch id as grouping on purchid with sum of line amount. Means

PONum totalamt

PO00001 200000

PO00002 30000

Was this reply helpful? Yes No
jason.bouchard 20 on at

Like (0)

Report

I created an aot view against the query and checked the view in the table browser and see the results returning 1 record per purchline record.

Was this reply helpful? Yes No
jason.bouchard 20 on at

Like (0)

Report

I thought the purchid is the link back to the purchtable records? I want to get a sum for the entire purchase order of the line amounts.

Was this reply helpful? Yes No
jason.bouchard 20 on at

Like (0)

Report

Yes per AOT the PurchId is the link back to purchtable.

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

Like (0)

Report

Are you saying that the query work correctly and your problem lies in the view that you told nothing about?

Was this reply helpful? Yes No
jason.bouchard 20 on at

Like (0)

Report

At this point I am not sure if the problem lies in the view or query. Here is the view to the query.

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

Like (0)

Report

Then I recommend forgetting the view for now and focusing on the query. If the query doesn't work correctly, there is no pointing in using it in a view, because it can't work either. And if it works correctly, you'll know that there is nothing to fix in the query and your problem must lie elsewhere.

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Can you please elaborate your issue? As per query it is returning data as expected. Now what's your issue in view? If you make us clear on this thing we can help you. If that is not expected then please let us know what is expected?

Was this reply helpful? Yes No
jason.bouchard 20 on at

Like (0)

Report

As stated, when I create a query against the view as above, it is returning a record per PurchLine with the LineAmount for the PurchLine record. That is not what I need, I need one record per PurchId with sum of LineAmount from PurchLine table grouped by PurchId.

Was this reply helpful? Yes No