web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / how to use aggregate f...
Microsoft Dynamics AX (Archived)

how to use aggregate function in update_recordset

(0) ShareShare
ReportReport
Posted on by Fahad Ahmed

I need to update a column on bulk of records using udpate_recordset.

The value would be a sum of amount being retrieved from another table. Here is the syntax that I come up with. both table1 and table2 are tempDB.

 

 

                update_recordSet table1

                setting value= table2.Amount                

                join sum (amount) from table2

                where table1.ID== table2.ID;

 

 

it passed the compiler but throws the following error on runtime

 

Cannot edit a record in table1.
The record has never been selected.

 

any thoughts?

-Fahad

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Hi Fahad,

    I have experienced the same issue in the job, however it doesn't throw any error if the query is used inside a class or table methods. Another thing is, it actually doesn't aggregate the column values. It only fetches the value of the first record of Table2.Amount. Only solution I can see is create a view of Table2 with a Query that aggregates Amount grouping by table2.ID. After that join that View with Table1 in your query of update_recordset.

    However, creation of view won't be required if you use "while select" ... Looks like this is an issue with update_recordset with Sum() in a join...

    Regards,

    Amit

  • Community Member Profile Picture
    Community Member on at

    Hmmm...not sure if a view works on tempdb or not, but you can always create an intermediary tempdb table and do an insert_recordset and then update your table1.

    Aggregations don't work on update_recordset but they definitely work using insert_recordset.

  • Ismail Mohamed Profile Picture
    Ismail Mohamed 576 on at

    Kyle L 

    thank you

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Sukrut Parab Profile Picture

Sukrut Parab 2 Moderator

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans