Hi,
I have a new requirement to filter a gallery based on 2 combo boxes.
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
Solved! Go to Solution.
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."
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."
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,
@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.
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.
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
---
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."
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,
@mdevaney I tried to follow that post. I did like below,
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
@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,
Osmand
Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.
Did you miss the call?? Check out the Power Apps Community Call here!
User | Count |
---|---|
265 | |
209 | |
77 | |
41 | |
33 |
User | Count |
---|---|
345 | |
218 | |
117 | |
71 | |
54 |