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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Update recordset insid...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Update recordset inside while loop

(0) ShareShare
ReportReport
Posted on by ShaqAX 709

Hi guys,

Can I use "update_recordset" to the same variable (table) which is used in its while loop ? Will that change the records retrieved when the "while..." executed ?

I'm writing a code to similar like this : 

RetailTransactionSalesTrans RetailTransactionSalesTrans1, RetailTransactionSalesTrans2
 
select firstonly RetailTransactionSalesTrans1 
where RetailTransactionSalesTrans1.transdate == systemdateget() && RetailTransactionSalesTrans1.CustAccount='TEST-1';

while select transDate, custAccount, ItemId, unit, TaxGroup, TaxItemGroup,
 	sum(Qty), sum(discAmount), sum(taxAmount), sum(netPrice), sum(NetAmount), sum(NetAmountInclTax), avg(Price)
        from RetailTransactionSalesTrans2
        group by transDate, custAccount, ItemId, unit, TaxGroup, TaxItemGroup
        where RetailTransactionSalesTrans2.transDate == RetailTransactionSalesTrans1.transDate
                            && RetailTransactionSalesTrans2.custAccount == RetailTransactionSalesTrans1.custAccount
                            && RetailTransactionSalesTrans2.MY_ReceiptType == RetailTransactionType::Sales
                            && RetailTransactionSalesTrans2.MY_StatementNumber == ""
                            && RetailTransactionSalesTrans2.MY_StatementLineRefRecId == 0
{
.
.
	newTableA.insert();
.
.

	Update_recorset RetailTransactionSalesTrans2
		setting MY_StatementNumber = newTableA.numberSeq,
		        MY_StatementLineRefRecId = newTableA.recid
	where RetailTransactionSalesTrans2.transDate == RetailTransactionSalesTrans1.transDate
                            && RetailTransactionSalesTrans2.custAccount == RetailTransactionSalesTrans1.custAccount
                            && RetailTransactionSalesTrans2.MY_ReceiptType == RetailTransactionType::Sales
                            && RetailTransactionSalesTrans2.MY_HasError == NoYes::No;

}

Can the "update_recorset" somehow makes the records created in "while" statement damaged ? Since I test in SQL, with that criteria will result 2 records, but then when debugging, it will loop forever, on the last record (2nd row item)

The idea is in this While statement, I will need to insert it into my other table (newTableA), and when it is insert successfully, I will need to update the source table with the reference Id number and Refrecid of the new record in newTableA.

How to handle this in simple but correct way ? I mean I can look into some of the existing process (code) but it looks quite complicated (or is it have to be that way), also not sure what I'm looking is the correct part to follow. 

Thanks,

I have the same question (0)
  • Suggested answer
    Arunraj Rajasekar Profile Picture
    Arunraj Rajasekar 1,743 on at

    I don't believe update recordset is required in this case.

    Once the while loop is executed, the variable used in the while loop will only return the most recently executed record. When that variable is used with update recordset, it will only update the last record in the loop.

    After the insert is successful, you can try to update the record within the loop.

    newTableA.insert();

if(newTableA.recid)
{  
    newTableA.selectforupdate(true)
    //logic
    newTableA.update();
}

    Another option is to try storing the values in a list after inserting it. Outside of the loop, get the values from the list and update it.

  • ShaqAX Profile Picture
    ShaqAX 709 on at

    Hi Arunraj,

    May I know exactly what you mean by "When that variable is used with update recordset, it will only update the last record in the loop." ?

    The update_recordset that I'm using is actually trying to update the same table variable that used in While select statement. Yes, I guess have to update outside the loop then. But the problem is the "While select" statement is a group by (aggregated), so not too sure whether storing the value in the list is good approach. Let me try first.

    Thanks

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 617

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 461 Super User 2026 Season 1

#3
Syed Haris Shah Profile Picture

Syed Haris Shah 298 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans