Filter based on two combo boxes

[OsmandFernando]

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. 

How can I do this?

@mdevaney @PowerAddict and other colleagues. 

Osmand

[mdevaney · Accepted solution]

@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."

[v-xida-msft]

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,

[OsmandFernando]

@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?

 

@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. 

[mdevaney]

@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.

[OsmandFernando]

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

Osmand

[mdevaney]

@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."

[v-xida-msft]

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,

[OsmandFernando]

@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

[OsmandFernando]

@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