Excellent video, I had such a bad time trying to find how to filter my gallery using a complex (choice) sharepoint! I found everything I was looking for, thank you!!
Thanks for the amazing video Reza. You have a way of explaining things thats simple and extremely effective. Complex lookup filtering made simple :) Thank you
Freaking amazing tutorial!!!! Thank you so much! been fighting trying to make my dropdowns filter the gallery correctly for 2 days. I need to learn how to bring in values from collections. Its one of the reasons why the filter was not working as well.
Drop-down "-select-" tip is a far better one I used to add a dummy entry = "Select" and set default value to record ID, this is a much better way there is no dummy record in the source Thanks mate
Is the Yes/No issue described at 8:20 only when displaying data from the SP list directly? I've tried to do something as simple as "ClearCollect(colName,Filter(SPlist, Inactive=false))", with Inactive being a Yes/No column ofcourse and there's neither a delegation warning and it collects the correct items.
Running queries and adding them to a collection does not bypass delegation. A single collect statement will hold up to a max of 2000 records depending upon the delegation limit set. As far as I know, the Yes/No column bug still persists.
@@RezaDorrani I just tested it and can't replicate the behavior or maybe I don't understand the case. i.imgur.com/MvKdKww.png I might switch to a text column anyway just to be on the safe side.
Hi Reza, i have a combobox with items set to Choices(List.Items), this choice column is configured to accept and save manually entered value from Form. I have no problem with saving the manually entered and listing in in the a combobox I placed as a Filter. However, when selecting the manually added choices, it seems not working and did not get any result from my list.
Another brilliant video from the genius of Power Automate. 1 Q Sir, Suppose I have 5 folders in a document library. I need to add a new column in only one of the folders. Once I create a column, it is added to all 5 folders. How can I ensure that the column is added to only one of the folders NOT ALL?
Hi Reza, thanks for this instructive video. I have a question, is it possible to have a search bar and drop down lists active for the same gallery? If so, do you have a video on this topic? In your video I see a search bar and different drop down lists, but not how they can work together on 1 same gallery.
Search is not a delegable function with SharePoint and should be avoided unless your list will be below 2000 items. Better option would be to use StartsWith function. I have a video on Multiple filters - ruclips.net/video/5dSk5iOgT68/видео.html
Really, very helpful playlist. Thank you vey much Reza! Could you please also do a video for filtering the Single Line of Text Filed or Choice Field with "Contains"?
@@RezaDorrani Thank you for the link. I have around 10+ filters (Text, Dropdown, Date controls) and maintaining pagination (2 Galleries - One hidden & One visble for results). So, hiding the rows based on CONTAINS ("IN") text is not working (showing empty rows). Could you please help me if there is any other workaround
@@hiddenimmortal It should work if the formulas are in place correctly. I will not be able to help here on chat. I recommend you post your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I mean, it is showing the rows but showing the visble rows based on pagination. For example, If there are 40 results, displaying 10 per page. Page 1 showing 2 rows, Page 3 showing 4 rows etc.
Smart filtering of data. It was really useful. I am looking for a method to use filter in filter (Example: Country filter & States filter - Country selection will show only respective states in state filter). Country & States are choice field in Share point. I would like to use this while feeding the data input to data base. While retrieving the data, I am using your collections filtering method (smart & easy syntax). thanks in advance.
Choice fields in SharePoint have no relationships between each other. You will probably have to create a list for Country and another list for states where the country data is also mapped. The you can leverage the lookup column type to get data from the list. Best place to deep dive on your request in case of issues would be the community forums powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1
I have been searching for such a video too. Well done, very clear explanation. But my question is how to filter this form on current user? .email = User().email
On App Start - set the User().Email in a variable Example: Set(varCurrentUserEmail, User().Email) Then while performing Filter operations use varCurrentUserEmail
@@RezaDorrani but where should i put in my global variable? I would like filter on a column called 'Relationship Manager' which is singel line Filter( CRACreditPointRequest, ('Type of request'.Value = DropDownTypeOfRequest.Selected.Value || If( IsBlank(DropDownTypeOfRequest.Selected.Value), true, false )) && (Status.Value = DropDownStatus.Selected.Value || If( IsBlank(DropDownStatus.Selected.Value), true, false )) /* I'm trying put in here - 'Relationship Manager'.Email = gloCurrentUser.Email but it doesn't work, it works if column is Person or group )
Superb Video, I am creating a text column default value of which is fetched from a different column, it works fine for new entries but when I click on edit, it reset to default. Request your help
Hi Reza, if we want only yes values in our application, then we can this Filter query (SPListName, IsActive) it will not give any delegation at all please advice if there is any limitation on it.
Hi Reza, great Video! Been trying to search the powerapp forums about the combobox with enabled search. I've tried using a collection and do not get a delegation warning but the field always just returns max first 500 items in a column. Is it a limitation of the combobox itself or might my Items formula be wrong?
Hi Emmanuel, I believe the combox will always run into the delegation limit as it uses the Search query ( Search is not delegable with SharePoint). In case of scenarios where you have more than 500 items in combobox, it would be better to redirect user to a different screen, where you can use a gallery and filters where they can make their selections.
@@RezaDorrani Thanks for your reply! I ended up just ditching the combo box altogether and just used text input control as a search input and just passing filter parameters to a gallery. I had initially wanted a different user experience with the combobox but works well with just txt input just the same, if not better. Really great series of videos about delegation, much appreciated!
Hi Reza. Thanks for the video. I am running through an error for the gallery items formula at '=' "Incompatible type for comparison. these types can't be compared. table, text"
You are comparing incorrect types. Difficult to know the issue without looking at your App in action. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hello Reza, I already learned so much by watching your channel. Thank you very much! I do have a question: based on this video, I can filter my gallery based on a choice field, using a dropdown box. Now, in addition, I would like the option to show all of the items in my gallery. Is this possible? regards Chris
Not sure I understood your question. You mean filter a look column based on text field? Then filter(splist, peoplepickercolumn.Email = txtBox.Text) where txtBox - will contain the email of the user.
Great video Raza! Question for anyone: I'm following the choice column filter and it works great except I need to allow tge combobox to allow multiple selections. But the steps in the videos are for single selection and if I update from selected.value to selecteditems it doesn't work. Any ideas?
Hey reaza I have 3 dropdown and i have a gallery where i am counting the row but if i am giving both filter and countrows its not working but if i am giving one by one differently its working Can you please guide me
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Great video Reza, I would like to ask for your help, I want to filter a gallery from sharepoint excluding specific items. I tried this "Filter('MySharePointList','MyColumn'.Value = "Accessory" && !(Title = "SpecificItem")". Works but with warning message, Could you help me with some workaround?
Hi Rozhin, You would need to add an additional query to the Filter function. Example: Filter( Students, Region.Value = drpRegion.Selected.Value || If( drpRegion.Selected.Value = "- Select -", true, false ), Class.Value = drpClass.Selected.Value || If( drpClass.Selected.Value = "- Select -", true, false ),StartsWith(Title,TextSearchBox1_2.Text) ) where StartsWith(Title,TextSearchBox1_2.Text) - is the query for the text field. Please note this is just an example.
Why does the ClearCollect(Choice) function in the App startup not give a delegation warning? Choice is not delegable. I wonder if this isn't a bug and that you should be getting an error. I'm going to experiment and see if this collection fails to retrieve choice options past the delegation limit.
The choice function returns the choices in the column. Sometimes the delegation warning does not reflect when working with collections. A collection will only store 2k records in 1 query. Collection is never the ideal solution for delegation. All collection data is held in memory in the App for that users session. The more data added to a collection, the more the performance will get impacted.
Thanks Reza posting series of videos on important topic Delegation. I am looking a help getting records in 1 - 1 relationship joined using Link column and would like to get records of child relationship with certain condition on Child lists columns other than link column
I have watched several of your videos and they have been very helpful. Using your example here, what if instead of a drop down for Region where you filter based on one value, instead you use a combo box so you can show all records from multiple selections, for example, show all from both East and West, or North, East & West? I cannot understand how to perform this without a delegation warning. I'm using this: Filter( 'Alert Tool', StartsWith( 'Record Number', 'Record number search box'.Text ), IsBlank('ComboBox Filter Status'.SelectedItems.Value) || IsEmpty('ComboBox Filter Status'.SelectedItems.Value) || 'Alert Status'.Value in 'ComboBox Filter Status'.SelectedItems.Value ) As I'm sure you know, the "in" is causing the delegation warning. What is the proper way to do this without that warning?
Hi Reza your videos are awesome! I have learned a lot from them. I have a question I have multiple filters applied on my gallery and now I want to include the filter on lookup column as well. I have written the below code but it's not working for lookup column could you see the issue in that Sort( Filter( 'Work List' , StartsWith( 'First Name', txtSeachbox.Text ) || StartsWith( Email, txtSeachbox.Text ) || StartsWith( 'Last Name', txtSeachbox.Text ) || StartsWith( 'Phone Number', txtSeachbox.Text ) || 'Applied Position'.Value = ddlPostion.Selected.Value ), Created, SortOrder.Descending ) Applied Position is my lookup column it's not filtering using it. but if I delete all the conditions and just put the Apllied Position condition in Filter function it work correctly
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Super important thing to note: make sure there are no "add a column to show each of these additional fields:" checked. I guess it gets more 'complex' than normal.
Excellent video, I had such a bad time trying to find how to filter my gallery using a complex (choice) sharepoint! I found everything I was looking for, thank you!!
Glad it was helpful!
All I was looking from powerapps using a sharepoint database is here. Thanks a lot.
You are welcome!
@@RezaDorrani Based in your instruction I could made five filters on the gallery. Works great. Thank you.
Filter(
GETA_db;
(Fase.Value = DropdownFase.SelectedText.Value || If(
(DropdownFase.SelectedText.Value = "todas");
true;
false
)) && (Observacao.Value = DropdownObs.SelectedText.Value || If(
(DropdownObs.SelectedText.Value = "todas");
true;
false
))&& (FaseConcluida2.Value = DropdownFaseConcluida.SelectedText.Value || If(
(DropdownFaseConcluida.SelectedText.Value = "todos");
true;
false
))&& (UF.Value = DropdownUF.SelectedText.Value || If(
(DropdownUF.SelectedText.Value = "todas");
true;
false
))&& (Atribuicao.Value = DropdownAtribuicao.SelectedText.Value || If(
(DropdownAtribuicao.SelectedText.Value = "todos");
true;
false
))
)
Thanks for the amazing video Reza. You have a way of explaining things thats simple and extremely effective. Complex lookup filtering made simple :) Thank you
Thanks so much.
Cannot believe Yes/No bug still exists! Thanks a lot for confirming it is a bug!
I almost always avoid Yes/No columns in SharePoint. Better to use Choice column and add 2 options "Yes,No"
Boolean columns are painful! Thanks Reza for this very useful video
Hello there :) Yes boolean columns are painful. I almost always try to avoid them.
Oh man i have been searching this video.. thanks for making it clear..
Thanks for watching
Excellent video! Helped me a lot, Thank you so much.
Glad it was useful
Freaking amazing tutorial!!!!
Thank you so much! been fighting trying to make my dropdowns filter the gallery correctly for 2 days.
I need to learn how to bring in values from collections. Its one of the reasons why the filter was not working as well.
Thanks for watching
Best video of month ! I would love to see more video's in the future. Good work :)
More coming :)
Superb Reza Bro, Thank you
You are most welcome
Drop-down "-select-" tip is a far better one
I used to add a dummy entry = "Select" and set default value to record ID,
this is a much better way there is no dummy record in the source
Thanks mate
Your welcome.
Recently though the dropdown control has a new property called showemptyselection which shows as empty value to start with.
Great set of videos, thank you
Thanks for watching!
Thank you Paul
Is the Yes/No issue described at 8:20 only when displaying data from the SP list directly?
I've tried to do something as simple as
"ClearCollect(colName,Filter(SPlist, Inactive=false))",
with Inactive being a Yes/No column ofcourse and there's neither a delegation warning and it collects the correct items.
Running queries and adding them to a collection does not bypass delegation. A single collect statement will hold up to a max of 2000 records depending upon the delegation limit set.
As far as I know, the Yes/No column bug still persists.
@@RezaDorrani I just tested it and can't replicate the behavior or maybe I don't understand the case. i.imgur.com/MvKdKww.png
I might switch to a text column anyway just to be on the safe side.
Text is the safest option
Many thanks. Useful information
Glad it was helpful!
Thank you
That is brilliant, thanks for sharing.
Glad you enjoyed it
Thank you Reza 🙌🙌
Most welcome!
Hi Reza, i have a combobox with items set to Choices(List.Items), this choice column is configured to accept and save manually entered value from Form. I have no problem with saving the manually entered and listing in in the a combobox I placed as a Filter. However, when selecting the manually added choices, it seems not working and did not get any result from my list.
Not sure about this. Have not tried this. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
You are a genius, thank you for the help!!
Welcome
Excellent tutorial to fix delegation issues! Very helpful. Question. How can a textbox search work together with a dropdown?
Than you Mark.
Search is not a delegable function with SharePoint. You would have to replace it with StartsWith function.
Another brilliant video from the genius of Power Automate. 1 Q Sir, Suppose I have 5 folders in a document library. I need to add a new column in only one of the folders. Once I create a column, it is added to all 5 folders. How can I ensure that the column is added to only one of the folders NOT ALL?
Column would be added to all 5 folders. That is the default behavior in SharePoint.
Hi Reza, thanks for this instructive video. I have a question, is it possible to have a search bar and drop down lists active for the same gallery?
If so, do you have a video on this topic? In your video I see a search bar and different drop down lists, but not how they can work together on 1 same gallery.
Search is not a delegable function with SharePoint and should be avoided unless your list will be below 2000 items.
Better option would be to use StartsWith function.
I have a video on Multiple filters - ruclips.net/video/5dSk5iOgT68/видео.html
Really, very helpful playlist. Thank you vey much Reza!
Could you please also do a video for filtering the Single Line of Text Filed or Choice Field with "Contains"?
With dataverse - ruclips.net/video/5dSk5iOgT68/видео.html
With SharePoint - ruclips.net/video/44j2VRbdWjk/видео.html
@@RezaDorrani Thank you for the link. I have around 10+ filters (Text, Dropdown, Date controls) and maintaining pagination (2 Galleries - One hidden & One visble for results). So, hiding the rows based on CONTAINS ("IN") text is not working (showing empty rows).
Could you please help me if there is any other workaround
@@hiddenimmortal It should work if the formulas are in place correctly. I will not be able to help here on chat. I recommend you post your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I mean, it is showing the rows but showing the visble rows based on pagination.
For example, If there are 40 results, displaying 10 per page. Page 1 showing 2 rows, Page 3 showing 4 rows etc.
@@hiddenimmortal that would be a limitation for in function since it is a hack. It will work will without pagination.
Hi, how do we combine the search bar function from part 1. With this drop down selection too
You can search and filter at same time. I have a video on that topic.
Smart filtering of data. It was really useful. I am looking for a method to use filter in filter (Example: Country filter & States filter - Country selection will show only respective states in state filter). Country & States are choice field in Share point. I would like to use this while feeding the data input to data base. While retrieving the data, I am using your collections filtering method (smart & easy syntax). thanks in advance.
Choice fields in SharePoint have no relationships between each other.
You will probably have to create a list for Country and another list for states where the country data is also mapped.
The you can leverage the lookup column type to get data from the list.
Best place to deep dive on your request in case of issues would be the community forums
powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1
I have been searching for such a video too. Well done, very clear explanation.
But my question is how to filter this form on current user? .email = User().email
On App Start - set the User().Email in a variable
Example: Set(varCurrentUserEmail, User().Email)
Then while performing Filter operations use varCurrentUserEmail
@@RezaDorrani but where should i put in my global variable? I would like filter on a column called 'Relationship Manager' which is singel line
Filter(
CRACreditPointRequest,
('Type of request'.Value = DropDownTypeOfRequest.Selected.Value || If(
IsBlank(DropDownTypeOfRequest.Selected.Value),
true,
false
)) && (Status.Value = DropDownStatus.Selected.Value || If(
IsBlank(DropDownStatus.Selected.Value),
true,
false
)) /* I'm trying put in here - 'Relationship Manager'.Email = gloCurrentUser.Email but it doesn't work, it works if column is Person or group
)
@@toox500 If column is single line of text then 'Relationship Manager' = gloCurrentUser.Email
@@RezaDorrani Thank you! I have solved the problem :-) You are the best!
Superb Video, I am creating a text column default value of which is fetched from a different column, it works fine for new entries but when I click on edit, it reset to default. Request your help
I have not tried this scenario. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi Reza, if we want only yes values in our application, then we can this Filter query (SPListName, IsActive) it will not give any delegation at all please advice if there is any limitation on it.
Avoid using Yes/No type columns - rezadorrani.com/index.php/2019/04/14/powerapps-filter-sharepoint-yes-no-column-bug/
Wonderful!
Thank you!
Hi Reza, great Video! Been trying to search the powerapp forums about the combobox with enabled search. I've tried using a collection and do not get a delegation warning but the field always just returns max first 500 items in a column. Is it a limitation of the combobox itself or might my Items formula be wrong?
Hi Emmanuel,
I believe the combox will always run into the delegation limit as it uses the Search query ( Search is not delegable with SharePoint).
In case of scenarios where you have more than 500 items in combobox, it would be better to redirect user to a different screen, where you can use a gallery and filters where they can make their selections.
@@RezaDorrani Thanks for your reply! I ended up just ditching the combo box altogether and just used text input control as a search input and just passing filter parameters to a gallery. I had initially wanted a different user experience with the combobox but works well with just txt input just the same, if not better. Really great series of videos about delegation, much appreciated!
Hi Reza. Thanks for the video. I am running through an error for the gallery items formula at '=' "Incompatible type for comparison. these types can't be compared. table, text"
You are comparing incorrect types. Difficult to know the issue without looking at your App in action. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hello Reza,
I already learned so much by watching your channel. Thank you very much!
I do have a question: based on this video, I can filter my gallery based on a choice field, using a dropdown box.
Now, in addition, I would like the option to show all of the items in my gallery.
Is this possible?
regards
Chris
Yes, absolutely. Check - ruclips.net/video/5dSk5iOgT68/видео.html
@@RezaDorrani Thanks for responding. You are the best! I'm sure I can make this work :-)
Hello Reza, how can I lookup a People Picker based on another text field?
Not sure I understood your question. You mean filter a look column based on text field?
Then filter(splist, peoplepickercolumn.Email = txtBox.Text)
where txtBox - will contain the email of the user.
Great video! Just a quick question, how would you sort that now by date created column?
Just decorate the Filter function with SortByColumns
SortByColumns(Filter condition..... , Created, Descending)
Great video Raza! Question for anyone: I'm following the choice column filter and it works great except I need to allow tge combobox to allow multiple selections. But the steps in the videos are for single selection and if I update from selected.value to selecteditems it doesn't work. Any ideas?
I recommend posting your query with screenshots on the forums at powerusers.microsoft.com in case someone has done something similar
How can I combine a dropdown and a textbox with data from the sharepoint?
Check ruclips.net/video/5dSk5iOgT68/видео.html
Hey reaza
I have 3 dropdown and i have a gallery where i am counting the row but if i am giving both filter and countrows its not working but if i am giving one by one differently its working
Can you please guide me
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Great video Reza, I would like to ask for your help, I want to filter a gallery from sharepoint excluding specific items. I tried this "Filter('MySharePointList','MyColumn'.Value = "Accessory" && !(Title = "SpecificItem")". Works but with warning message, Could you help me with some workaround?
Not operator does not support delegation.
Check my workarounds video (may be it helps) - ruclips.net/video/yXvDj60-3YQ/видео.html
@@RezaDorrani Tanks a lot.
what would I do if I wanted my gallery to filter on those two dropdown fields, as well as, a TextInput field to filter on title
Hi Rozhin,
You would need to add an additional query to the Filter function.
Example:
Filter(
Students,
Region.Value = drpRegion.Selected.Value || If(
drpRegion.Selected.Value = "- Select -",
true,
false
),
Class.Value = drpClass.Selected.Value || If(
drpClass.Selected.Value = "- Select -",
true,
false
),StartsWith(Title,TextSearchBox1_2.Text)
)
where StartsWith(Title,TextSearchBox1_2.Text) - is the query for the text field.
Please note this is just an example.
@@RezaDorrani OMG thank you so much, I was putting my text field query in the wrong place.
Have things changed since then for Yes/no columns in SharePoint, or do you still recommend using a choice column?
They have not. I would still recommend using a Choice column.
Why does the ClearCollect(Choice) function in the App startup not give a delegation warning? Choice is not delegable. I wonder if this isn't a bug and that you should be getting an error. I'm going to experiment and see if this collection fails to retrieve choice options past the delegation limit.
The choice function returns the choices in the column. Sometimes the delegation warning does not reflect when working with collections. A collection will only store 2k records in 1 query. Collection is never the ideal solution for delegation. All collection data is held in memory in the App for that users session. The more data added to a collection, the more the performance will get impacted.
Thanks Reza posting series of videos on important topic Delegation. I am looking a help getting records in 1 - 1 relationship joined using Link column and would like to get records of child relationship with certain condition on Child lists columns other than link column
Not sure I understand the request. Is this for SharePoint or CDS?
I have watched several of your videos and they have been very helpful. Using your example here, what if instead of a drop down for Region where you filter based on one value, instead you use a combo box so you can show all records from multiple selections, for example, show all from both East and West, or North, East & West? I cannot understand how to perform this without a delegation warning. I'm using this:
Filter(
'Alert Tool',
StartsWith(
'Record Number',
'Record number search box'.Text
),
IsBlank('ComboBox Filter Status'.SelectedItems.Value) || IsEmpty('ComboBox Filter Status'.SelectedItems.Value) || 'Alert Status'.Value in 'ComboBox Filter Status'.SelectedItems.Value
)
As I'm sure you know, the "in" is causing the delegation warning. What is the proper way to do this without that warning?
IN is not a delegable function with SharePoint. There is no way around it except a hack - ruclips.net/video/44j2VRbdWjk/видео.html
Can I use delegation on less than 2000 items? Thank you. 🙏
I did not understand the question.
@@RezaDorrani My data has less than 500 but can I still use the delegable functions just incase someday the data grows over the limits.
@@bzflowerbee Yes, you can use them.
Excellent video as always..
Is Yes/No columns issue resolved now ? or still we need to go for workaround of choice/text/number column ?
Still an issue
Best vídeo!
Thank you :)
Hi Reza your videos are awesome! I have learned a lot from them. I have a question I have multiple filters applied on my gallery and now I want to include the filter on lookup column as well. I have written the below code but it's not working for lookup column could you see the issue in that
Sort(
Filter(
'Work List' ,
StartsWith(
'First Name',
txtSeachbox.Text
) ||
StartsWith(
Email,
txtSeachbox.Text
) ||
StartsWith(
'Last Name',
txtSeachbox.Text
) ||
StartsWith(
'Phone Number',
txtSeachbox.Text
) ||
'Applied Position'.Value = ddlPostion.Selected.Value
),
Created,
SortOrder.Descending
)
Applied Position is my lookup column it's not filtering using it. but if I delete all the conditions and just put the Apllied Position condition in Filter function it work correctly
Formula looks good. I would break it down part by part and see which starts with function is causing the data to be empty.
@@RezaDorrani Thanks for replying. I will check that
i used ". Value" for choice and lookup columns. But I still have an error. Can any one please help me, What might be the case.
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Super important thing to note: make sure there are no "add a column to show each of these additional fields:" checked. I guess it gets more 'complex' than normal.
It just brings in more data based on number of additional fields selected.