Announcements
Hi All,
I have a sharepoint list of part numbers(3000+ records).
Currently, I have a combo box, which allows me to search for a particular part. I have the formula below
SortByColumns(Distinct('Tbl','Part No'),"Value")
However, this only looks at the first 2000 rows, is there a way to get over this so it will search all 3000+ records?
Thanks in Advance
Hi @Drrickryp
Thank you for the reply.
I am getting an error on the highlighted section of the formula
With({a: SortByColumns(Distinct('Tbl','Part No'),"Value"),
b: Distinct(SortByColumns('Tbl','Part No'),"Value". SortOrder. Decending))}, Filter(b, Not(Value in a))
)
Thanks for the reply.
When entering the formula on "On Visible" for the screen, it comes up with an error
With({a: SortByColumns(Distinct('Tbl','Part No'),"Value"),
b: Distinct(SortByColumns('Tbl','Part No'),"Value". SortOrder. Decending))}, Filter(b, Not(Value in a))
)
In Power Apps, there is a limitation on the number of records that can be retrieved from a SharePoint list using the Distinct function. By default, the Distinct function retrieves a maximum of 2000 records.
To work around this limitation and retrieve all the distinct part numbers from your SharePoint list (with 3000+ records), you can use a combination of functions like Collect and GroupBy.
Here's an example of how you can modify your formula to overcome the limitation:
Add a collection to your app to store all the distinct part numbers. You can do this in the OnVisible property of the screen or another appropriate event. For example:
Share
Report
All responses (
Answers (ClearCollect(MyPartNumbers, GroupBy('Tbl', "Part No", "PartNumber"))
This Collect function with the GroupBy function will collect all the distinct part numbers from the 'Tbl' SharePoint list into the MyPartNumbers collection.
Set the Items property of your combo box to the MyPartNumbers collection:
Items = MyPartNumbers
This will populate the combo box with all the distinct part numbers from the collection.
Note: Make sure to replace 'Tbl' with the actual name of your SharePoint list in the formula.
By using the Collect and GroupBy functions, you can bypass the limitation on the number of records retrieved by the Distinct function. This approach will allow you to search through all 3000+ part numbers in your SharePoint list.
of you can simple check this blog of mine to retrieve more than 2000 rows from sharepoint:
https://shaheer365.blogspot.com/2023/05/retrieving-more-than-2000-rows-from.html
#1
WarrenBelz
146,508
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,369
Most Valuable Professional
