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 / 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

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 > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 592 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 478 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 305 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans