Delete Sharepoint Item using Parse JSON string

[Leu0101]

I have a SharePoint list created from an email using a CSV file. The CSV file is picked up by power automate and run thru Parse JSON and creates the SharePoint item. I'm having an issue with how to delete a SharePoint list item that is not on my CVS file/JSON Parse so I can delete it from the SharePoint list and make sure it matches up with my CSV file. Any guidance would be greatly appreciated! 

[Sundeep_Malik]

Hey @Leu0101 

Can you give an example what you are trying to do?

Like replace the values you don't want to show from that csv.

For example Replace a name with Abc, id with 123 etc. 

[Leu0101]

What I'm trying to do is, send power automate a CSV file with a unique column called "Unit ID". When I run the CSV report it creates my SharePoint items accordingly. However, let's say the next day I run the report again and two items fall off that report. I want to tell power automate to compare using the Parse JSON / "Unit ID" from the CSV and compare it to SharePoint's list of item(s) from the following day. If the Unit ID from the SharePoint list does not show up on the newly run CSV parse JSON "Unit ID", I want to remove the SharePoint list item.

[Leu0101]

Basically, I want to delete the SharePoint list item that currently sitting in my SharePoint list if it doesn't show up on my CSV report using the unique "Unit ID" column.

Here is my sharepoint list called winterization:

Let's say I ran my CSV report, and Unit ID "9999" was not on my report. I want to delete it from the Winterization SharePoint list.

[Sundeep_Malik]

@Leu0101 

Make two arrays.

One would be consisting of the new values.

One would be consisting of old values.

Then compare the old values one by one to the new values.

If a match is found. Keep it otherwise delete it.

[Leu0101]

I've tried many different ways for literally the past 12 hours. Could you show me a mock-up in power automate on how I would do that Sundeep_Malik?

[Leu0101]

😓No Joke. It would be greatly appreciated!

[Sundeep_Malik · Accepted solution]

@Leu0101 

I tried one thing. Check if this helps.

Took a flag variable, initialized it to 0.

Then took a compose of unit ids. In your case the data you are getting from csv.

Then used get items.

Then the flow after wards.

Took an apply to each and entered the value of SP List.

Then apply to each 2 passed the above compose output in that.

Inside that used a condition. Matching id from SP List to the compose.

If they are equal setting the value of flag variable to 1.

If not leaving the if no empty.

Outside the Apply to each 2 and inside the apply to each 1.

Condition to check if flag variable is still 0, then delete the item.

After that, setting the flag value again to 0.

So, basically what I am trying to do here is, if a value is found, I am setting flag value as 1  and if it is equal to 1, then I am not deleting the value from SP List.

If the flag value is 0, then the value is not found, and deleting it from SP List.

[Sundeep_Malik]

@Leu0101 

My SP List looked like this:

After running the flow:

[Leu0101]

I haven't been able to get it to work within the code as of yet. Does this look correct to you?

[OliverR-82]

I'm just thinking out of the box here, and I don't know all of your requirements so this may not be an option to you. But, it sounds like you just want the SP list to reflect what's in the CSV file, right?

Is it an option to just clear the entire list each time before you import the csv again? How many list items are we talking about? If it's a fairly small number, this approach would probably be the easiest. You would have an up-to-date SP list. Of course, if any other interactions need to take place with the SP items, then this way is not an option.

[Leu0101]

Thanks for the response Oliver-82. You are correct in that I do want it to reflect the CSV. You are also correct that I'd like to keep the files in SharePoint because they hold notes and detailed information so clearing at the beginning wouldn't be an option. If it falls off the CVS file it should fall off the SharePoint list. 

[Sundeep_Malik]

@Leu0101 

Everything seems correct.

Make sure the data types match. I also tried it before, and my data types were not matching.

Btw @OliverR-82 nice technique. @Leu0101  you can try it.

Just I think, maybe he is just getting the ids from there so deleting the entire contents of the SP List wont be an option. Because there will be other columns as well.

If not then deleting everything would be the best/easiest choice.

[OliverR-82]

It seems like it just comes down to comparing two arrays, though? Compare the data from the .csv file (which is an array) with the list items from your list. Maybe this blog post by Pieter Veenstra is of help?

[Leu0101]

@Sundeep_Malik Your solution did work perfectly! I had come to realize the get items limits to 100 by default. My list had 114 and was skipping 14 of them. After I changed the default limit it worked perfectly. Thank you for your response. It helped a great deal in helping me understand arrays. I appreciate it sincerely!