cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filtering with combo box

I'm attempting to filter a data sheet based on a SQL table, and attempting to avoid all delegation issues.

 

My table has mostly text columns, but a couple of value based columns, one of which is integer.

 

In my filter statement, I have many StartsWith(ColumnName1, TextInput1.Text), StartsWith(ColumnName2, TextInput2.Text) conditions.

 

For one column I have labeled "Status" which is an integer 0-8, I have a combo box (I like combo box because I can clear the value and leave it blank) with all the statuses as selections.  

 

The problem I'm having is, if I add Status=ComboBox.Selected.Status, it will only show items if I select a status.  If I clear the combo box, I get no data.

 

I tried to solve this with an If statement, but that seems to break the delegation rules.  I even tried to do a StartsWith(Text(Status),Text(ComboBox1.Selected.Status) but that doesn't seem to work either.

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filtering with combo box

Hi @GOBrien11 ,

How many records stored in your SQL Table? More than 2000?

 

Yeah, you are right. Currently, the Filter('[dbo].[SQLTable]', If(...)) could not be delegated in SQL Table. If the records stored in your SQL Table is not more than 2000, you could ignore this Delegation warning issue.

 

As an possible solution, I agree with your formula almost. You need to put the If formula outside the Filter function:

If(
   IsBlank(ComboBox1.Selected.Status),
   Filter(
          '[dbo].[YourSQLTable]',
          StartsWith(ColumnName1, TextInput1.Text),
          StartsWith(ColumnName2, TextInput2.Text),
          ...
   ),
   Filter(
          '[dbo].[YourSQLTable]',
           StartsWith(ColumnName1, TextInput1.Text),
           StartsWith(ColumnName2, TextInput2.Text),
          ...,
           Status = ComboBox1.Selected.Status
   )
)

 

But there are some limits with above formula, if you want to filter your SQL Table records based on multiple ComboBoxes, above solution would be complex.

 

If you want to get rid of the Delegation warning issue, you could also consider load your SQL Table records into a local collection, then use the local collection as data source in your Filter formula. Please check the solution in the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

After that, you would be able to use the following formula:

 Filter(
          SQLTableLocalCollection,                    // Use the local collection as data source
          StartsWith(ColumnName1, TextInput1.Text),
          StartsWith(ColumnName2, TextInput2.Text),
          ...,
          If(
             IsBlank(ComboBox1.Selected.Status),
             true,
             Status = ComboBox1.Selected.Status
          )
)

 

If above solution is helpful in your scenario, or you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Filtering with combo box

Hi @GOBrien11 ,

Because Status is an Integer, StartsWith will not work on blank (a number never starts with an empty string) - so 

Filter(
   YourDataSource,
   If(
      IsBlank(ComboBox1),
      true,
      StartsWith(
         Text(Status),
         ComboBox1.Selected.Status
      )
   )
) 

 

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.

Highlighted
Frequent Visitor

Re: Filtering with combo box

Tried this earlier, and it works, but the If statement throws the delegation issue.

Highlighted
Frequent Visitor

Re: Filtering with combo box

I'll tell you what worked:

 

If(IsBlank(ComboBox1.Selected.Status), Filter (All text filters), Filter (All text filters + Status = ComboBox1.Selected.Status))

 

This just doesn't seem to be the most efficient way to code this..

Highlighted
Community Support
Community Support

Re: Filtering with combo box

Hi @GOBrien11 ,

How many records stored in your SQL Table? More than 2000?

 

Yeah, you are right. Currently, the Filter('[dbo].[SQLTable]', If(...)) could not be delegated in SQL Table. If the records stored in your SQL Table is not more than 2000, you could ignore this Delegation warning issue.

 

As an possible solution, I agree with your formula almost. You need to put the If formula outside the Filter function:

If(
   IsBlank(ComboBox1.Selected.Status),
   Filter(
          '[dbo].[YourSQLTable]',
          StartsWith(ColumnName1, TextInput1.Text),
          StartsWith(ColumnName2, TextInput2.Text),
          ...
   ),
   Filter(
          '[dbo].[YourSQLTable]',
           StartsWith(ColumnName1, TextInput1.Text),
           StartsWith(ColumnName2, TextInput2.Text),
          ...,
           Status = ComboBox1.Selected.Status
   )
)

 

But there are some limits with above formula, if you want to filter your SQL Table records based on multiple ComboBoxes, above solution would be complex.

 

If you want to get rid of the Delegation warning issue, you could also consider load your SQL Table records into a local collection, then use the local collection as data source in your Filter formula. Please check the solution in the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

After that, you would be able to use the following formula:

 Filter(
          SQLTableLocalCollection,                    // Use the local collection as data source
          StartsWith(ColumnName1, TextInput1.Text),
          StartsWith(ColumnName2, TextInput2.Text),
          ...,
          If(
             IsBlank(ComboBox1.Selected.Status),
             true,
             Status = ComboBox1.Selected.Status
          )
)

 

If above solution is helpful in your scenario, or you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,117)