cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lordneeko
Frequent Visitor

Get Columns that Changed in a Sharepoint list

I have a flow that I want to use to report when changes are made to a SharePoint list (and to show exactly what changed). I'm posting the information as a notification in MS Teams.

 

I currently have the flow setup to grab the last change and that works just fine.  The problem I'm running into is that the data is returned as an object like this:

{
"SinceVersionExisted": true,
"SinceVersionId": 1024,
"SinceVersionLabel": "2.0",
"UntilVersionIsCurrent": true,
"UntilVersionId": 1536,
"UntilVersionLabel": "3.0",
"ColumnHasChanged": {
"ID": false,
"Title": true,
"Priority_x0020_Tier": false,
"Section": true,
"DAA": false,
}
}

 

 

I want to be able to filter that "ColumnHasChanged" object for only those items that are "True" then output that as either a string or Table or something like this

 

FieldChanged
Titletrue
Sectiontrue

 

Eventually the plan is to retrieve the changes and show those as well (and eventually propagate those changes down-range, but I'm in the crawl phase right now.

 

I thought I'd be able to "apply to each" the "ColumnHasChanged" object, but that doesn't work because it isn't an array/collection.  

 

Now sure how to proceed. Do I need to convert the object to an array?

1 ACCEPTED SOLUTION

Accepted Solutions
lordneeko
Frequent Visitor

Ok I'm updating this again as I've gotten smarter.  I was actually able to meet my original goal of turning the Object, into an array which I can iterate over and work with (and use to push the data to other sources).  The original 'solution' I submitted was VERY slow and this new version is at  least 10 times faster (dependent on the number of changed items). Now, that I can parse the data into an array BEFORE iterating and creating the final string, I can filter out the data for only the 'true' items and only have to create my final output (string, HTML, etc) from the filtered list. MUCH faster.

 

 

Details:

The For Split the Object Data into an Array and Cleanup Extra Characters action is unchanged. However, after this point I instead use the SELECT function to create an array.

Then, I use PARSE JSON to turn it back into a set of objects I can work with, filter, etc

lordneeko_0-1628882962786.png

 

This gives me the solution I was hoping for with an array of objects that look like this

[
 { 
   "Field": "ID",
   "Value": "false"
 },
{
  "Field": "Title",
  "Value": "true"
},
{etc}
]
 
Now, I can easily work with the data, send it on to other datasets, and post it (formatted) within a notification.

 

View solution in original post

5 REPLIES 5
abm
Super User
Super User

Hi @lordneeko 

 

With the current JSON structure you can't convert to XML and use XPath query to find where attribute values are true. The only option you have to use parse JSON step to parse and check individual property is true or not.

 

Pass the JSON to Parse JSON Step.

 

Next Use the below expression to check every single propery is true or not.

 

body('Parse_JSON')?['ColumnHasChanged']?['Title']
 
Check the value is true then add the Property name in an array.
 
Thanks


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
lordneeko
Frequent Visitor

So that is my problem. I don't want to have to parse each property name (Also, the data is already in JSON format, so I don't need to ParseJSON. The original data format is already set like that

lordneeko_0-1628875212971.png


What I want to do is simply grab all of it and print it out in a nicer format than 

string(body('Get_changes_for_an_item_or_a_file_(properties_only)')?['ColumnHasChanged']) which is an ugly mess.
 
Also, I may not know what the property value's name is, since the owners of the list may have added a new field which is the "new or changed" item that I'm trying to report on.
 
Somehow, I need to convert
"ColumnHasChanged": {
"ID": false,
"Title": true,
etc
}
to an array like
[
 { 
   "Field": "ID",
   "Value": "false"
 },
{
  "Field": "Title",
  "Value": "true"
},
{etc}
]
 
But without having to know what the values of "field" are ahead of time.

Hi @lordneeko 

 

Like I mentioned earlier your current JSON format doesn't allow to convert to XML. Hence you can't use the xpath query which would have been ideal in this situation. 

 

Please see this thread which I answered few hours ago.

 

Solved: Re: Trouble with Parse JSON schema - Power Platform Community (microsoft.com)

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
lordneeko
Frequent Visitor

I'm providing my "solution"

This isn't pretty, and I hope someone can respond with a better way, but this is what I did

lordneeko_0-1628881499832.png

 

 

Details:

For Split the Object Data into an Array and Cleanup Extra Characters

split(replace(replace(replace(string(body('Get_changes_for_an_item_or_a_file_(properties_only)')?['ColumnHasChanged']), '{', ''),'}',''), '"', ''),',')

 

This gives me an array I can iterate on.

 

Then I use the condition compare split(item(), ':')[1] = "true"

 

then I append the following to my output String (variable initialized before the loops)

concat(split(item(), ':')[0], ',', split(item(), ':')[1])

 

this ends up with a string

 

Title, true

Modified, true

etc, true

 

 

I may do a little more so I can shove this into an HTML table for printing to my MS Teams message, but for now, this is where I'm at.

lordneeko
Frequent Visitor

Ok I'm updating this again as I've gotten smarter.  I was actually able to meet my original goal of turning the Object, into an array which I can iterate over and work with (and use to push the data to other sources).  The original 'solution' I submitted was VERY slow and this new version is at  least 10 times faster (dependent on the number of changed items). Now, that I can parse the data into an array BEFORE iterating and creating the final string, I can filter out the data for only the 'true' items and only have to create my final output (string, HTML, etc) from the filtered list. MUCH faster.

 

 

Details:

The For Split the Object Data into an Array and Cleanup Extra Characters action is unchanged. However, after this point I instead use the SELECT function to create an array.

Then, I use PARSE JSON to turn it back into a set of objects I can work with, filter, etc

lordneeko_0-1628882962786.png

 

This gives me the solution I was hoping for with an array of objects that look like this

[
 { 
   "Field": "ID",
   "Value": "false"
 },
{
  "Field": "Title",
  "Value": "true"
},
{etc}
]
 
Now, I can easily work with the data, send it on to other datasets, and post it (formatted) within a notification.

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,069)