Skip to main content

Notifications

Announcements

Give Back to the Community this Month
News and Announcements icon
Dynamics 365 Community / Blogs / Dynamics 365 F & O - Knowledge sharing by B... / SysDa Update in X++ D365FO ...

SysDa Update in X++ D365FO Part II

Bharani Preetham Peraka Profile Picture Bharani Preetham Pe... 3,582 Super User 2024 Season 1
In continuation to my older article on SysDa API for inserting records, here we go with my new article on updating table with some joins and complex syntaxes of SysDa involved.

Lets not wait more! 

Use Case:
In a scenario where performance is considered too critical, then we need to go with this.

Scenario:
We are going to update a field of a table. This can be done using update_recordset or Query::update_recordset.

The current approach is much more faster than the above 2.

Lets say we have header and line tables, perhaps we can take as an example of SalesQuotationTable and SalesQuotationLine. We have some status on Line level.

So if the current date is equal to the Expiry date of SalesQuotationHeader, we are going to mark the Line with some status like lets say stop.

So now, lets see the code and we will understand.

//Table buffers declaration
SalesQuotationTable salesQuotationTable;
SalesQuotationLine salesQuotationLine;

//Getting and setting the current date to Expiry date Variable

TransDate expiryDate = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());


//Creating Update Object on SalesQuotationLine which generates an SQL Statement same like update_recordset tableName

var updateObject = new SysDaUpdateObject(salesQuotationLine);

//The below statement is like assigning a value or to the field like SET fieldName = "some Value"

updateObject.settingClause()
     .add(fieldStr(SalesQuotationLine, SQStatus)
     ,new SysDaValueExpression(SQStatus::Stop));

//WHERE Condition along with AND

queryObject.whereClause(
      new SysDaEqualsExpression(
          new SysDaFieldExpression(salesQuotationTable, fieldStr(SalesQuotationTable, QuotationId)),
          new SysDaFieldExpression(salesQuotationLine, fieldStr(SalesQuotationLine, QuotationId)))
           .and(
      new SysDaEqualsExpression(
          new SysDaFieldExpression(salesQuotationTable, fieldStr(SalesQuotationTable, QuotationExpiryDate)),
          new SysDaValueExpression(expiryDate))));


//JOIN

updateObject.joinClause(SysDaJoinKind::ExistsJoin, queryObject);


//Update Operation Final Execution

ttsbegin;
new SysDaUpdateStatement().update(updateObject);
​​​​​​​ttscommit;

Code Explanation:

As you guys already know, I'm updating a field using SysDa API.

Using SysDaUpdateObject, I told the system that we are going to update this table. And then assigned some value which we can say as "STOP". Then we are having a where clause, which seems for visibility, it  tricky but it is not in actual.

In the WHERE clause, I'm writing a general relation between SalesQuotationLine and SalesQuotationTable and then adding another condition with AND, checking if the Expiry Date at Header is actually equal to the current date which we found at the start.

And then I gave a join clause since I'm doing a join with Header table and made a join of Exists Join as you all know that I am a big fan of this join :)

Finally in a transaction, I called the actual update by passing the updating object as a parameter.
​​​​​​​
The syntax is bit time taking to write in actual but this gives a huge boost to the total performance.


That's all about Update Operation with Joins using SysDa.


Stay Tuned!

Happy Learning :p

code snippet widget

Comments