cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k_cain
Helper III
Helper III

Keep Data in Specific SharePoint Columns

I created a two separate flows to 1) bulk delete the data in my SharePoint list and 2) repopulate the list with updated data. The AccountExecutive column frequently changes names, so I run the flows to delete and refresh the list with the latest information. However, the last two columns in the list (FundingStatus and Comments) must remain the same and not change. Is there a way to add this in the flows? How can I delete the data and repopulate it without losing the information already inputted in the FundingStatus and Comments columns?

 

k_cain_1-1645125466341.png

 

9 REPLIES 9
ScottShearer
Super User
Super User

@k_cain 

It would be a lot easier to simply update the items rather than doing a bulk delete and re-adding the items.  

One way to do the bulk delete would be to:

  • Loop through each item to be deleted
  • Ad an new item inside the loop - do it before it is deleted so you can still reference the data in the columns to be preserved
  • Delete the original item

That obviously is not a good way to proceed.  Just update the items that you will be deleting.  One advantage of doing it this way is that you'll have the version history (assuming that versioning is turned on).

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

Scott, thanks for responding. I like your idea of doing away with the bulk delete. The only thing is I am a Power Automate newbie and am not sure I know how to only perform the update function. Are you saying that I can set a flow to update specific SharePoint columns? Will I need a unique ID for this?

@k_cain 

Where and how do you have the updated data stored?  Also, what is the link between the updated ata and the data to be updated?  Is there a common column?

Let me know and I'll provide an example.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

The updated data is stored in an Excel spreadsheet that gets refreshed every day. The Excel spreadsheet contains all SharePoint list columns, with the exception of the FundingStatus and Comments columns. The only columns that will be changed by the users are FundingStatus and Comments. The ContractNumberBackEnd column is the unique identifier for each different entry. Here's what the Excel spreadsheet looks like:

 

k_cain_0-1645131090206.png

 

@k_cain 

Will post an example this evening.....

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

@k_cain 

A couple of more questions...

How man items in the SharePoint list (approximate)?

Do all of the items in the Excel spreadsheet have a related item in the SharePoint list?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

There are about 4,000 items in the SharePoint list. The Excel spreadsheet items are the exact same records in the SharePoint list; however, the Excel spreadsheet will be refreshed once a week and may have different data in the columns for AccountExecutive, ContractStatus, and ExpirationDate. My goal is to make sure the columns the user updates do not get wiped out when I update the data.

@k_cain 

Please see my example below.  I have a common column called UniqueCol and I am updating the Title and Update1 Columns in SharePoint - the other columns remain the same.  In my filter array action, I reference the output of the select.  In the Update item action, the ID that I reference is from the output of the Parse JSON action.

Finally, you won't be able to select your unique column directly from dynamic properties when configuring the Filter array action.  You'll need to enter to following in the expression window:

item()?['ContractNumberBackEnd ']

 

 

image.pngimage.pngimage.pngimage.pngimage.png

 

When creating the Flow, stop after the Filter array action and run the Flow.  In the run, go to the Parse JSON action, click on show raw inputs and copy the text to your clipboard.

image.png

After that, add the Parse JSON action, click on Generate from sample and paste in the text from your clipboard.

image.pngimage.png

Then continue creating your Flow.

Let me know if you have any issues.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

@k_cain 

One more thing....

SharePoint will return only 100 items by default from a Get items action. Excel will only return 256 rows by default.

For both the Excel action and the Get items action, go to settings for the action, turn on pagination and set the threshold to 5000

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (2,983)