web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / The StartsWith part of...
Power Apps
Unanswered

The StartsWith part of this formula may not work correctly on large data sets.

(0) ShareShare
ReportReport
Posted on by Cr1t 555

Hi All, 

 

I have a delegation warning and i dont know how to fix it, please help. this used to work for small data sets and has stopped working when the dataset is increased. Really new to delegation warning.

 

If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 ClearCollect(
 SearchItemsCollection,
 Filter(
 'Data - Customers',
 StartsWith(
 CustomerName,
 Main_CompanyNameSearch_Input.Text
 ),
 Not(CompanyCode = "DMCC"),
 Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 1
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 2
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 3
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 4
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 5
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 6
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 7
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 8
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 9
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 10
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 11
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 12
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 13
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 14
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 15
 )
 ).Value || Territory = First(
 LastN(
 First('Access List').TerritoryAccess,
 16
 )
 ).Value
 )
 );
 If(
 IsEmpty(SearchItemsCollection),
 Set(
 NewSupplierVisVar,
 true
 )
 );
 Set(
 SearchResultsVisVar,
 true
 )
)

 

Please help thank you. 

@WarrenBelz @timl @Amik @iAm_ManCat 

Categories:
Building Power Apps
I have the same question (0)
  • WarrenBelz Profile Picture
    WarrenBelz 153,030 Most Valuable Professional on at

    HI @Cr1t ,

    Your initial issue is here

    Not(CompanyCode = "DMCC"),

    as this filter is not Delegable - you can firstly think about simply specifying all the values that it could equal. Also how big is 'Access List' ?

  • Cr1t Profile Picture
    Cr1t 555 on at

    @WarrenBelz 

    Thanks for your reply, Access List has only 16 records, its basically user email and the territory they can access. so there is only two columns. 

    What do you mean by simply specify could you explain me more? 

    Cr1t_0-1690875739806.png

     

    I tried this and still get the same error cant search anything more than 2000 records, 

     CompanyCode <> "DMCC",
 Territory In FirstN(
 'Access List'.TerritoryAccess,
 16
 ).Value
 )
 );

    Please help, thank you. 

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 153,030 Most Valuable Professional on at

    @Cr1t ,

    There is no real perfect solution here - you need to manage the non-delegable portions - I will give you a couple of structures. The best one depends on the number of records left after the StartsWith filter

    If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 With(
 {
 wData:
 UnGroup(
 ForAll(
 'Access List' As aList,
 Filter(
 'Data - Customers',
 StartsWith(
 CustomerName,
 Main_CompanyNameSearch_Input.Text
 ) && 
 Territory = aList.TerritoryAccess
 )
 ),
 "Value"
 )
 },
 ClearCollect(
 SearchItemsCollection,
 Filter(
 wData,
 CompanyCode <> "DMCC"
 )
 )
 )
)

    and this one

    If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 With(
 {
 wData:
 Filter(
 'Data - Customers',
 StartsWith(
 CustomerName,
 Main_CompanyNameSearch_Input.Text
 )
 )
 },
 ClearCollect(
 SearchItemsCollection,
 Filter(
 wData,
 CompanyCode <> "DMCC" &&
 Territory in 'Access List'.TerritoryAccess
 )
 )
 )
)

     

    Please click Accept as solution 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 Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

  • Cr1t Profile Picture
    Cr1t 555 on at

    @WarrenBelz 
    Thanks for taking your time and providing me this code, i used the first code provide got may errors like all the text labels where not able to find their respective column values. 

    Cr1t_1-1690886350002.pngCr1t_2-1690886364820.png

     



    the send code game me this error

    Cr1t_0-1690886311638.png

     

  • WarrenBelz Profile Picture
    WarrenBelz 153,030 Most Valuable Professional on at

    @Cr1t ,

    I would need to know which field is causing this as I relied on your posted data structure when putting this together. This however is a different issue to your posted question which was about Delegation on a Filter.

  • Cr1t Profile Picture
    Cr1t 555 on at

    @WarrenBelz 

    Sorry for the delayed response, i took time to figure out all the errors, i was able to fix most of them but now i have this error and 3 delegation warnings. The issue is with "Territory = aList.TerritoryAccess".

    TerritoryAccess is a Choice coulmn

    Cr1t_0-1690963659719.png

     

    If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 With(
 {
 wData: Ungroup(
 ForAll(
 'Access List' As aList,
 Filter(
 'Data - Customers',
 StartsWith(CustomerName, Main_CompanyNameSearch_Input.Text) &&
 Territory = aList.TerritoryAccess
 )
 ),
 "Value"
 )
 },
 ClearCollect(
 SearchItemsCollection,
 Filter(
 wData,
 CompanyCode <> "DMCC"
 )
 )
 );
 If(
 IsEmpty(SearchItemsCollection),
 Set(NewSupplierVisVar, true)
 );
 Set(SearchResultsVisVar, true)
)

     

    Cr1t_1-1690963690457.png

    Cr1t_2-1690963718217.png

     

     

  • Cr1t Profile Picture
    Cr1t 555 on at

    @WarrenBelz 

    I used the second code did some changes, was able to get rid of the delegation warning. but stuck with this now dont know how to fix it, please help.

    Cr1t_0-1690976365894.png

    If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 With(
 {
 wData: Filter(
 'Data - Customers',
 StartsWith(CustomerName, Main_CompanyNameSearch_Input.Text)
 )
 },
 ClearCollect(
 SearchItemsCollection,
 Filter(
 wData,
 CompanyCode <> "DMCC" &&
 Territory = FirstN('Access List'.TerritoryAccess, 16).Value
 )
 )
 );
 If(
 IsEmpty(SearchItemsCollection),
 Set(NewSupplierVisVar, true)
 );
 Set(SearchResultsVisVar, true)
)

    Cr1t_1-1690976403127.png

     

  • Cr1t Profile Picture
    Cr1t 555 on at

    @WarrenBelz 

    Thank you very much again, sorry agin for asking multiple question, i was able to get the code working with your solution as a base, i used the second code example and worked for me. this is the updated code i have used and there is no delegation warning and i was able to get the same functionality. 

    If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 With(
 {
 wTerritories: Choices('Access List'.TerritoryAccess).Value,
 wData: Filter(
 'Data - Customers',
 StartsWith(CustomerName, Main_CompanyNameSearch_Input.Text)
 )
 },
 ClearCollect(
 SearchItemsCollection,
 Filter(
 wData,
 CompanyCode <> "DMCC" &&
 Territory in wTerritories
 )
 )
 );
 If(
 IsEmpty(SearchItemsCollection),
 Set(NewSupplierVisVar, true)
 );
 Set(SearchResultsVisVar, true)
)

    Thank you very much again. 

  • WarrenBelz Profile Picture
    WarrenBelz 153,030 Most Valuable Professional on at

    @Cr1t ,

    Why are you using FirstN here - that will output a Table

     

    Please click Accept as solution 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 Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

  • Cr1t Profile Picture
    Cr1t 555 on at

    @WarrenBelz 

    I was doing a lot of silly mistakes and i was able to get it working with this code below, thanks for you help. without your base code i would not have achived anything. you are the best. 

    If(
 IsBlank(Main_CompanyNameSearch_Input.Text),
 Notify(
 "Error, the company name input has been left blank",
 NotificationType.Error
 ),
 With(
 {
 wTerritories: Choices('Access List'.TerritoryAccess).Value,
 wData: Filter(
 'Data - Customers',
 StartsWith(CustomerName, Main_CompanyNameSearch_Input.Text)
 )
 },
 ClearCollect(
 SearchItemsCollection,
 Filter(
 wData,
 CompanyCode <> "DMCC" &&
 Territory in wTerritories
 )
 )
 );
 If(
 IsEmpty(SearchItemsCollection),
 Set(NewSupplierVisVar, true)
 );
 Set(SearchResultsVisVar, true)
)

     

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

News and Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 796 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 327 Super User 2025 Season 2

#3
Power Platform 1919 Profile Picture

Power Platform 1919 268

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans