Finding Changes between new and old version of JSON file

Like (0) Share

Report

Posted on 2 Aug 2024 22:09:56 by BP-02082141-0

I am trying to develop a flow which will detect changes in Planner tasks since the OOB connectors for Planner are so limited. So far I am able to move the old JSON file into a subdirectory, export all current planner tasks and their data to a JSON file in the original location, then effectively import both the old and the new JSON files back into the flow.

What I'm having a hell of time figuring out is how to read each task detailed in the new JSON file and use the task ID to locate the matching task in the old file and determine which values of these 2 keys are different, using a condition function to take specific action(s).

I have quite a bit of scripting experience in batch and power shell, but my background is not in programming, and JSON is very new to me so I may not be using correct terms or approaches here. I am certainly open to suggestions on better ways to accomplish this task. For example, I considered using SharePoint list items for each task instead and then comparing them that way. I'm still not against that, but I will need a way to ensure closed or deleted Planner tasks are also removed from the SharePoint list(s).

For right now, I'm giving up for the weekend and will attempt to tackle this again on Monday. In the mean time, can anyone provide some guidance?

Thanks in advance!

Categories:

Process Advisor

All responses (6)

Answers (0)

BP-02082141-0 6 on 13 Aug 2024 at 18:03:05

Like (0)

Report
Finding Changes between new and old version of JSON file

Thanks for the response...

I have been able to work through this process where my flow is able to find changes between each task's sub-values using a conditional statement for each value. While I feel I effectively achieved a functional flow on my own, I will acknowledge that the basic ideas provided by FLMike originally are effectively in use, so if you're reading this because you're looking for similar answers to what I was, make sure to review his response.

Unfortunately, last night I got an email saying my flow is running too many actions. This morning, I finished adding in the last of the conditions for each value set, and ran a test. A single run took 8.23 minutes and more than 4000 actions against a total Planner task count of 68 tasks. According to that email I have a "Power Platform Requests transition limit" of 10,000. The best part is that I haven't added any actual tasks to the condition statements yet!

So I've determined a few things that are likely all true, either individually or simultaneously:

I don't really know what I'm doing and know just enough to think I do.

There's a more efficient way to accomplish my goals within my flow(s)

Power Automate may not be the best option for what I'm doing.

That being said, I do have some nested "For each" or "Apply to each" statements that really jack up the action count from what I've read. I took a quick look at the information provided by takolota1 and my eyes immediately glazed over, however, giving where I'm at right now, I'll dive into that determine how much of that might apply here. I'll update later once I have a better idea for my path forward.

In the meantime, if anyone has suggestions on better solutions, let me know. lbendlin below mentioned "git" which I'm not familiar with, but I'm willing to entertain just about anything with a reasonable cost.
takolota1 4,898 Moderator on 12 Aug 2024 at 21:48:17

Like (0)

Report

Finding Changes between new and old version of JSON file

You can also accomplish this without apply to each loops by using an intersect( ) to get the common records between the old & new files, then using a Select to get an array of the primary keys of those common records, & then a Filter array on the New records where the Select primary keys array does not contain the primary key of each new record.

I use such a set up in this template:

https://community.powerplatform.com/galleries/gallery-posts/?postid=e1e47e8f-9bfd-4d9c-9b43-68ced3a6cbfb
BP-02082141-0 6 on 12 Aug 2024 at 13:26:12

Like (0)

Report

Finding Changes between new and old version of JSON file

Maybe so, but I'm trying to keep the whole process running through cloud-based solutions provided as part of O365.
lbendlin 8,081 Super User 2025 Season 2 on 09 Aug 2024 at 21:04:33

Like (0)

Report

Finding Changes between new and old version of JSON file

Isn't that what version control systems like git are for ?
BP-02082141-0 6 on 09 Aug 2024 at 21:00:55

Like (0)

Report
Finding Changes between new and old version of JSON file

Thank you for the response, and apologies for the delay.

I'll attempt to address your points individually in a way that hopefully is not confusing as I'm new to this site and not entirely up to speed with formatting preferences for some of the information I'm providing.

First thing, we need to see an example of the JSON. After that its sort of easy.

Here's a shortened and anonymized version of the current JSON file:

[ { "taskId": "-kkKIz_F-063b2igsrRCxGUAH1HD", "taskName": "Dr. M (2407291804SVC)", "taskDescription": "Op 6. xray issue", "taskProgress": 0, "taskStart": "2024-07-29T18:04:11Z", "taskDue": null, "taskComplete": null, "assignees": [ { "assigneeID": "f49af69e-e2d8-454d-829f-08a87d3e0ee3", "assigneeName": "John Doe" } ], "subtasks": [ { "id": "1", "value": { "@odata.type": "#microsoft.graph.plannerChecklistItem", "isChecked": false, "title": "Quote Provided", "orderHint": "8584789905801584695", "lastModifiedDateTime": "2024-08-02T16:31:45.3191112Z", "lastModifiedBy": { "user": { "displayName": null, "id": "2a8c7f11-e9c8-4e45-94e5-cd83db8ea44f" } } } }, { "id": "2", "value": { "@odata.type": "#microsoft.graph.plannerChecklistItem", "isChecked": false, "title": "Order Placed", "orderHint": "8584789905801584695", "lastModifiedDateTime": "2024-08-02T16:31:45.3191112Z", "lastModifiedBy": { "user": { "displayName": null, "id": "2a8c7f11-e9c8-4e45-94e5-cd83db8ea44f" } } } } ], "buckets": [ { "bucketID": "UFMm6WNGLUOT57O4uDnQrWUAFSz1", "bucketName": "Testing Automation" } ], "Sterilization": null, "Multi-Op": null, "Remodel": null, "3D": null, "Single Op": null, "New Space": null, "Exist EQ": null, "2D": true, "Mechanical": null, "Scanner": null, "Sensor": null }, { "taskId": "gTDy2NYDKk2qjlUkarmqBmUALybC", "taskName": "Dr B", "taskDescription": "chair/unit combo", "taskProgress": 0, "taskStart": null, "taskDue": null, "taskComplete": null, "assignees": [], "subtasks": [], "buckets": [ { "bucketID": "brod_HA2Z0uqLXSwzELkEmUAHsV3", "bucketName": "Prospects" } ], "Sterilization": null, "Multi-Op": null, "Remodel": null, "3D": null, "Single Op": true, "New Space": null, "Exist EQ": null, "2D": null, "Mechanical": null, "Scanner": null, "Sensor": null }, { "taskId": "HyMcKXIj-0SKq4D4XQnVnmUAGD9O", "taskName": "Dr L", "taskDescription": "12/18 - 2d pan; possible 2024 close.", "taskProgress": 0, "taskStart": null, "taskDue": null, "taskComplete": null, "assignees": [], "subtasks": [], "buckets": [ { "bucketID": "brod_HA2Z0uqLXSwzELkEmUAHsV3", "bucketName": "Prospects" } ], "Sterilization": null, "Multi-Op": null, "Remodel": null, "3D": null, "Single Op": null, "New Space": true, "Exist EQ": null, "2D": true, "Mechanical": null, "Scanner": null, "Sensor": null }, ]

And here's an "older" version of the same file with some tweaks similar to the changes I'm trying to detect:

[ { "taskId": "HyMcKXIj-0SKq4D4XQnVnmUAGD9O", "taskName": "Dr Z", "taskDescription": "12/18 - 2d pan; possible 2024 close.", "taskProgress": 0, "taskStart": null, "taskDue": null, "taskComplete": null, "assignees": [], "subtasks": [], "buckets": [ { "bucketID": "UFMm6WNGLUOT57O4uDnQrWUAFSz1", "bucketName": "Testing Automation" } ], "Sterilization": null, "Multi-Op": true, "Remodel": null, "3D": null, "Single Op": null, "New Space": true, "Exist EQ": null, "2D": true, "Mechanical": null, "Scanner": null, "Sensor": null }, { "taskId": "-kkKIz_F-063b2igsrRCxGUAH1HD", "taskName": "Dr. M (2407291804SVC)", "taskDescription": "Op 6. xray issue", "taskProgress": 0, "taskStart": "2024-07-29T18:04:11Z", "taskDue": null, "taskComplete": null, "assignees": [ { "assigneeID": "f49af69e-e2d8-454d-829f-08a87d3e0ee3", "assigneeName": "John Doe" } ], "subtasks": [ { "id": "1", "value": { "@odata.type": "#microsoft.graph.plannerChecklistItem", "isChecked": true, "title": "Quote Provided", "orderHint": "8584789905801584695", "lastModifiedDateTime": "2024-08-02T16:31:45.3191112Z", "lastModifiedBy": { "user": { "displayName": null, "id": "2a8c7f11-e9c8-4e45-94e5-cd83db8ea44f" } } } }, { "id": "2", "value": { "@odata.type": "#microsoft.graph.plannerChecklistItem", "isChecked": true, "title": "Order Placed", "orderHint": "8584789905801584695", "lastModifiedDateTime": "2024-08-02T16:31:45.3191112Z", "lastModifiedBy": { "user": { "displayName": null, "id": "2a8c7f11-e9c8-4e45-94e5-cd83db8ea44f" } } } } ], "buckets": [ { "bucketID": "brod_HA2Z0uqLXSwzELkEmUAHsV3", "bucketName": "Prospects" } ], "Sterilization": null, "Multi-Op": null, "Remodel": null, "3D": null, "Single Op": null, "New Space": null, "Exist EQ": null, "2D": true, "Mechanical": null, "Scanner": null, "Sensor": null }, { "taskId": "gTDy2NYDKk2qjlUkarmqBmUALybC", "taskName": "Dr B", "taskDescription": "chair/unit combo", "taskProgress": 0, "taskStart": null, "taskDue": null, "taskComplete": null, "assignees": [], "subtasks": [], "buckets": [ { "bucketID": "brod_HA2Z0uqLXSwzELkEmUAHsV3", "bucketName": "Prospects" } ], "Sterilization": null, "Multi-Op": null, "Remodel": null, "3D": null, "Single Op": true, "New Space": null, "Exist EQ": null, "2D": true, "Mechanical": null, "Scanner": null, "Sensor": null }, ]

not sure what you mean by import them in the Flow, are you getting Get Content or something?

I"m using a "Get file content using path" step in the flow, then "Compose" the data as JSON, and finally "Parse JSON" to theoretically allow me to pull fields into future steps as variables/data.

Then once you load them both, you can use an Apply to Each, to loop through the Original Files JSON

This is where I get lost in how to get this going. For starters, the number of tasks exported into this JSON file can vary wildly, so I know I'll need an Apply to Each step to cycle through each of the records from the 1st JSON file, effectively set the value of each key/value for the object as an individual variable, then locate the record with the same taskId in the 2nd JSON file, set the value of each key/value in for the matching record in that file as different variables, compare each set of variables and take specific actions based on what changed for that key/value.

For example, in the first file above, you can see task ID "HyMcKXIj-0SKq4D4XQnVnmUAGD9O" is the 3rd record, and one of it's key/values is "bucketname": "Prospects". I need to be able to catch the fact that this taskID is the 1st record in the second file, and the key/value mentioned has changed to "bucketname": "Testing Automation"

I'll get my hands back into this and see if I can figure it out based on your previous response, but if you can offer further guidance, it would be greatly appreciated. If it helps, I'm willing to share my Flow steps, or anything else for clarity as well.

Thanks again!
Suggested answer

Michael E. Gernaey 45,250 Super User 2025 Season 2 on 03 Aug 2024 at 04:38:54

Like (0)

Report

Finding Changes between new and old version of JSON file

Hi

Thanks for posting.

First thing, we need to see an example of the JSON. After that its sort of easy.

Once you have them both back imported, we would need to use a Filter Action and write an advanced Filter to find the single ID you want.

So Imagine this

1. You would (not sure what you mean by import them in the Flow, are you getting Get Content or something?

2. Either way, you would use a ParseJSon action on both of them so that you had something you can easily filter/ etc on

3. Then once you load them both, you can use an Apply to Each, to loop through the Original Files JSON

3a. Inside the Apply to Each, you would read that records ID (not without seeing a sample of the JSON I cannot write it for you here)

3b. You would then use a Filter Action, to filter the New JSON, looking for that ID, that you have from the apply to Each. You would

simply write an Advanced Filter Function (not hard), but again I cannot show the real one without the JSON Sample

the output of the Filter, would be the proper planner task from JSON (B)

Now... I don't know for sure what you are trying to do exactly but you have a couple of options

A) turn the value of the Current Loops Record/JSON Item into a string

B) turn the Original JSON Item into a string

Compare them. If they aren't exactly the same you know its changed.

or C) Look at the Modified DateTime stamps

Community site session details

Finding Changes between new and old version of JSON file

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Leaderboard > Power Automate

Featured topics

Product updates

Community site session details

Finding Changes between new and old version of JSON file

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate

Featured topics

Product updates