Count rows in sharepoint list

[CMSGuy]

I have a process where I need to delete ALL records out of a SharePoint list and then it will recreate records from a new daily csv file.  I have the Do Until loop pulling 5000 records and going until it reaches a hardcoded limit of 80,000.  I would rather like to find out how many current records there are, store that into a variable and use it so then it will be cleaner.  Is there an easy way to either delete all records (like a simple truncate table statement is in SQL), or is there an easy way to get current record count?

Thanks for the information

[Sundeep_Malik · Accepted solution]

Hey @CMSGuy 

So, to count the number of items, you can use the following expression in a compose action:

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

As far as I understood your scenario, you are using do until till you reach your last item.

So, what you can do is first count the number of items coming from get items, by the above expression, then just use increment variable to increase the variables count by everytime the number of items coming in get items.

So, lets say there are 12000 items, the loop will run, give you 5000, you increment the variable with that 5000, next time loop runs, again 5000 will be added to the variable, and for the last time 2000 items will be there, then it will just increment the 2000, so in total 12000 items. Below adding screenshot for what you can do, as far as I understood the scenario.

Below 1st taking count of get items, then incrementing the variables value with the count of get items.

If this was the solution you were looking for, you can mark this as a solution and if you liked the explanation, you can give a thumbs up. 🙂

[grantjenkins]

My question is WHY are you deleting then adding approx. 80,000+ rows of data into a SharePoint list daily. Just wondering what the purpose is and whether there might be a better solution for you.

[CMSGuy]

If there is a better way, then I am open to learning.  We have a table of employees working on certifications.  Each certification has different learning path qualifications.  On a daily basis, an employee might or might not complete one of those qualifications so the data is variable on a day-to-day basis.  Is there a better way to lookup a record and update it if it changed?  Or if the record does not exist, create a new?

[grantjenkins]

Sorry for the late reply. I guess I meant what's the main purpose of having it in SharePoint. It seems like a lot of data to store in a SharePoint List.

Could you just have an Excel file/table that contains the data stored in a SharePoint Library. Then each day just replace the Excel file with the new one. If you're reporting on it, then you could still drive the report directly from the Excel file. Maybe not an option, but worth mentioning.

[josefm2]

Hi,

Wondering what steps are involved before Do Until? Trying to replicate but I still get a top record of 5k. Thanks.