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?
