cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kriggo15
Post Patron
Post Patron

SQL Delegation Issue for Search

It looks like I'm having a delegation issue using Search with SQL Server, though that app doesn't state there is a delegation warning.

 

The SQL view I'm using as a source has > 2000 records. I have a gallery which uses the below code to filter the data based on a dropdown value. When I include Search in the code, the gallery immediately loads 2K records and does not present the full dataset. I don't understand why this is occurring. Neither of my filter criteria are numeric.

Returns 2000 records in the gallery upon gallery load:
Search(
    If(cmbType.Selected.Value = "Table",
        Sort(Filter('schema.view',  RID = DBID.Text), Name),
        Sort(Filter('schema.view',  RID = SchemaID.Text), Name)),
    TextInput3.Text, "Name")


Returns 100 records at a time and will increment past 2000 records:
If(cmbType.Selected.Value = "Table",
    Sort(Filter('schema.view',  RID = DBID.Text), Name),
    Sort(Filter('schema.view',  RID = SchemaID.Text), Name))

 

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks @WarrenBelz. I was actually able to figure it out by nesting the Search function in the true and else values:

If(
   cmbType_.Selected.Value = "Table",
   Search(Sort(Filter('schema.view', RID = DBID.Text), Name), TextInput3.Text, "Name"),
   Search(Sort(Filter('schema.view', RID = SchemaID.Text), Name), TextInput3.Text, "Name"))

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @kriggo15 ,

Try this

Sort(
   Filter(
      'schema.view',
      If(
         cmbType.Selected.Value = "Table",
         RID = DBID.Text,
         RID = SchemaID.Text
      ) &&
      (
         IsBlankl(TextInput3.Text) ||
         TextInput3.Text in Name
      )
   ),
   "Name"
)

 

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.

Thanks @WarrenBelz. I was actually able to figure it out by nesting the Search function in the true and else values:

If(
   cmbType_.Selected.Value = "Table",
   Search(Sort(Filter('schema.view', RID = DBID.Text), Name), TextInput3.Text, "Name"),
   Search(Sort(Filter('schema.view', RID = SchemaID.Text), Name), TextInput3.Text, "Name"))

View solution in original post

@kriggo15 ,

Very much the "long way around" - I tried to give you a much more compact solution, but also some options if the Search box was blank.

@WarrenBelz, I appreciate the help. Unfortunately, your code provided me with a delegation warning and also caused the gallery to not load any data.

@kriggo15 ,

That surprises me, however glad you got it working.

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,529)