Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Large collection

(0) ShareShare
ReportReport
Posted on by 11
 I have a data source table with more than 4000 entries.
From this I need to create a collection to be used in a dropdown box. This will be filtered by another dropdown box. Both dropdowns will get their values from a single field in the data source table which has three information with delimiter "||" between them.
Construction of a field 'SalesPersonKey' value: "SalesPerson||CompanyName||SourceSystem"
The dropdowns will be:
1: Company Name
2: Salesperson
 
I do not know if one collection is the best solution,  but I need to be able to create the two dropdowns, where the first filter the second.
The code needs to take into account that new companies can be added, and salespersons are added at a regular basis in source systems.
 
 
'Dim.SalesPerson' is a SQL-table, that is a source in the Power App. I cannot change the columns in this table, it is as it is.
Until now, this code has been used:
Concurrent(
    ClearCollect(
        ColSalesKey1,
        Sort('Dim.SalesPerson',SalesPersonCode, SortOrder.Ascending)
    ),
    ClearCollect(
        ColSalesKey2,
        Sort('Dim.SalesPerson',SalesPersonCode,SortOrder.Descending)
    )


);
ClearCollect(
    ColSales,
    ColSalesKey1,
        Filter(ColSalesKey2, Not(SalesPersonCode in 'Dim.SalesPerson'.SalesPersonCode))

);
Clear(ColSalesKey1);
Clear(ColSalesKey2);

ClearCollect( colSLSSplit,
    ({SalesPersonCode: "All"}), 
        AddColumns( ColSales, 
            SalesPerson, Index(Split(SalesPersonKey,"||"),1).Value, 
            Company, Index(Split(SalesPersonKey,"||"),2).Value)

);
 
But it doesn't work anymore, as the entry count is beyond 4000, so we are missing entries in the middle.
 
Based on a lot of reading, I have tried this code:
Clear(Persons);
ForAll(
        ClearCollect(CompanyList, Distinct(
        ClearCollect(
            col_PersonCode,
            AddColumns(
                'Dim.SalesPerson',
                SalesPerson, Index(Split(SalesPersonKey,"||"),1).Value, 
                Company, Index(Split(SalesPersonKey,"||"),2).Value
            )
        ), Company)),
        Collect(Persons, Filter(col_PersonCode, Index(Split(SalesPersonKey,"||"),2).Value=Value)
                )
)
 
But it only collects 2000 entries.
 
Any suggestions on how to create the correct entries for the two dropdowns?
Categories:
  • ATG_DLF Profile Picture
    ATG_DLF 11 on at
    Large collection
    Thank you @WarrenBelz,
     
    This works perfectly to solve my need. Added a column to the collection that can be used as filter from the first dropdown.
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,666 on at
    Large collection
    Hi ATG_DLF 
    If you have a unique sequential numeric field, you can collect all records as per this blog of mine.
     
    Please click 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 giving it a Like.
    MVP (Business Applications)     Visit my blog Practical Power Apps    Buy me a coffee

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,666

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,996

Leaderboard