cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Filter based on two combo boxes

Hi,

 

I have a new requirement to filter a gallery based on 2 combo boxes. 

 

  • 1st Combo box is for the model number
  • 2nd Combo box for the sales month

I have done the filter for the 1st box and working fine. But I want to filter the Gallery 1st by Month then by model

Following is my current formula under Gallery Item property to filter by model

 

If(IsBlank(ComboBox3.Selected),SalesDetails_1,Filter(SalesDetails_1,'Model No'.Value=ComboBox3.Selected.'Model Number'))

 

Under the SalesDetails_1 I have a column name as SalesMonth. 

Capture.PNG

How can I do this?

 

@mdevaney @PowerAddict and other colleagues. 

 

Osmand

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filter based on two combo boxes

@OsmandFernando 

My suggestion would be the following.  Please change ComboBoxMonth and 'Sales Month' to match your names.  I don't know what those are without seeing the app.

Filter(
    SalesDetails_1,
    IsEmpty(ComboBox3.Selected)=true Or 'Model No'.Value = ComboBox3.Selected.'Model Number',
    IsEmpty(ComboBoxMonth.Selected)=true Or SalesMonth = ComboBoxMonth.Selected.'Sales Month',
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

13 REPLIES 13
Highlighted
Super User
Super User

Re: Filter based on two combo boxes

@OsmandFernando 

My suggestion would be the following.  Please change ComboBoxMonth and 'Sales Month' to match your names.  I don't know what those are without seeing the app.

Filter(
    SalesDetails_1,
    IsEmpty(ComboBox3.Selected)=true Or 'Model No'.Value = ComboBox3.Selected.'Model Number',
    IsEmpty(ComboBoxMonth.Selected)=true Or SalesMonth = ComboBoxMonth.Selected.'Sales Month',
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Community Support
Community Support

Re: Filter based on two combo boxes

Hi @OsmandFernando ,

Do you want to filter your Gallery Items based on the two ComboBox selected values?

Could you please share more details about the formula you typed within the Items property of the second ComboBox (Sales Month)?

Further, could you please share a bit more about the "Sales Month" column in your SP List?

 

If the "Sales Month" column is a Number type column in your SP List, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       SalesDetails_1,
       If(
          IsBlank(ComboBox3.Selected.'Model Number'),
          true,
          'Model No'.Value = ComboBox3.Selected.'Model Number'
       ),
       If(
          IsBlank(SalesMonthComboBox.Selected.ColumnName), // ColumnName represents the column you used as Display value within the SalesMonthComboBox
          true,
          SalesMonth = Value(SalesMonthComboBox.Selected.ColumnName)
       )
)

Note: I assume that you list all available month values within the second ComboBoxm, e.g. 1, 2, 3, 4, 5, 6, 7, 8, ...

 

Please consider take a try with above solution, check if the issue is 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.
Highlighted
Post Prodigy
Post Prodigy

Re: Filter based on two combo boxes

@mdevaney  Thank you so much for your help, It is working fine after I changing field name and combobox name. But it has 3 delegation warnings as you can see in the below screenshot, (Or, Value and =). Is there any way to overcome those?

 

Capture.PNG 

@v-xida-msft  Thank you so much for your support, I tried your solution as well after changing the field name and combobox name. It shows the entire 1st set (Model) with delegation warning and when I filter the month it did not work. Under the Month Combobox I have inputted the values from Jan to Dec manually like this, 

 

["January","February","March","April","May","June","July","August","September","October","November","December"]

 

Osmand. 

Highlighted
Super User
Super User

Re: Filter based on two combo boxes

@OsmandFernando 

Suggest you try my code exactly as is.  I believe the delegation warnings could be occuring because you put my suggested code inside of an IF statement.  The IF function cannot be delegated for SharePoint.

Highlighted
Post Prodigy
Post Prodigy

Re: Filter based on two combo boxes

@mdevaney Thank you so much and I did that but still it has the delegation warning issue

 

Annotation 2019-12-23 190320.jpg

 

Osmand

Highlighted
Super User
Super User

Re: Filter based on two combo boxes

@OsmandFernando 

The additional delegation problems here are coming from the use of .'Sales Model' and '.Value'.  My friend @Edwin-Abdalian proposed a very nice solution I think you should read.  He proposed bringing the additional lookup columns into your SharePoint list to avoid the delegation issue.

 

Link to old forum post

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Delegation-warning-on-SharePoint-li...

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Community Support
Community Support

Re: Filter based on two combo boxes

Hi @OsmandFernando ,

Could you please share a bit more about your data source? Is it a SP List?

How many records stored in your data source (SalesDetails_1)? More than 2000?

 

Please consider modify your formula as below:

Filter(
       SalesDetails_1,
       If(
          IsBlank(ComboBox3.Selected.'Model Number'),
          true,
          'Model No'.Value = ComboBox3.Selected.'Model Number'
       ),
       If(
          IsBlank(ComboBox3_1.Selected.Value),
          true,
          'Invoice Number:SalesMonth'.Value = ComboBox3_1.Selected.Value
       )
)

 

If the amount of your data source records is not more than 2000, I think a collection could achieve your needs. Please consider save your data source into a collection, then use the collection as data source within your app instead of Original SalesDetails_1 data source.

Set the OnStart property of App to following:

ClearCollect(RecordsCollection, SalesDetails_1)

Then modify your Filter formula as below:

Filter(
       RecordsCollection,   // Modify formula here
       If(
          IsBlank(ComboBox3.Selected.'Model Number'),
          true,
          'Model No'.Value = ComboBox3.Selected.'Model Number'
       ),
       If(
          IsBlank(ComboBox3_1.Selected.Value),
          true,
          'Invoice Number:SalesMonth'.Value = ComboBox3_1.Selected.Value
       )
)

then re-load your app (fire the OnStart property of App), then check if the issue is solved.

Note: Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

If the amount of your data source records is not more than 2000, you could ignore this Delegation warning issue.

 

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.
Highlighted
Post Prodigy
Post Prodigy

Re: Filter based on two combo boxes

@mdevaney  I tried to follow that post. I did like below, 

 

  • Create a lookup column as IDMonth
  • Then select the SalesTable ID field also additional fields and Sales Month
  • Then when I go back to the list I realized that I need to select the ID to populate the Sales month

on the above 3 bullet point, is my understanding correct? if correct, it does not make sense to the end user to select such an ID where he/she has no idea which ID to select. 

 

Kindly correct me if I am wrong..

 

Osmand

Highlighted
Post Prodigy
Post Prodigy

Re: Filter based on two combo boxes

@v-xida-msft  Thank you for your reply. 

 

Yes It is a SP list. And it will have more than 2000. My current set up is also set to 2000. (not 500)

 

So, if the list items are going to be more than 2000, Won't collection also work?

 

I am starting to getting worried now as I have done some collections to my app where the list will have more than 2k

 

Also, i want to know, 

 

  • Is there any development plans from Microsoft to overcome those maximum line issues and delegation issues?
  • Will the delegation issue have with CDS?

Osmand

 

Helpful resources

Announcements
secondImage

New Return to Workplace

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

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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
Users online (3,642)