Send an email When an item in a SharePoint list is modified based on the value of an item changed

[AT_AlanTse123]

Hi all, i am trying to create power automate flow to send an email when an item in a Sharepoint list is modified based on item value changed. 

For example, i had the column in the SharePoint List called "Cable left". If the value of "Cable left" changed, it will send an email.  

I found that Microsoft provided the template for the captioned subject but how can i set the condition that if value of "Cable left" increase or decrease, it will send email?

Would anyone can help me?

Thank you very much.

Sorry for my poor english.

[Sundeep_Malik]

Hey @AT_AlanTse123 

Not sure how we can determine if the value increased or decreased. But by using the action called send http request to sharepoint you will be able to get the previous value. You already have the current value. So, by this you can automatically know if it increased or decreased. Or maybe send both values in the email.

Giving you a link how you can do the above:

https://www.sharepointsiren.com/2021/05/power-automate-sharepoint-get-column-changes/

https://www.youtube.com/watch?v=DT6BpixFbHY

[AT_AlanTse123]

Thank you very much for your information.

I tried and refer the link to create the flow and tested not work, it showed the error:

The length of the URL for this request exceeds the configured maxUrlLength value.

And it showed the error is on the third block ("Send an HTTP request to Sharepoint").

I don't know how to change Uri.

Would you please help me?

Thank you.

Below is the flow:

[Sundeep_Malik]

Hey @AT_AlanTse123 

Nice work till now. 

The place where you made a mistake is,

in the uri, in the versions, just pass the since version dynamic field dont pass the body.

Body contains everything of that item, but since version will only have the since version for that item.

Now it should work.

Just paste this in expressions after versions:

outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/SinceVersionId’]

[AT_AlanTse123]

Thank you for your reply.

I try to refer your suggestion to update the uri and run again.

It showed the error:

Cannot find resource for the request version.

but i had enable versioning 

[Sundeep_Malik]

@AT_AlanTse123 

I edited my above comment to paste this expression after versions:

outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/SinceVersionId’]

Did you do that? still getting error, if yes, send the ss of both the flow in edit mode and the error in run history.

[AT_AlanTse123]

I do that but still has error : bad request , below is the flow in edit mode:

Error message

Thank you very much.

[Sundeep_Malik · Accepted solution]

@AT_AlanTse123 

Oh saw the mistake:

I just tried it on my end and it worked. You have directly written the expression. Do something like this:

Just paste the following directly in your uri:

_api/web/lists/getByTitle('CLPeS Cable Store')/items(@{triggerOutputs()?['body/ID']})/versions(@{outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']})

Hopefully it works this time.

[Sundeep_Malik]

@AT_AlanTse123 

But when I tried this, I noticed something. It will only give you a true or false value for the column changed. It will not tell you the previous value. 😞

So, you will not be able to determine it increased or decreased.

Edited:

It gave I was using it incorrectly. Haha.

[AT_AlanTse123]

It is worked, thank you very much for your help.

Ok, it is not be able to determine it increased or decreased 

That's the way it is

[Sundeep_Malik]

@AT_AlanTse123 

After this use Get item action. Then take the column for which the value has changed. Then just find the difference between them. Something like this:

[AT_AlanTse123]

Thank for your guide, but next step is send an http request to sharepoint or other?

I tested send an http request to sharepoint and follow previous method that cause error

[Sundeep_Malik]

Hey @AT_AlanTse123 

So, I tried it on my end. This is how my flow looks like:

In old value compose I used the following expression:

body('Send_an_HTTP_request_to_SharePoint')?['d/OData__x005f_x0043_x005f_1']

Above OData... is my column name, you should enter the cable left column or something. Or just send the screenshot of the body you are receiving from the send http action. I will tell you what to write in it.

In New Value I am getting the new value for the above column, you can get the value of cables left.

C1 is the column for me:

After this I am just taking out the difference of both the above, if it is negative then the value decreased if it is positive then the value decreased. 

Expression I have used:

sub(int(outputs('NewValue')),int(outputs('OldValue')))

I used int because my columns in SP list were single line of text, in your case if the column is a number no need to put it. 

My old value was 10, New value is 40. So, the difference came out 30. 

I know its a little complicated. If you want to try you can, otherwise just ignore. 😉

[AT_AlanTse123]

Thank you very much!!!

I follow your method, it is error on difference and old value, the error is:

Unable to process template language expressions in action 'Difference' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Would you please tell how to change the old value?

Below screenshot for your reference:

Send an HTTP request to SharePoint

Raw inputs:

{
"host": {
"connectionReferenceName": "shared_sharepointonline",
"operationId": "HttpRequest"
},
"parameters": {
"dataset": "https://group.sharepoint.com/sites/CLPePowerApps",
"parameters/method": "GET",
"parameters/uri": "_api/web/lists/getByTitle('CLPeS Cable Store')/items(6)/versions(5120)"
}
}

Raw Outputs:
{
"statusCode": 200,
"headers": {
"Transfer-Encoding": "chunked",
"Vary": "Origin,Accept-Encoding",
"X-NetworkStatistics": "0,262656,0,0,226542,0,71447",
"X-SharePointHealthScore": "1",
"X-SP-SERVERSTATE": "ReadOnly=0",
"DATASERVICEVERSION": "3.0",
"SPClientServiceRequestDuration": "84",
"X-DataBoundary": "NONE",
"X-1DSCollectorUrl": "https://mobile.events.data.microsoft.com/OneCollector/1.0/",
"X-AriaCollectorURL": "https://browser.pipe.aria.microsoft.com/Collector/3.0/",
"SPRequestGuid": "c1f07da0-60ec-1000-e0f5-89fbf15185a5",
"request-id": "c1f07da0-60ec-1000-e0f5-89fbf15185a5",
"MS-CV": "oH3wwexgABDg9Yn78VGFpQ.0",
"Strict-Transport-Security": "max-age=31536000,max-age=31536000; includeSubDomains",
"X-FRAME-OPTIONS": "SAMEORIGIN,DENY",
"Content-Security-Policy": "frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com *.powerapps.com *.yammer.com *.officeapps.live.com *.office.com *.stream.azure-test.net *.microsoftstream.com *.dynamics.com *.microsoft.com onedrive.live.com *.onedrive.live.com securebroker.sharepointonline.com;",
"MicrosoftSharePointTeamServices": "16.0.0.23109",
"X-Content-Type-Options": "nosniff,nosniff",
"X-MS-InvokeApp": "1; RequireReadOnly",
"x-ms-request-id": "c1f07da0-60ec-1000-e0f5-89fbf15185a5",
"Cache-Control": "no-store, max-age=0, private",
"P3P": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"",
"Set-Cookie": "ARRAffinity=0102e146bf02da3054822d4acfdabff925d548924e264227d7e2dd7a4eef2676;Path=/;HttpOnly;Secure;Domain=sharepointonline-ea.azconn-ea.p.azurewebsites.net,ARRAffinitySameSite=0102e146bf02da3054822d4acfdabff925d548924e264227d7e2dd7a4eef2676;Path=/;HttpOnly;SameSite=None;Secure;Domain=sharepointonline-ea.azconn-ea.p.azurewebsites.net",
"X-AspNet-Version": "4.0.30319",
"X-Powered-By": "ASP.NET",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "true",
"x-ms-apihub-obo": "false",
"Date": "Thu, 01 Dec 2022 08:48:15 GMT",
"Content-Type": "application/json; odata=verbose; charset=utf-8",
"Expires": "Wed, 16 Nov 2022 08:48:15 GMT",
"Last-Modified": "Thu, 01 Dec 2022 08:48:15 GMT",
"Content-Length": "3617"
},
"body": {
"d": {
"__metadata": {
"id": "https://group.sharepoint.com/sites/CLPePowerApps/_api/web/lists/getByTitle('CLPeS Cable Store')/items(6)/versions(5120)",
"uri": "https://group.sharepoint.com/sites/CLPePowerApps/_api/web/lists/getByTitle('CLPeS%20Cable%20Store')/items(6)/versions(5120)",
"type": "SP.ListItemVersion"
},
"CreatedBy": {
"__deferred": {
"uri": "https://group.sharepoint.com/sites/CLPePowerApps/_api/web/lists/getByTitle('CLPeS%20Cable%20Store')/items(6)/versions(5120)/CreatedBy"
}
},
"Fields": {
"__deferred": {
"uri": "https://group.sharepoint.com/sites/CLPePowerApps/_api/web/lists/getByTitle('CLPeS%20Cable%20Store')/items(6)/versions(5120)/Fields "
}
},
"FileVersion": {
"__deferred": {
"uri": "https://group.sharepoint.com/sites/CLPePowerApps/_api/web/lists/getByTitle('CLPeS%20Cable%20Store')/items(6)/versions(5120)/FileVersion"
}
},
"Created": "2022-12-01T08:44:08",
"IsCurrentVersion": false,
"VersionId": 5120,
"VersionLabel": "10.0",
"ID": 6,
"ContentTypeId": {
"__metadata": {
"type": "SP.ContentTypeId"
},
"StringValue": "0x010085C890E2282C1641A8DCBF3710673C80"
},
"Title": "145093KSE",
"Modified": "2022-12-01T08:44:08Z",
"Author": {
"__metadata": {
"type": "SP.FieldUserValue"
},
"LookupId": 9,
"LookupValue": "full name removed",
"Email": "email address removed"
},
"Editor": {
"__metadata": {
"type": "SP.FieldUserValue"
},
"LookupId": 9,
"LookupValue": "full name removed",
"Email": "email address removed"
},
"OData__x005f_HasCopyDestinations": null,
"OData__x005f_CopySource": null,
"owshiddenversion": 10,
"WorkflowVersion": 1,
"OData__x005f_UIVersion": 5120,
"OData__x005f_UIVersionString": "10.0",
"Attachments": false,
"OData__x005f_ModerationStatus": 0,
"OData__x005f_ModerationComments": null,
"InstanceID": null,
"Order": 600,
"GUID": "bf1e4a74-1695-420e-a2e9-b1b06837c4ee",
"WorkflowInstanceID": null,
"FileRef": "/sites/CLPePowerApps/Lists/CLPeS Cable Store/6_.000",
"FileDirRef": "/sites/CLPePowerApps/Lists/CLPeS Cable Store",
"Last_x005f_x0020_x005f_Modified": "2022-10-18T13:50:15Z",
"Created_x005f_x0020_x005f_Date": "2022-10-18T13:50:15Z",
"FSObjType": "0",
"SortBehavior": {
"__metadata": {
"type": "SP.FieldLookupValue"
},
"LookupId": 6,
"LookupValue": "0"
},
"UniqueId": "b7091bac-59e1-4212-b774-1df68dce6361",
"ParentUniqueId": "{32A6C314-3FAD-4A35-8FBB-032F5710E4BD}",
"SyncClientId": {
"__metadata": {
"type": "SP.FieldLookupValue"
},
"LookupId": 6,
"LookupValue": null
},
"ProgId": "",
"ScopeId": "{81A5F02B-68DE-4BA3-833F-5E99222D9446}",
"File_x005f_x0020_x005f_Type": null,
"MetaInfo": "",
"OData__x005f_Level": 1,
"OData__x005f_IsCurrentVersion": false,
"ItemChildCount": "0",
"FolderChildCount": "0",
"Restricted": "",
"OriginatorId": "",
"NoExecute": "0",
"ContentVersion": "0",
"OData__x005f_ComplianceFlags": "",
"OData__x005f_ComplianceTag": "",
"OData__x005f_ComplianceTagWrittenTime": "",
"OData__x005f_ComplianceTagUserId": "",
"AccessPolicy": "",
"OData__x005f_VirusStatus": "",
"OData__x005f_VirusVendorID": "",
"OData__x005f_VirusInfo": "",
"AppAuthor": null,
"AppEditor": null,
"SMTotalSize": {
"__metadata": {
"type": "SP.FieldLookupValue"
},
"LookupId": 2528,
"LookupValue": ""
},
"SMLastModifiedDate": "2022-12-01T08:47:45Z",
"SMTotalFileStreamSize": "0",
"SMTotalFileCount": {
"__metadata": {
"type": "SP.FieldLookupValue"
},
"LookupId": 0,
"LookupValue": ""
},
"ComplianceAssetId": null,
"field_x005f_1": "4600008487",
"field_x005f_2": "10018711",
"field_x005f_3": "HV - 120mm 3C AL XLPE",
"field_x005f_4": "KSE",
"field_x005f_5": "CLPE Tsing Yi",
"field_x005f_6": "400",
"field_x005f_7": "2902.5",
"field_x005f_8": "3278",
"field_x005f_9": null,
"field_x005f_10": "Full drum in Tsing Yi",
"field_x005f_11": null,
"field_x005f_12": null,
"field_x005f_13": null,
"field_x005f_14": null,
"Hold": null,
"FileLeafRef": "6_.000",
"OData__x005f_CommentFlags": "",
"OData__x005f_CommentCount": ""
}
}
}