Filter based on two combo boxes

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

Sort by

Verified answer

mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!
@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."

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
OsmandFernando on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
OsmandFernando on at

Like (0)

Report
Copy link

Link copied!

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

Osmand

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!

@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-list-lookup-column-Id-versus/m-p/369550/highlight/true#M106464

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

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
OsmandFernando on at

Like (0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
OsmandFernando on at

Like (0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @OsmandFernando ,

If you have faced a Delegation warning issue with your formula in your app, it means that you could not delegate the data process to your data source, instead, you could only process data locally.

In default, you could only process 500 records locally at most. You could change this limit to maximum value -- 2000, then you could process 2000 records locally at most.

If you would like this Delegation records limits to be increased, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

More details about delegable functions supported for CDS connector, please refer to the following article:
https://docs.microsoft.com/en-us/connectors/commondataservice/#powerapps-delegable-functions-and-operations-for-the-cds-for-apps

If the amount of your data source records is more than 2000, as an alternative solution, you could consider bulk-load records from your data source into a collection in your app, then use the collection as data source in your app instead of the original data source.

Please check and see if the following thread would help in your scenario:

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

Best regards,

Was this reply helpful? Yes No