Dataverse to List flow (create/update/delete)

[michellecoslet]

I am working on a flow to basically mirror a Dataverse Table to a Sharepoint List. The flow is running however it is creating countless entries for the same item as well as blank items. I followed a video to create the flow and must be missing something critical. My end goal is the following:

1. When a NEW item is added to the Dataverse table, create a new item into the Sharepoint list

2. When an item is updated in the Dataverse table, update the same item in the Sharepoint list

3. When an item is deleted in the Dataverse table, delete the same item in the Sharepoint list

Here is a screenshot of my current flow:

The Unique identifier for entity instances in the Dataverse table is EmployeeWithFullNamesid

Here is a screenshot of my data from my Dataverse Table:

@Expiscornovus 

[Nived_Nambiar]

Hi @michellecoslet 

Could you share the screenshot of sharepoint list and dataverse table , from above screenshots, it is not clear 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[michellecoslet]

dataverse table:

sharepoint list after running test (after adding zzz and xxx test entries):

Last week, the accrued days was carrying over to the list, now it is now. 

[michellecoslet]

flow has been left on and it periodically updates the list with new information. here is the latest screenshot. My table has 36 entries (employees) however my sharepoint list is only showing 4 at the moment. and amand is listed 3 times with the same information.

[Nived_Nambiar]

Hi @michellecoslet 

I have designed the sample flow which does the record creation, updation and deletion in sharepoint list when row in dataverse is deleted,updated or created.

I have dataverse table like below which have three main column : A, B and Sum, The record id is NewTable

I have designed sharepoint list which has columns: NewTableID : Which represents new table id (record id in dataverse table), A,B,Sum.

Flow is designed like below:

Step1:

Flow is triggered with below trigger

Step 2: using the SdkMessage  property which is present in body dynamic content of the trigger output which denotes the action done on record whether it is create, delete, update. Based on above property a switch is created like below

write that expression in switch so to get the SDKMessage value (since i could not see that property in dynamic content)

Now based on values of SdkMessage property, create three switch statements like below

1. Create: 

This switch statement run when the record is created in dataverse table, so a item should be created in sharepoint list

Here for title , i just placed expression guid() for demo purposes, you can change it accordingly as per your requirement.

Delete: when record in dataverse table is deleted, item corresponding to that record is deleted in sharepoint list. we can fetch the record in sharepoint list by using filter query with NewTableID column since it has record id of dataverse table

Update: when record in dataverse table is updated, corresponding item in sharepoint list is also updated , the method of finding item in sharepoint corresponding to updated record is simmilar to what you have done in delete action.

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[michellecoslet]

first off, thank you for posting such a thorough response!!!

So I was able to replicate your setup except for the Switch expression. It is not working as a dataverse expression and as a result i get the following error at testing:

The execution of template action 'Switch' failed: The result of the evaluation of 'scope' action expression '@triggerBody()?['body']?['SdkMessage']' is not valid. It is of type 'Null' but is expected to be a value of type 'String, Integer'.

[michellecoslet]

After several attempts at rewriting the expression, it finally displayed like yours:

However i still receive the error on update and create so far.

[Nived_Nambiar]

Hi @michellecoslet 

to investigate the issue, we need to figure out what is the expression output of triggerBody()?['body'] , could you check that using compose action ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[michellecoslet]

run test:

i just get a download link that leads to a blank page (no data downloaded):

now, if i run the compose on just 'body', I get the following:

{

  "cr2fa_emp_statuscode": "Active",

  "cr2fa_attendancestatus": true,

  "cr2fa_employeewithfullnamesid": "bfbf2215-a466-ee11-9ae7-000d3a1a3c76",

  "cr2fa_emp_name_lfm": "Vang, Mai See",

  "_owningbusinessunit_value": "2c2bb339-412e-ec11-b6e5-000d3a582004",

  "_owningbusinessunit_value@Microsoft.Dynamics.CRM.lookuplogicalname": "businessunit",

  "_owningbusinessunit_type": "businessunits",

  "statecode": 0,

  "_statecode_label": "Active",

  "statuscode": 1,

  "_statuscode_label": "Active",

  "cr2fa_accrueddays2": "0",

  "cr2fa_emp_lastname": "Vang",

  "cr2fa_emp_department": "SHOP FLOOR",

  "cr2fa_emp_employeeid": "442",

  "_ownerid_value": "6d861b40-412e-ec11-b6e5-000d3a582004",

  "_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",

  "_ownerid_type": "systemusers",

  "modifiedon": "2023-10-11T15:02:23Z",

  "cr2fa_emp_recordid": "993FC8E6-FC44-42EB-9937-7A772AF703C0",

  "_modifiedby_value": "6d861b40-412e-ec11-b6e5-000d3a582004",

  "_modifiedby_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",

  "_modifiedby_type": "systemusers",

  "cr2fa_emp_firstname": "Mai See",

  "createdon": "2023-10-09T13:02:31Z",

  "_createdby_value": "6d861b40-412e-ec11-b6e5-000d3a582004",

  "_createdby_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",

  "_createdby_type": "systemusers",

  "ItemInternalId": "bfbf2215-a466-ee11-9ae7-000d3a1a3c76",

  "SdkMessage": "Update",

  "RunAsSystemUserId": "6d861b40-412e-ec11-b6e5-000d3a582004",

  "RowVersion": "806297294"

}

[Nived_Nambiar]

Hi @michellecoslet 

is there any body dynamic content in the output of trigger, could you place there instead ?

Also are you doing any update to dataverse table , like update, create or delete ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[michellecoslet]

I get a litle closer if i use the following in Switch:

Result:

[Nived_Nambiar · Accepted solution]

Hi @michellecoslet 

This seems to be strange, yesterday the dynamic content which i add in switch was showing like triggerBody()?['body']?['SdkMessage'] 

But now it is showing as triggerOutputs()?['body']?['SdkMessage'] i should have notice it earlier, 

So you are correct with respect to your expression in switch action.

Now coming to the error, could you show how you have written the filter query in get items under Update, this would help to understand what has gone wrong in flow ?

The filter query should be like this :

The new table dynamic content should be enclosed within single quotes. I think you have enclosed in double quotes.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[michellecoslet]

Not a problem - here is how I have it setup now:

i originally had this field named "Record ID" but changed it to "ID0" thinking perhaps there was an issue with the naming. neither fixed the error however.

[michellecoslet]

Works!

Note: I did have to change the name back to RecordID in the filter and added the single quotes. 

Thank you!!!