web

You’re offline. This is a read only version of the page.

News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics AX (Archived) distinct keyword for r...

Microsoft Dynamics AX (Archived)

distinct keyword for records from AOT query

(0) Share

Report

Report

Posted on by Community Member

Hi,

I added few 3 different views to the AX standard query ProjTable_NoFilter.

Each of these view has amount column and i want to show Sum of that into ProjProjectListPage list Page.

but these views are replicating the records and hence i am getting wrong Sum of the Amount.

It's quite large amount than expected because of the extra rows created by the Join between these views.

If i keep single view in the AOT Query, sum is correct. but as soon as I add the 2nd view it actually shows larger value than expected.

I found that from T-SQL query that if we use Select 'DISTINCT' keyword, everything works fine. But i am not sure how to use distinct in AOT Query.

Any pointer would be much appreciated.

*This post is locked for comments

I have the same question (0)

All responses Img

All responses (4)

Answers Img

Answers (1)

Suggested answer

Iulian Cordobin 8,201 on at

Like (0)

Report

Unfortunately there is no DISTINCT keyword in AX. You'll have to rethink your approach.

Was this reply helpful? Yes No
Iulian Cordobin 8,201 on at

Like (0)

Report

By rethinking the approach I mean redesign your Query. Sounds like you are getting wrong tables cardinality mixed.

In your query how are you adding your views?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Thanks Lulian.

Even if there was distinct keyword, performance issue of the loading form would have still remained.

So I created a three different views for each of the new sum column and joined them in the base query. That solved both data and performance issues.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

form works fine but now i am facing issue to show one of the sum to a Cue i created.

My Cue is pointing to the standard AX menuitem ProjProjectsListPage_MyProjects.

and while adding a Cue to the Role Center, i picked up one of the table and then sum column. but it says sum calculation failed. Not sure why?

Was this reply helpful? Yes No

Helpful resources

Quick Links

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1

Martin Dráb 4 Most Valuable Professional

#1

#3

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans