Query SharePoint metadata and show it in Power Apps

(1) Share

Report

Posted on by Palaces

Hello everyone,

I have an application that a vendor built for a company and there's a field that stopped showing accurate data at 500 records firstly, then got stuck in 2000 when increased the limit in power apps from 500 to 2000. I'm very aware of delegation limits in Power Apps and SharePoint, but I'm wondering if there's any way we could query the metadata in SharePoint to reflect the total number of rows in a list that is over 20k rows, please see image below. That image is from the 'Site Content' section in SharePoint, so, I'm wondering if there's a way to query that number in the Items column for the Checklist list, and show/reflect this number in a label/text control in Power Apps.

Thank you very much for your help in advance.

Regards :)

Categories:

Connectors

Using Apps Using Formulas

Connecting to Data

I have the same question (0)

All responses (15)

Answers (0)

WarrenBelz 150,367 Most Valuable Professional on at

Like (0)

Report
Query SharePoint metadata and show it in Power Apps

Hi @Palaces,

This may work for you.
api/web/lists/GetByTitle('Checklist')?$select=ItemCount&$filter=Status eq 'Pending'

You would need to send the Status as a Parameter from Power Apps.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful?
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Palaces 45 on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

Thank you, @WarrenBelz!

I was referring to do this filter with the column 'Status' using the Sent HTTP Request to SharePoint, something like this: _api/web/lists/GetByTitle('Checklist')?$select=ItemCount?$Status="Pending" . Maybe the Uri I'm showing doesn't make sense, but that's what I'm asking, if it's possible to filter using the HTTP Request? If it's possible then I could insert an HTTP Request per each status in the same flow and then use the 'Respond to Power Apps' action to present the data in Power Apps.

Regards! :)

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 150,367 Most Valuable Professional on at

Like (0)

Report
Query SharePoint metadata and show it in Power Apps

Hi @Palaces,

You can do one of two things here - if the count of the Filtered list is less than 2,000, you can do it all in Power Apps. You did not say what type of column it was, so I will assume a Single Line of Text. If it is a Choice column, you need Status.Value in Power Apps and Status/Value in the Flow below. So Power Apps for up to 2,000 records after filter.
With( { _Data: Filter( SPList, Status = "Current" ) }, CountRows(_Data) )

Using a Flow

ensure you set the Pagination to at least the size of your List

then send back the total count

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful?
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Palaces 45 on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

Hi @WarrenBelz, after evaluating the options I think I'll move forward with the flows option. Have you ever filtered the ItemCount based on the value of one of columns in the list?, i.e, I have a Column 'Status', and based on the 'Status' (Complete, Pending, or Cancelled) filter the items in the list and count them, then respond to Power Apps with this value.

Really appreciate your valuable help!

Was this reply helpful? Yes No
WarrenBelz 150,367 Most Valuable Professional on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

Hi @Palaces,

It is unfortunatley not possible directly from Power Apps. However, a workaround may be to run a timed Flow (daily/weekly or whatever) getting the number of records as before and the last ID, then subtracting and writing the result to a single record reference list, which can then be queried by the Power Apps code I supplied.

Was this reply helpful? Yes No
Palaces 45 on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

Thanks @WarrenBelz!

This is a good work around, however there are records that are deleted from time to time, and accuracy is needed. it's interesting we can make a http request from a flow but not from the app itself.

Was this reply helpful? Yes No
WarrenBelz 150,367 Most Valuable Professional on at

Like (0)

Report
Query SharePoint metadata and show it in Power Apps

Hi @Palaces,

If you are never going to delete records (and never have since the List was created), then the number of records is easy
First( Sort( SPListName, ID, SortOrder.Descending ) ).ID

This will present the newest 2,000 (or whatever your Data Row Limit is) records in descending order (sorting by ID is Delegable and you can also use Created ) and the ID of the first record is the newest and the total number of records.

If however you have deleted some in the past (I will use a random number 45 below), but will not in the future you can set a "line in the sand". Get the total present number of records directly in Site Contents in SharePoint, and subtract it from the newest ID and then you can do this

First( Sort( SPListName, ID, SortOrder.Descending ) ).ID - 45

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful?
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Palaces 45 on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

@WarrenBelz, @Arun K Rajan thank you very much for providing the solutions!

The downsize of using roles is that there are more than one field where the data needs to be updated, this means I'll need a flow per each field, apologies for not including this in my original post, I just realize I said one field. This is why I was trying to find a way to do this directly from Power Apps and not from flows. Also, the deleted records are not needed to track in the records count. If you can think of any way this can be accomplished directly from Power Apps that'd be great, if no, I can just create a flow per each field.

Thank you very much!

Was this reply helpful? Yes No
WarrenBelz 150,367 Most Valuable Professional on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

@Arun K Rajan,

That does not allow for deleted records.

Was this reply helpful? Yes No
Arun K Rajan 23 on at

Like (0)

Report

Query SharePoint metadata and show it in Power Apps

Hi,

The best approach will be to use the power automate like previously said, other work around will be if you have indexed column on your SharePoint list, Instead of SP ID we can use that Indexed field and add conduction this will avoid delegate issues .

Regards,

Arun

Was this reply helpful? Yes No