Gallery Delegation Issues with a Sharepoint stored Excel file

[TheodociusKMT]

Howdy,
I'm working with a PowerApp attached to an excel file stored in Sharepoint. When building out the gallery, I noticed that the search function does not work in terms of filtering out the choices in the gallery. That was unfortunate but I was able to find the filter function. I have two columns that I need the users to be able to search 'Rest' (which is a location number) and 'Name' (location name). When trying the Startswith function with rest, it doesnt work at all because it's a number column I suppose. However, name does. When trying the following expression out, I'm able to search name and rest number successfully, but I'm getting the dreaded yellow triangle of death. I'm not worried about delegation in this instance because it's just 88 rows. However, I do want to be a professional and find a workaround that searches both columns and doesn't have the delegation warning. Can anyone help me on this matter?

Filter(Table1,(StartsWith(Name,GallerySearcher.Text))|| StartsWith(Rest,GallerySearcher.Text))

[WarrenBelz · Accepted solution]

Hi @TheodociusKMT ,

Yes - you have a Delegation issue there due to the number field. If you only have 88 rows, you can get rid of the warning with this

With(
   {_Data: Table1},
   Filter(
      AddColumns(
         _Data,
         RestTxt,
         Text(Rest)
      ),
      StartsWith(
         RestTxt,
         GallerySearcher2.Text
      ) ||
      StartsWith(
         Name,
         GallerySearcher.Text
      )
   )
)

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

[BhargavPatel]

@TheodociusKMT Excel is not a delegable data source so you will get that warning. You may apply workarounds which will make the warning go away, but it will not make the query delegable i.e., Power App will still have to do the work of searching/filtering/sorting the data. However, I would not worry about delegation in your case since you are only dealing with 88 records. 

[TheodociusKMT]

Thanks @BhargavPatel  and @WarrenBelz !! It worked out! That's what I love about this community!!

[TheodociusKMT]

@WarrenBelz Quick follow up question, the fix you suggested, worked for fixing the warning but ended up breaking my patch formula. Is there an additional expression to get that back on track?

UpdateIf(

    colData,

    true,

    {

        'Display Start Time': DateAdd(TimeValue(

                DrpStartTimeHour.Selected.Value & ":" &

                DrpStartTimeMinute.Selected.Value & " " &

                DrpStartTimeAMPM.Selected.Value

        ),-TimeZoneOffset(Now()-120),TimeUnit.Minutes),

        'Display End Time': DateAdd(TimeValue(

                DrpEndTimeHour.Selected.Value & ":" &

                DrpEndTimeMinute.Selected.Value & " " &

                DrpEndTimeAMPM.Selected.Value

        ),-TimeZoneOffset(Now()-120),TimeUnit.Minutes),

        'Start DOW': Switch(Weekday(DateValue(drpStartDateSelect.Selected.Value)),1,"Sunday",2, "Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),

        'End DOW': Switch(Weekday(DateValue(drpStartDateSelect.Selected.Value)),1,"Sunday",2, "Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),

        'Start Date': DateValue(drpStartDateSelect.Selected.Value),

        'End Date': DateValue(drpStartDateSelect.Selected.Value),

        'Message Text': Text(MessageInput.Text)

    }

);

Patch(Table1,colData)

[WarrenBelz]

@TheodociusKMT ,

This is one of the (many) issues with Excel unless you have a unique identifier - you can try this

Patch(
   Table1,
   DropColumns(   
      colData,
      RestTxt
   )
)

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

[TheodociusKMT]

@WarrenBelz That worked as well! Thank you sooooooo much! Fixed the application completely!