Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

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

(0) ShareShare
ReportReport
Posted on by 20

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.

pastedimage1678883905937v1.pngpastedimage1678884137185v2.png

  • jason.bouchard Profile Picture
    jason.bouchard 20 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    Thank you, an article I was following was showing to have a query also behind the view which as you stated was not needed.  Just the view  makes more sense from a database perspective.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,900 Most Valuable Professional on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    Note that you don't a query at all. You could have set it directly in the view and you wouldn't have to deal with two different things.

  • jason.bouchard Profile Picture
    jason.bouchard 20 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    This seemed to work from testing.  That is just funky how they did this.  The grouping was defined in the query and then you have to define it again with the view?

    Appreciate the help all.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,900 Most Valuable Professional on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    You need to do it directly on the view field. Set the Aggregation property to Sum.

  • jason.bouchard Profile Picture
    jason.bouchard 20 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    So spitting out the contents from running the query in a runnable class is looking like the query is not duplicating the records.  So how do I use the query with the view then without the duplicates?  It is not letting me drag the sum onto the field list in the view?

  • jason.bouchard Profile Picture
    jason.bouchard 20 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    I am trying this from AOT query and qot view, to add a field to all purchase orders form. purchtable form.  the runnable class was to try to test why I'm getting dupes and to isolate it a problem with the query or view.

  • jason.bouchard Profile Picture
    jason.bouchard 20 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    I do not see the queries defined anywhere in SSMS?  I see the views though.

  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    Also if you just needs to see the records with that query, simply fire the same SQL query in SSMS. It is more simpler.

  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    The process is correct. If it is not working then you need to check if sync is done perfectly.

    The other simpler approach is to write a while select statement grouping on purchid and returning each purch I'd and sum of the line amount in the runnable job.

  • jason.bouchard Profile Picture
    jason.bouchard 20 on at
    RE: aot query with groupby on purchline table with sum causing extra rows

    I am trying to print the output of the query with a runnable class, I'm not aware of another way to get the output from the query, the view seemed to be the easiest way.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,074 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,900 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans