cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StephenGW
Helper IV
Helper IV

SharePoint List by row count

All,

 

Looking for some help. I have a couple lists that are connected to a Power App. Problem is Power Apps don't play well with lists longer than 2000 rows. I'm trying to make a flow that will move items from one list to an Archive list when the total rows reach a specific count. Ideally I would like for when the row count reaches 1751 it moves the oldest row to the Archive to always keep the row count at 1750. If that is not possible I could work with something like when the row count reaches 1800 it moves the oldest 800 rows to the new list. All data would be moved, so copied to the new list at the last empty row so nothing is overwritten, and then deleted from the original list.

 

Is this possible? How would I go about writing this?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @StephenGW 

I guess maybe you set variable went wrong, lead to ID is empty. I found an easier way you can take a try.

Because Get items ID is sorted from smallest to largest by default , so when the number of items exceeds the specified number, you only need to get the first ID, then get the item and then move it to another list.

Please take a try with following steps.

vLilyWmsft_0-1627006110415.png
vLilyWmsft_1-1627006110418.png

Expression: length(outputs('Get_items')?['body/value'])

vLilyWmsft_2-1627006110420.png

Expression:first(body('Get_items')?['Value'])?['ID']

vLilyWmsft_3-1627006110421.png

vLilyWmsft_4-1627006110426.png

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
v-LilyW-msft
Microsoft
Microsoft

Hi @StephenGW 

According to your description, you want to keep up to 1700 items in the list.

I set up a flow, When the number of my items is greater than or equal to 5, will move the oldest item to another list.

You can take a try with the screenshots as below:

This is my test list, backup list has the same column.

vLilyWmsft_17-1626943689266.png

vLilyWmsft_0-1626943245117.png

vLilyWmsft_1-1626943245121.png

vLilyWmsft_2-1626943245124.png

Expression: length(outputs('Get_items')?['body/value'])

vLilyWmsft_3-1626943245126.png

vLilyWmsft_4-1626943245127.png

Expression: int(first(body('Select'))?['ID'])

vLilyWmsft_5-1626943245129.png

vLilyWmsft_6-1626943245133.png

vLilyWmsft_7-1626943245135.png

vLilyWmsft_8-1626943245136.png

vLilyWmsft_9-1626943245138.png

vLilyWmsft_10-1626943245139.png

This is my whole Flow:

vLilyWmsft_15-1626943496532.png

The details in Condition2 is as below:

vLilyWmsft_16-1626943550115.png

When I created the new item.

vLilyWmsft_12-1626943245145.png

The top item will be moved to the backup list.

vLilyWmsft_13-1626943245146.png

vLilyWmsft_14-1626943245148.png

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-LilyW-msft 

 

Looks promising. I will test it out later today and report back.

 

Thank you!

@v-LilyW-msft 

 

It was looking good but it failed. It gave me the error seen below. I wonder if this has to do with the fact the items are already in the list? I set it up to do 1420 as the limit since I have 1419 items already in the list. Is this the problem?

 

StephenGW_0-1626959789997.png

 

Thanks

Hi @StephenGW 

I guess maybe you set variable went wrong, lead to ID is empty. I found an easier way you can take a try.

Because Get items ID is sorted from smallest to largest by default , so when the number of items exceeds the specified number, you only need to get the first ID, then get the item and then move it to another list.

Please take a try with following steps.

vLilyWmsft_0-1627006110415.png
vLilyWmsft_1-1627006110418.png

Expression: length(outputs('Get_items')?['body/value'])

vLilyWmsft_2-1627006110420.png

Expression:first(body('Get_items')?['Value'])?['ID']

vLilyWmsft_3-1627006110421.png

vLilyWmsft_4-1627006110426.png

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-LilyW-msft 

 

I got another error. It says the error is temporary so I waited a little bit a tried again with the same results.

StephenGW_0-1627042432629.png

This seems pretty straight forward. Any ideas what I'm doing wrong? Is the delete item required? Until I felt it was running well I didn't want to put it in there. Would that cause the problem?

Hi @StephenGW 

Could you please provide the screenshots of your Flow configuration?

Please check whether the Outputs of Compose has a value in your running result.

And please make sure your Expression is entered correctly.

vLilyWmsft_0-1627261227907.png

Best Regards.

 

@v-LilyW-msft 

 

It looks like maybe the condition isn't working? It's outputting false. I adjusted it down to 1000 items and my lilst has 1420. 

StephenGW_0-1627303201364.png

StephenGW_1-1627303246981.png

StephenGW_2-1627303301962.png

So it's not even making it to the Compose.

 

Hi @StephenGW 

I seem to know where the problem lies. Get items returns 100 records by default, and the number of your items is more then 100, so the result of your Condition is false. Please increase this limit up to 2,000(The maximum limit is 5000 ) which exceeds the number of items in your list as below:

vLilyWmsft_0-1627354193290.png

vLilyWmsft_1-1627354229879.png

Then save the flow and test again.

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-LilyW-msft 

 

Perfect, that was the problem and it works great now! Thank you so much for your continued help! 

StephenGW
Helper IV
Helper IV

@v-LilyW-msft 

 

So I discovered a problem. When multiple items are created while the flow is running this causes it to fail and then not send those items to the archive list.

 

Would there be a way to have a flow run every morning on a recurrence and move the oldest items to get the row count below the set number? So every morning it runs the flow. Finds how many items there are over 1800 and moves the oldest items to the archive list? so the number of items ends back up at 1800 then this repeats every morning?

 

What would this look like?

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!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,420)