HomeНаука и техникаRelated VideosMore From: Shane Young

Learn to use the PowerApps Filter function

211 ratings | 27670 views
In this show, we explore the PowerApps Filter Function. We discuss the function, how to build a search box, the nuances of the PowerApps Filter SharePoint, and build a custom PowerApps Filter Gallery. Very cool stuff. Document on PowerApps Delegation https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview Video on PowerApps Search https://www.youtube.com/watch?v=FaTnOOc34os Bold Zebras for all of your PowerApps consulting needs. https://www.BoldZebras.com
Html code for embedding videos on your blog
Text Comments (161)
Konstruktionsbüro JB (19 days ago)
Thanks again for a clear and concise video. The expression "Filter(’Project Details’,ProjectName = Gallery2.Selected.ProjectName, ProjectTask = Gallery3.Selected.tasks)" makes perfect sense to me , but how about using a DatePicker control in stead of a gallery for the second logical text? I am building an app that allows a production manager to keep track of hours used by external employees on a given case pr. day. The user selects case and relevant date and the app presents which external employees were on site and how many hours each used in a DataForm. My expression looks like this: Filter(ExternalWorkers; Titel=GlryCases.Selected.Title2.Text; Date = DatePicker1.SelectedDate) which returns no data to the form and a grumpy reply from PowerApps that the expression is not valid. Using only the first logical expression it works fine - obviously without filtering by date, so, something is fishy with the dates, but what?
Konstruktionsbüro JB (13 days ago)
+Shane Young Solved it! in short, the two dates need to be converted to values i.e. Filter(TempHourAccount; Value(DateOnJob)= Value(DatePicker1.SelectedDate)). It does produce a blue squiggly line, but it works - both on its own and in the original expression.
Konstruktionsbüro JB (15 days ago)
+Shane Young I tried making a new SharePoint List and rather than using a template I made the corresponding new app from scratch. The result however is the same; Fitering by case number works just fine, but as soon as I try to add the expression with the DatePicker into the string it comes to a screeching halt again. I have no problem having a label repeating the DatePicker1 selected date (using your "if in doubt use a label as a control measure"), hence the issue seems to be focused on filtering by the same value – both alone and as part of a series of filter expressions like the one in the post above. SharePoint returns this message: “The desired action is invalid. The expression "DatoP_x00e5_Job eq 2018-11-15 is invalid. clientRequestID.......” What I notice here is the DatePicker1 value is US style YYYY-MM-DD and DatoP_x00e5_Job is Scandinavian DD-MM-YYYY, could it be something as simple?
Shane Young (16 days ago)
I would play with. What you want to do is normal so something weird must be messing with you. If you display the date in the gallery do you see what you expect?
Konstruktionsbüro JB (16 days ago)
+Shane Young a SharePoint list "ExternalWorkers" containing data on case name and number, employee details, and the date of the entry. I am beginning to think that there is a conflict between the date picker's output and the data in the date field of the SharePoint list. I exchanged the DataForm with a gallery and again the first logical argument worked perfectly, but adding the Date picker still fouls things up.
Shane Young (16 days ago)
What is your data source?
shinkakida (26 days ago)
Hello Shane, first your videos are life savers. I have a quick question. If I want a list to display as a gallery filtered on the current user (I.e. I have Sharepoint list filtered to [me]) how would accomplish the same thing in a powerapp?
Shane Young (24 days ago)
Filter(SharePointlist, emailcolumn = user().email) is an example
Linda Adams (1 month ago)
Great videos. Would have been nice to see the detail in your SharePoint list. I'm a newbie and it would have helped me put things together much easier since we went so fast viewing the filter. Thanks for posting.
Shane Young (29 days ago)
Sorry Linda. Did you get it figured out? I will try to do better in future videos. Thanks for The feedback
Ben Gillard (1 month ago)
Hi, is it possible to see a screen shot of the three lists you used as the data source for the three galleries? Thanks in advance
Shane Young (29 days ago)
Ben did you get this figured out? If not email me and I will send you the screenshots.
LearnIT (1 month ago)
I have a check box and a button in my gallery. For some reason when I try to hide button by when checkbox is true but it seems to hide or show all buttons in the gallery. What am i doing wrong? 🤔 🤯
LearnIT (1 month ago)
+Shane Young Thanks.
Shane Young (1 month ago)
That is normal. You are setting all the button properties to that value. What you need is to think about it differently. When you check the box you need to update a property of the data source. Show: false. Then set the buttons visible property to ThisItem.Show This video has a similar concept shown https://www.youtube.com/watch?v=xgznk4XlPCo
Deep Shah (1 month ago)
Hi Shane, Thanks for your videos. I have an app with 2 dropdown filters and a search box. But now Im trying to create a button that will clear all the filters and give me all the list . I tried a few ways but nothing seemed to work well. Do you know how I can do that?
Shane Young (1 month ago)
Usually, in those complex scenarios, I use the filters/search box to create a collection. And then have the gallery just show the collection. That way my reset button can reset the controls and the collection.
Adam Hiatt (1 month ago)
I was hoping you'd go a little more in depth on how to add multiple search parts to a filter. PowerApps automatically created a filter for me that works ok but not great. Here's what it says: SortByColumns(Filter('Truck Inventory', StartsWith(Title, 'Search Bar'.Text)), "Title", If(SortDescending1, Descending, Ascending)) The problem is it only searches the title. I have tried a few ways to get it to also search the Item Description field so we can search by all these areas but I can't seem to find the right setup. I'm also not fond of the "StartsWith" because if the search is for a word later in the name of the field, it won't find it. Suggestions? Is there a "Contains" option? Your videos have been a great help so far. Thanks in advance.
Andrew Adams (2 days ago)
Does this work: SortByColumns(Filter('Truck Inventory', StartsWith(Title, 'Search Bar'.Text)||StartsWith(Description,'Search Bar'.Text)), "Title", If(SortDescending1, Descending, Ascending)) Where Description is the name of the item description column that you want to make searchable.
Sweta Gupta (1 month ago)
Hello Shane, Very informative video. Any advice on how to get the price on the basis of selection done from Item drop down. I am using one drive excel list to display the items on drop down and on the other column of excel sheet I have price listed. I tried multiple options of filter but every time get failed. Thanks
Moonpie Sunbeam (1 month ago)
Hi Shane. Great video, very informative! Any advice on how to utilize the filter functionality to create a search center app for SharePoint document libraries (potentially hundreds of libraries)? Since the search in SP itself isn't great, I am considering building a solution in PowerApps for my company instead of using a SP Search Center site. Thanks!
Shane Young (1 month ago)
I think SharePoint search is probably better than what you can do in PowerApps. But I hope you prove me wrong. 😀
host funding (1 month ago)
- Great stuff, you are really helping me. I am designing something amazing for our workplace and you are going to be our saving grace. THANKS! I know I will have questions and will send through to you. Would love to show you what I came up with in the end, as a non-developer who just wants to make life easier (oh, and enjoys this stuff!). Thank you for being so clear and being very honest about what works, what doesn't and what wastes time. So helpful!
Shane Young (1 month ago)
Feel free to email me shane @powerapps911.com anything you want to share. Thank 😻
Daniel Bell (1 month ago)
Hi Shane, Great vide, I’ve watched a fair few of them now and they are very informative! :) . Could you please advise how I would filter data by user? I have one sharepoint list that contains a job sheet. This job sheet list has columns such as customer details and jobs description and one of the columns contains a drop down box linked to another sharepoint list which contains employee details where one of the columns in this list holds the employee email address...however this isn’t the column that is displayed In my job sheet form, the job sheet form shows their actual name from a separate column in the employee details list. I want the resulting filtered data to display all the items from the Job sheet but only for the jobs that are assigned to the user that is filtered. Either via a filter box or appstartup. Is this possible? Note. Each employee has a office 365account and email address.
Andrew White (20 days ago)
This what I to filter assigned Projects. Filter(Projects, Status.Value = "In Progress", AssignedTo.DisplayName = User().FullName)
Daniel Bell (24 days ago)
Shane Young thanks for the Reply shane. I guess I’m a little stuck on how to get the user() username to reference a certain record in the form. For example I have a record in my form and this is called “AssignedTo” in this record I have the name of the employee that the job is assigned too. We have several employees and I’m wanting when they load the app on their phone/device to just see any jobs that have their name in the “AssignedTo” record. I can’t work out the formula for the app startup property to make the current user have to match the record in “AssignedTo”. Does that make more sense? 😳
Shane Young (1 month ago)
Yes. 😀 But I am a little lost. Remember you can use User().Email to get the current logged in user.
Nigel Irwin (1 month ago)
How can I associate a single form with 3 different galleries. The galleries have been filtered to show a subset of data. They all contain the same fields. Is there a way to use a variable in the items property of a form so instead of Items: gallery1.selected it could be Items:varGal.selected, where varGal contained the name of the instigating gallery.
Nigel Irwin (1 month ago)
+Shane Young Thanks for your reply. I did it another way by using buttons to set a variable to a filter string, and using that variable in the Gallery Items property and worked a treat. Formula was: Set(varItems,Filter('Trainer Allocation',Status.Value = "Awaiting sign up")); Navigate(Screen2, ScreenTransition.Fade) on the button and the gallery item property was set to: varItems. Saved having multiple galleries/screens
Shane Young (1 month ago)
Yes. Set the items property for the form to Lookup(datasource, id = variable)
Fábio J Júnior (1 month ago)
Tenho uma planilha Excel com o seguinte cabeçalho: Prédio e Equipamentos. Com vários prédios e vários equipamentos....sendo que temos prédios que a lista de equipamentos é maior ou menor que os demais. Gostaria de trabalhar com card suspenso de modo que filtre o prédio e que a lista de equipamentos seja filtrada respeitando os equipamentos do prédio filtrado, é possível?
Fábio J Júnior (1 month ago)
+Shane Young Thank you!!
Shane Young (1 month ago)
Try this video PowerApps Cascading Dropdown https://youtu.be/pkZG2boN7jQ
Fábio J Júnior (1 month ago)
Sorry my english
Fábio J Júnior (1 month ago)
Sorry my english
Fábio J Júnior (1 month ago)
+Shane Young I have an Excel spreadsheet with the following heading: Building and Equipment. With several buildings and various equipment .... being that we have buildings that the equipment list is bigger or smaller than the others. I would like to work with a suspended card so that it filters the building and that the list of equipment is filtered respecting the equipment of the filtered building, is it possible?
Ersula Williams (1 month ago)
Hi Shane, Thank you for the video. If the filter does not have the item in the gallery, is there any way to popup a message or disable a button not allowing the user to continue so they won't reach an empty gallery?
Shane Young (1 month ago)
Yes. Look up my video on conditional formatting. You can check if the results IsEmpty and not show the button if true.
Purnima Sharma (1 month ago)
Hi Shane, Thankyou for this another amazing video..I want to show edit button on each record of gallery..How to do this?
Shane Young (1 month ago)
You can add a button to the gallery. What are you struggling with?
Abiha Rizvi (2 months ago)
Hello Shane. I have a question that can we use a filter function on images? What I am trying to do is that when we click on an image, the particular records popup on the gallery just in the same way as when you typed in the title, the records popped up that matched the title. Can we do this on images instead of a text field?
Shane Young (1 month ago)
You can set the on select of an image to trigger a pop up. No problem
Abiha Rizvi (2 months ago)
I haven't done anything yet. Was trying to figure it out. Can you please help?
Shane Young (2 months ago)
I don’t see why not. What have you tried?
Ambuja Kumbhar (2 months ago)
Hi Shane, can you please tell me how to change gallery selected item on button click or any other event, like passing as parameter to change the slected item of Gallery ?
Webmaster AdrianIvanov (1 month ago)
Yes... it definitely appears to be an impossible! - I also needed to do that and I've been unable too in any way.
Shane Young (2 months ago)
I don’t think you can. What are you trying to do?
Marielos Hernandez (2 months ago)
Hello, could you help me with something? i want to make an entrance in my app where i filter my data from excel with teo kind of filters, for example the start and end date i select and by clilent, so in my gallery the information showing is going to be refering to my filtrs and that the total if the products by client so that way i can just usea the formula sum to get a total, i hope i explain myself, thank you!
Shane Young (2 months ago)
Yes you can do that. Is it not working for you?
Akram Taelb (2 months ago)
can you helpe me how i can fllter data and display in formular
Shane Young (2 months ago)
What have you tried?
don erwin saunar (2 months ago)
Hi There, your videos are very helpful. Do you have any videos that shows how to save results of a gallery/collection after multiple filters, to be saved in sharepoint list? (details could have several rows, but can it be saved in just one sharepoint list record?
Shane Young (2 months ago)
To make it one record check out the concat function.
Tomáš Dang (3 months ago)
Hello, I have a case where I would appriciate your advice. I have a SHP list "Activities" and a list of "Orders", "Activities" info are lookuped to "Orders". By select+edit of an "Activities" record from the list my app runs in edit mode, goal is to have screen with gallery filtering "Orders" which are related to the selected record of "Activities" list. I tryied to do the same as you did but with SharpointForm instead of Gallery (Filter(Orders; Project_x0020_Activity1.Id = SharePointForm1_2.Selected.ID_OLD) ), I am having trouble with the condition, if I put manually an ID of an Activity instead "SharePointForm1_2.Selected.ID_OLD" it works. Thank you upfront
Tomáš Dang (3 months ago)
thank you, actually I found a workaround based on the TextInput I have set the Default property on DataCard1.Update which represents the value of the ID. This also alows to manually filter the galleries based on the textInput box if user needs to check different records.
Shane Young (3 months ago)
Forms don’t have a selected property. Replace form.selected.id with the name of the control that is showing the value you want. This video may also help. PowerApps SharePoint LookUp Columnhttps://youtu.be/43ekj5MlNJU
DNA iQ Sound Labs. (3 months ago)
How would Filter on Drop Down Box on form be performed. Using 2 columns one shows value some text(Homepage) column and if the Active Column equal to "Yes" then display only those lines to select from.
Shane Young (3 months ago)
Use the filter function in the items property of the drop down
Gopa Kumar (3 months ago)
Hi Shane New to PowerApps. Your videos are helping me a lot in learning the basics. Appreciate the sharing of knowledge.
Shane Young (3 months ago)
You are welcome.
Daniel Weikert (3 months ago)
Hi Shane, thanks a lot for your videos. Great teaching and very interesting. I got a question you might help with. So far I used an excel file from OneDrive/Sharepoint to create the PowerApp and also "write back" within the App to the file. Is it possible to do this write back into an on premise sql server? This would be really interesting to me. Thanks and best regards
Daniel Weikert (3 months ago)
For some reasons it tells me my credentials are wrong. Using my windows credentials. Are there common errors which often happen and could lead to this?
Shane Young (3 months ago)
Yes. This video Powerapps on premise data gateway https://youtu.be/uqx_h4Ym8ng
Phill Squire (3 months ago)
Hi Shane please help. I am trying to use nested gallery where the first gallery shows me the folders in a document lib on Sharepoint to which I have added a filter, so far so good. Now I want the second gallery to show me the contents of the folder based on the selection from gallery 1 Hope this makes sense
Shane Young (3 months ago)
It does make sense but I have never tried. Email me Shane at PowerApps911 and I will take a look next week.
prasad prasad (3 months ago)
Hi Shane, How to display HTML text (text containing HTML tags in Sharepoint List) in Display Form?
Garry Baker (4 months ago)
Just wanted to say thanks for these videos. They've been incredibly helpful for me on the job. (Please, I'm begging) Keep it up!
Shane Young (4 months ago)
I will if you promise to keep learning
Enzi Satine (4 months ago)
Hi Shane! this video is very helpful -thank you so much! However, I am having issues with my data source. I have a excel file with more than 10,000 records but PowerApps only shows 2,000 records when I do the filtering. What is your recommendation in this case? Thank you!
Shane Young (4 months ago)
You need to read up on Delegation in PowerApps. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview Long story short for that much info I think you will need to consider a different data source.
Blake Thomas (4 months ago)
Thanks for this! Is there a way to connect a gallery and a form on the same screen in the way you are connecting the two galleries? In my case I am using Power BI Data integration data plus a sharepoint list.
Shane Young (4 months ago)
Blake I think this is what you are looking for? PowerApps Repeating Tables like InfoPath Part 2 - View and edit the data https://youtu.be/DylxsXIUyDc
Benni Wößner (4 months ago)
Hey Shane could you help? Every Time I try to use the Filter function my powerapps doesnt recognize my Sharepointlist... Means for example: Filter(Test, Filterbox.Text = Title) doesnt work. Ther always stands no DataSource used so i think the problem is it doesnt recognize "Test" the Sharepoint List as a Database... Any idea?
Shane Young (4 months ago)
Have you added Test as a data source to your app? Can you view the list in a gallery? Oh... try flipping it around. Filter(test, title = field.text) better?
Craig Jenkins (4 months ago)
Great video again Shane.  Is there a way to created Sumifs in powerapps?  So if I wanted to do something like the following how would this be done.  Sum of column Days, where column Name = Name=User().FullName and column Status = Approved So in Excel it would be something like  =SUMIFS(H:H, G:G, "Approved", A:A, "Craig Jenkins")
Shane Young (4 months ago)
Hmmm. In my head it is something like this: Sum(Filter(datasource, Name=User().FullName and column Status = Approved), Days) That isn't working code but I think enough to get the wheels turning for you.
prasad prasad (4 months ago)
-Is it possible to have a message pop up only if it's a user's first time on the app?
Shane Young (4 months ago)
You would have to track it was their first time in a data source but then yes.
prasad prasad (4 months ago)
HI Shane, I don't want to see the scroll-bar on my screens. Is it possible to remove scroll bar on the datatable while displaying the contents?
prasad prasad (4 months ago)
Also i don't want to the headings in the table. Is it possible to remove headings for data table?
Anton Prinsloo (4 months ago)
Hi Shane, thanks for your videos. I have been struggling to get my search as well as my filter feature to work the way I want it to. In all the cases I tried, filter or search will only return results if I type in from the first sentence. So if I type Ro, I see "Robert", but if I type "bert", nothing comes up. I have tried duplicating your tutorials on Filter and Search, but no luck. Currently the BrowseGallery has the following SortByColumns(Filter('Standard Changes', StartsWith(Title, TextSearchBox1.Text), Status = "Approved"), "Title", If(SortDescending1, Descending, Ascending)) and textsearchbox1: Filter('Standard Changes', TextSearchBox1 in Title) What I need is when I type "bert", that all the strings with "bert" comes up, including "Robert" I would really appreciate your help. Many thanks Anton Edit: Finally I got it. Change the BrowseGallery to: Filter('Standard Changes', TextSearchBox1.Text in Title) solved the problem.
Shane Young (4 months ago)
Awesome! Glad you figured it out. :)
prasad prasad (4 months ago)
Hi Shane. I have images/icons in one of the column of my sharepoint list. How to display those images/icons in datatable of my screen using powerapps?
Shane Young (4 months ago)
Need to use an Image Control.
prasad prasad (5 months ago)
Hi Shane, I have a Data-Table taken from my SharePoint in which there are five columns. How to filter the table based on one column values? Scenario: Table name- Markers. Columns names- items,M/Q,Phase_x0020_Entering,..etc. I want to filter Phase_x0020_Entering column with "Concept" text in it. For Data-Table, I have given in items:- Filter('Markers',"Concept" = Phase_x0020_Entering) It was showing error on "=". Could you please help me solve?
prasad prasad (4 months ago)
Filter(Markers,"Concept" = Phase_x0020_Entering). Filter is working fine but with some Delegation warning. Thanks Shane.
Shane Young (5 months ago)
Are you sure that the column Phase_x0020_Entering contains Concept? That should work.
prasad prasad (5 months ago)
I also tried that. That isn't working. Even I tried with "in" in place of "=". None of them is working.
Shane Young (5 months ago)
What about the reverse? Filter('Markers',Phase_x0020_Entering = "Concept" )
Miguel Perez (5 months ago)
Thanks Shane, really enjoying your videos. How can I use "or" filter? You said that "," is "and", but I need "or" in order to filter by multiple columns.
Joerg (27 days ago)
I faced the same challenge ... SortByColumns(Filter('Report database', ( (StartsWith('Customer_name',TextSearchBox1.Text) || StartsWith('Customer_country',TextSearchBox1.Text)) || (StartsWith('Customer_city',TextSearchBox1.Text) || StartsWith('Key_Account_Manager_Name',TextSearchBox1.Text)) ) ) , "Report_x0020_date", If(SortDescending1, Descending, Ascending)) The magic with the blocks of brackets is as far as I know necessary, since || can only check two statements and not a row of more than two - so for many columns it will be brackets and brackets and brackets and brackets. At least it did not work without the brackets for me, with brackets it works. If anyone knows better, please let me know. And the beauty of it: all delegable - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list
Zak W (5 months ago)
Hey Shane. These vids are boss, thank you. Question: what about filtering with collections? Your examples worked well with SharePoint data, but didn't return any results when I swapped the data for a collection. Thoughts?
Arkadiusz Piekarski (5 months ago)
Thanks Shane. It would be great if You could always show the data used in your video at the begining. You used to do that :)
Shane Young (5 months ago)
You are right. Sorry, sometimes I get too excited and forget. Do you need info from this video? I can get it for you.
Cara Testing (5 months ago)
Hi Shane, Thank you for the wonderful guide as always. Quick question, how do you suggest that we replicate the same for date filters? I have tried many methods, but '>' or '<' signs are not recognised in powerapps date filters. Any guidance would be much appreciated. Cheers Pat
Cara Testing (5 months ago)
Tried following as well; SortByColumns(Filter(Events_table, To >= Today())) , "From" , Ascending) SortByColumns(Filter(Events_table, To >= TextInput5.Text)) , "From" , Ascending)
Cara Testing (5 months ago)
Hi, I tried the following; SortByColumns(Filter(Events_table, To.Text >= Today())) , "From" , Ascending) Also tried to to use the different text box with Today() as default. But nothing seems to work.
Shane Young (5 months ago)
What is the formulas you have tried?
xMDK (5 months ago)
Hi Shane How would you filter multiple columns at once(filter subfields and titles with the same search bar)?
Shane Young (5 months ago)
Filter can have multiple conditions. Filter(DataSource, Subfields = SearchInput.text, Title = SearchInput.Text) Something like that should work.
Sean Miller (6 months ago)
I am using the filter function to return entries from a large SharePoint list. I am trying to show text when no match is found from filter. Right now the result is just blank, I guess. But I want people to know that nothing was found and not that the app is not working.. any ideas guys? I have tried to add a text box that is visible when that gallery item is blank but that result is returned prior to the filter function. I want it seen if the result is blank not always when blank
Shane Young (6 months ago)
Use IsEmpty to see if what you are returning is blank. IsBlank is for checking something like a text field. IsEmpty is for checking a record/table.
Bill Kuhn (6 months ago)
Love your videos Shane!  Great instructional content, hitting major sought after content and perhaps best of all...getting right to the point on how to use these various features and configurations.  Well done!
Shane Young (6 months ago)
Thanks Bill. I try to make good content that isn’t boring. 😻
Alix Kashdan (6 months ago)
Hi Shane, your videos are super helpful! Is it possible to apply a search function and a filter function to the same gallery? I'd like to make it possible to search all columns, and also make it possible to filter by specific columns. I've gotten the search and filter functions to work separately, but I'm having trouble applying them both to a single gallery. Thanks!
Ziad Hamandi (6 months ago)
I am in the same predicament Shane. I need to search for a specific text but display the result filtered by those records greater or equal to today.
prasad prasad (6 months ago)
Hi Shane, Suppose I need to use only one DataTable to display from SharePoint. But i have to display only two columns out of 8 columns based on selection of buttons. My Scenario: If I select "customer" button i have to display "customer" column and "Title"Column. If I select "Product" button i have to display "product" column and "Title"Column.....Similarly for other columns too. The thing is I have to make use of single data table to display only two columns out of 8 columns based on button selection
Shane Young (6 months ago)
I relied to Prasad in email but in case anyone else is wondering what I would do is use multiple data tables on top of each other. Then only make the one visible that shows the columns I want to show.
Tim Truax (6 months ago)
You have been very helpful in getting me up to speed on PowerApps. Thanks! Shane. A quick question: How do I know when to put something in quotes? IOW, what kinds of things must be in quotes, and when is the name of the object (sans quotes) the right syntax?
Shane Young (6 months ago)
Text is always in quotes. Sometimes column names inside of functions are in quotes. (Like SortByColumns and Search). I don't know I have a solid answer here. You Just have to kind of figure it out. The best way is if you look at what the formula is suggesting in the dropdown as you type that is a hint. Sorry for a weak answer.
Katherine Shea (6 months ago)
Hi Shane, thanks for these amazing videos, we love them! We're trying to build an ap that lets a user find an entry (row) based on a variety of criteria (columns) the user could chose from 3 different drop down menus to find entries by country, by contact or by category. What we can't figure out is how to make one single gallery combine the search results of each box. (For example choosing all entries where Sean is the contact that refer to the country Peru.) We can make this work using search or filter for either of the fields, but we can't get the gallery to draw from multiple fields simultaneously. Can you explain how to do that? (maybe your next video :))
Katherine Shea (6 months ago)
Thank you so much! I'll watch your video about collections (I've only just read about them) and I'll follow up if I have questions!
Shane Young (6 months ago)
Hi. Yeah that one is tricky. What I did last time to solve that was put the whole data set in a collection. Then I filter the collection each time you input a search criteria.
Wesley van der Blom (7 months ago)
Great instruction. Got it working after some ‘trail and error’ with CDS database. Your video helped a great deal!
Shane Young (7 months ago)
Very cool. I haven't tried it with CDS yet.
mfsalazar1 (7 months ago)
great video! tnkx, but can i do if i want to have questions with multi filter like this case but not simple list, i mean i want to answer questions like select a choise between 1 to 4
Shane Young (7 months ago)
Not sure I excatly follow. You can use And and Or functions if you wanted to in your filter or If. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-logicals
Michael Buckingham (7 months ago)
Good Shane! I think I can leverage this on a solution I am moving from on premise Nintex to online PowerApps/Flow. Our major use case that is still on InfoPath is for forms that are filled out offline and submitted via email. Is that something that PowerApps could support?
Shane Young (7 months ago)
Hey Michael - PowerApps can go offline but I don't have a way for them to send an email if they are offline.
Shutyourjibbs (7 months ago)
Shane, I've tried multiple times to do exactly what you did on this video, but it keeps giving me multiple yellow triangles that tell me it creates a circular reference. What am I doing wrong?
Shane Young (7 months ago)
Email me some screenshots and I will see if I can help shane at PowerApps911.com
Shutyourjibbs (7 months ago)
Same result.
Shutyourjibbs (7 months ago)
I changed the name of the text box to FilterTextBox1, and it updated the name in the formula, but it didn't change anything.
Shane Young (7 months ago)
Try Filter(‘Tool Wish List’, “Shane” = EmployeeName)
Shutyourjibbs (7 months ago)
That was the name I gave to that field.
Miryam NN (7 months ago)
Awesome!!  ....
Shane Young (7 months ago)
You are awesome! Thanks
Heather Wurtz (7 months ago)
I'm looking to filter a SharePoint list based on tags added from a DropDown (in that list). I feel like I'm on the right track using nested galleries, but How do I generate the tag list (DropDown in SharePoint- basically a nested table) as its own Gallery. The complexity of it precludes me from selecting it as an item, but it lives in the same list as the data I intend to filter.
Sam Weber (7 months ago)
I am curious as how to do this too Shane... Can you explain? I need to basically create broad topics/headers that live on the front of the app, when a user clicks one of the topics, they are directed to just that data that it is tagged too... (i.e., if I click on the Jelly topic header, I only get data pertaining or tagged to jelly)
Heather Wurtz (7 months ago)
I've tried every variant I can think of and nothing is working. My Gallery is blank every time. The SharePoint List I have a three column SharePoint list named "QA app". Column 1 = "Question", Column 2 = "Answer" The third column (Column Name = "Tags") is a drop-down with multi-selection functionality. The App On the Edit screen, a user can type in a Question and an Answer, then Tag it with predetermined meta tags to categorize the content- this works perfectly! I'm attempting to add a filter function wherein the user can filter the gallery ('QA app') using a combobox (FilterBox) which is populated with the options defined in the "Tags" column (Choices('QA app'.Tags)). This is the closest thing I've found to a solution, but no data pulls into my Gallery. Filter('QA app', Tags in FilterBox.SelectedItems.Value) Perhaps it should be noted that The only data visible in my gallery is "Question" and "Answer". The "Tag" data is only visible on the edit page and in the SharePoint List.
Heather Wurtz (7 months ago)
Thanks! That got the data for the combo box from SharePoint for me. Now I just have to use the selections to sort a Gallery. I can’t believe this is so complicated... it’s just filtering :/
Shane Young (7 months ago)
PowerApps Patch Function https://youtu.be/gX5q_V3U8-s It is in this video. Whoops. Close to the beginning.
Shane Young (7 months ago)
It is past my bedtime. 😻 I think you want the choices function and I think I show how to use it in this video PowerApps SharePoint LookUp Column https://youtu.be/43ekj5MlNJU if not that one then the SharePoint patch video. If you cannot figure it out I will help in the morning.
Eric Thomas (8 months ago)
Hi there Shane. Great videos. Thank you. I would love to see the power of Lookup, Filter, Sort, SortByColumns, and GroupBy all combined to manipulate Gallery views against Sharepoint lists. Its a tough road without someone showing off examples.
Eric Thomas (7 months ago)
How to combine LookUp, SortByColumns, CountIf, Distinct, and Filter to pull records into galleries would be AWESOME.
Eric Thomas (8 months ago)
Sure. Here is a complex example of needing Sort/Filter/Lookup combinations: https://powerusers.microsoft.com/t5/General-Discussion/Complex-Sort-Filter-LookUp-function/m-p/105053#M38425
Shane Young (8 months ago)
Happy to help. Can you give me a more exact idea of what you want to see?
Al-Sabea Mustafa (8 months ago)
you know , I am so so happy to see this video , so easy so Genius , i am really love it i wish you explain sortbycolumn with the filter function.thanks man
AJ Fleet (8 months ago)
Hi Shane, I just wanted to say thanks for the great videos. I am currently forming some apps for our business in my spare time and your videos are really easy to understand. I will inevitably end up getting a proper engineer on the job but for the time being your input has been invaluable. Take it easy
Shane Young (8 months ago)
Awesome. Keep up the good work. I will do my best to make enough videos you can do it all yourself. Also, I will mention if you need help I am always available for consulting. ;)
Dara Mandara (8 months ago)
Hi Shane,,, it's me again :) i get every thing in place in my app, but there are still things stopping from using the app, i hope you can help me out with them. i searched the internet and the forum and didn't figure out how to fix it: - how can i get the value from (drop down, toggle....) to be filled in a cardvalue (excel cell in my case) for example, if the dropdown values is "Yes", then fill this cell with "Yes". - datepicker "again", whrnever i move from screen it goes back to another value, so i want it to stay the same date i choose, and then to fill that date again in an excel cell. - adding an image is a headache for me, how can i add an image that will stay in the same card of the client, and can access it whenever i need, (i'm connecting by a dropbox) thank you in advance
Dara Mandara (8 months ago)
thanks for reply. will follow your channel for more tips and videos.
Shane Young (8 months ago)
1. Dropdown1.Selected.Value would be the text value of the input. 2. Try writing the date to a variable so you don't lose it. 3. I need to cover images in a future video
Marlon Herry (8 months ago)
Another great video Shane. I would really like to see a parent-child and a parent-child-grandchild application where you are not just pulling data but writing back data to SharePoint list. Say a Purchase Requisition (PR) or Purchase Order (PO) system with a PR/PO header, PR/PR Line items and some of the items may have lines of details attached to them. For the Child/Grandchild form can you edit multiple lines in one screen/form then post back/save all the items back to the data source with a link to the parent.
Shane Young (6 months ago)
I think this is the video you are looking for. :) https://www.youtube.com/watch?v=43ekj5MlNJU
Mary Lou Farnum (6 months ago)
I'm with Marlon, Shane. Please move this up on your list of videos to make.
Shane Young (8 months ago)
We could do something like that. I will put it on the list but, it might be a while. The list is getting long. :)
Andy Tuke (8 months ago)
Hi Shane, with regards to delegation. If you have use a function that starts filtering, and is able to delegate, enclosed in another function that isn't able to delegate do you think the filter returns < 500 items to the client which then applies the outer function. Or does the delegation try to pass the entire function group to the server, and fail to delegate?
Paul O'Flaherty (8 months ago)
I seem to recall the documentation says that if any part of a formula is not delegable, then none of it is delegated. But when I experimented it appeared that if the *inner* functions are delegable then they are processed first (with delegation) before the outer (non-delegable) functions run. I have a video where I put a GroupBy (non-delegable) around a SortByColumns (delegable) and the SortByColumns *is* still delegated - check around the 23 minute mark: https://www.youtube.com/watch?v=eiZ9_EWSuBk&t=172s Tip: when testing delegation you can now set the (normally 500) 'Data row limit for non-delegable queries' to a lower level so it is more apparent what is being delegated and what isn't (go to File, App Settings, Experimental Features).
Shane Young (8 months ago)
I have an answer off the top of my head but I want to test it before I put it in writing. :) (I think the whole thing goes south if any part isn't delegatable.)
Paul O'Flaherty (8 months ago)
Thanks Shane, really enjoying your videos. You mentioned that when you are building new applications for clients you are using SharePoint lists (without lookups)... but for a new application why not use Azure SQL DB? While there is an additional cost, it is quite low ($5 / month for the lowest tier) and you get a lot of extra functionality (more delegation, ability to use Views, better scalability, backup and so on). Are your clients reluctant to go the Azure SQL DB route? Do you find other advantages to SharePoint lists? I'd be interested to hear your thoughts/experiences.
Shane Young (8 months ago)
I do need to cover CDS. Thanks for the nudge.
Paul Haley (8 months ago)
Hi Shane, If you do a SQL/SharePoint comparison could you include some info on the Common Data Service and where it fits in.  It seems to have a lot of potential but I am confused as to how to use it.
Shane Young (8 months ago)
Bummer that your "proper job" isn't embracing PowerApps. It is the go-to for Access Web Apps migration. I am actually doing that exact project with a client this week. Fun. :)
Paul O'Flaherty (8 months ago)
Hi Shane I’m using PowerApps to develop applications to help my wife run a few small businesses she owns/manages (and to give me some real-world projects to get my teeth into!) so it is my call what platforms we use. I started experimenting with SharePoint list/library-based solutions and found them OK if the data model is very simple and the volume of data relatively small, but for anything with a degree of complexity to the data model or where more than a few dozen records are created per week I use Azure SQL DB for the data-source. I also find it quicker and easier to develop in Sql Server/PowerApps rather than SharePoint/PowerApps (due to delegation and views) but then I'm a SQL developer by background so perhaps somewhat biased :) IMHO the cost (developer hours) of building with SharePoint lists first then later migrating to SQL is likely much higher than just building in SQL in the first place (and paying $5 a month for Azure), so if there is any chance a solution is going to cross the complexity/capacity threshold now or in the foreseeable future, I’d recommend going with SQL generally and Azure specifically due to low cost and easy maintainability (for the sort of apps we’re looking at, you can rely on the inbuilt maintenance, no need for a DBA). In my ‘proper job’ we are looking at migrating an Access Web App to another platform, but this is a corporate giant and although they do have Office 365, unfortunately Azure SQL DB and PowerApps are not approved/available there yet.
Shane Young (8 months ago)
Hi Paul. That is an outstanding question and I think my answer is a reflection of where the world is at with PowerApps right now more than what is absolutely correct. Right now I am finding that most customers have an existing solution that in some capacity revolves around SharePoint. So for their first step into PowerApps, they are preferring to keep the data in SharePoint and just extend its capabilities with a PowerApps App. They feel good that they and other (often less technical users) are more comfortable when all of the data they are working with is in SharePoint. The App has primarily been to collect more data for SharePoint. For some, I have recommended SQL as a backend (not even talking about whether it is Azure or not) and they don't have any interest. I think this will change when we get to a point people want to build new solutions with PowerApps instead of augmenting existing processes. What are you seeing? Do you have similar experiences? I would love your thoughts both on where you are at and what you are seeing. Thanks -Shane PS - May turn this into a topic for my Podcast next week. :)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.