CODEWITHSUNDEEP
javadatabaseazureazure-devopsdataverse
anonymous
anonymous

Reputation: 260

How to detect changes in records of Dataverse tables

I have a requirement to fetch the records from the Dataverse in which some changes have been done in specif columns values. For example, let's say we have a table named employee in which we have a field called position which can be changed over time from intern, software developer, development lead, etc. If we have 10 records currently and if the position of one of the employees gets changed, I need only that one employee record. I have gone through Retrieve and detect changes to table definitions but I believe it is related to changes in the schema and not related changes in the data. I am using the Spring Boot with Java 11 and to work with Dataverse I am using the Olingo library and also may use the Web APIs if required. Is there a way to detect the changes in the data as described above?

EDIT

To add more details we will have a scheduled job that triggers at X minutes which needs to fetch the employee data for which position has changed from the last fetch time of X minutes. As we can see in the image below, all 3 records are being updated in that X minutes internal and last modified time has been updated for all. I need to fetch the records highlighted in green for which position attribute has changed. For a record with Id 2, I don't need to fetch it as the position is the same.

enter image description here

Upvotes: 1

Views: 4415

Answers (1)

J-M
J-M

Reputation: 1497

Solution 1: Custom changes table

If you may and can extend your current Dataverse environment

  1. Create a new table called Employee Change. Add a column of type Lookup named Employee and link it to your Employee table
  2. Modify Main Form and add Employee column to the form
  3. Create a workflow process which would fire on field change. Inside the workflow process you create an Employee Change record and set lookup column value to the changed record

You can now query Employee Change table for changed records. You would need to expand the lookup column to get required columns from Employee table.

Example Web API query:

GET [Organization URI]/api/data/v9.1/employee?$select=createdon, employeeid
&$expand=employeeid($select=employeeid,fullname,column2,column3) HTTP/1.1  
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0

More info on expanding lookup columns can be found here

Solution 2: Auditing

Use built-in auditing feature

  1. Make sure auditing is enabled. Details can be found in docs
  2. Enable auditing on required column in Employee table
  3. Query audit records for changes in Employee table. You have to pay attention only to changes to specific attributes of interest
  4. You will get a list of records changed and then you have to query once again to retrieve columns of the records

Solution 3: Push instead of pull

It might make more sense to push changes from Dataverse to your API instead of constantly querying for changes. You could use Microsoft Power Automate to create a simple flow which would call your API / platform when change is detected in Dataverse

A good start could be exploring the following Power Automate template: When a record is updated in Microsoft Dataverse, send an email. You could then replace "send email" steps with querying other APIs

Upvotes: 1

Related Questions