cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jwim
Level: Powered On

Sort/Search issues with lookup column from Sharepoint List

I'm new to Power Apps and have spent too many hours trying to figure this out and need some help. I've created 3 apps: Project Establishment, Set Project Milestones and Project Status that are connected to Sharepoint Lists. Someone will establish a Project in Project Establishment, selecting a site and a program (along with a few other fields). The Site and Program are then combined to create a unique Site Program column to differentiate between projects. I've got sort and search working in this apps BrowseScreen's Gallery. I use the below formula to do the sorting on the gallery.

 

SortByColumns(Filter('Project Establishment', StartsWith(Site_x0020_Program, TextSearchBox1.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

Project Establishment.png

 

Once a project is established, a user will  go into the Set Project Milestones app and click a plus sign button to create a new line item inthe Set Project Milestones SP List, with the user only being able to select in a dropdown menu from the Site Program column that was created in the first app. So the column in the SP list is a lookup to the initial Project Establishment list. This works great, but on the BrowseScreen Gallery, the order of my Site Programs is based on the latest one created, not descending. Initially in the Gallery's Data/Items field, I have 'Set Project Milestones', which is the order of the latest created. If I try to use a similar formula as in the Project Establishment app, the gallery list goes blank. I think it has something to do with the Site Program column being a lookup column in the Set Project Milestones list, but I do not know what to adjust it too. The search and sort functionality doesn't work either. Below is the formula that doesn't work but I think is close.

 

SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program, TextSearchBox1.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

Set Project Milestones.png

 

Same Issue for Project Status.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sort/Search issues with lookup column from Sharepoint List

Does this work?

 

Items = Sort(Filter('Set Project Milestones', StartsWith(Site_x0020_Program.Value, TextSearchBox2.Text)), Site_x0020_Program.Value, If(SortDescending1, Descending, Ascending))

 

I changed SortByColumn to Sort.

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Sort/Search issues with lookup column from Sharepoint List

This line doesn't work:

 

SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program, TextSearchBox1.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

Is the issue that on this screen your search box is not TextSearchBox1? Since that is the name of the control on the first screen I am guessing on this screen it is TextSearchBox2? 

 

So you should be 

 

SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program, TextSearchBox2.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

Does that make sense? Does it help?

 

Shane

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
jwim
Level: Powered On

Re: Sort/Search issues with lookup column from Sharepoint List

Unfortunately that doesn't resolve the issue. They're completely different Apps not just different screens inside of one app, so the TextSearchBox name shouldn't matter. For simplicity sake I did change it to TextSearchBox2 on the second app to differentiate it a bit. So:

 

This works for the first app:

Items = SortByColumns(Filter('Project Establishment', StartsWith(Site_x0020_Program, TextSearchBox1.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

This works for the second app:

Items = 'Set Project Milestones' ....... but that doesn't sort the list of Site Programs, or allow me to search.

 

This does not work for the second app:

Items = SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program, TextSearchBox2.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

When I try to use the above formula, my data source changes to "No Data" and my Title2 is Site_x0020_Program.Value. I have to believe it has to be something with the Site_x0020_Program column (it tells me I cannot sort on the expression type). 

 

Maybe instead of 3 apps, I should have extra screens in the first app? Just a lot of work since I haven't found a way to copy a screen from one app to another.

 

Jason

Super User
Super User

Re: Sort/Search issues with lookup column from Sharepoint List

Sorry, I should have opened the app the first time. Okay. The issue is you need the value of your lookup. Site_x0020_Program is a lookup column, correct? If so you need .Value at the end. Lookup columns are annoying. 🙂 

 

Items = SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program.Value, TextSearchBox2.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

All better? 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
jwim
Level: Powered On

Re: Sort/Search issues with lookup column from Sharepoint List

The yellow warning icon changes to a blue info icon (states "Part of this StartsWith formula cannot be evaluated remotely due to service limitations. The local evaluation may produce suboptimal or partial results. IF possible, please simplify the formula"), which is a step in the right direction, but the BrowseGallery2 is still blank.

 

Formula looks like this:

Items = SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program.Value, TextSearchBox2.Text)), "Site_x0020_Program", If(SortDescending1, Descending, Ascending))

 

Current Error: The second Site_x0200_Program is still showing as the error in the formula (The function 'SortBy Columns' has some invalid arguments and Cannot sort on the expression type)

 

Jason

Super User
Super User

Re: Sort/Search issues with lookup column from Sharepoint List

Sorry, in my test I sorted by a different column. I am looking to see if I can get it to sort by the lookup column, so far I cannot. Try a different column just to see if that gets you further. 

 

Items = SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program.Value, TextSearchBox2.Text)), "DifferentColumnHere", If(SortDescending1, Descending, Ascending))

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
jwim
Level: Powered On

Re: Sort/Search issues with lookup column from Sharepoint List

Getting closer. If I sort by the Milestone 1 Date column, it will sort by the date of that column. (Search also works).

 

SortByColumns(Filter('Set Project Milestones', StartsWith(Site_x0020_Program.Value, TextSearchBox2.Text)), "Milestone 1 Date", If(SortDescending1, Descending, Ascending))

 

So it comes down to how to sort from a lookup column that was created in the Project Establishment app, but i'm trying to pull it from the Set Project Milestone app. I've tried to just use Project Establishment as the data source, but then none of the milestones show up in the detail screen after creating in the edit screen.

 

Do you think creating a formula column in the Set Project Milestones column based off of Site Program would allow a non-lookup column to be sorted by. Not sure if you run into the same issue when sorting a column that is formula based.

 

Jason

Super User
Super User

Re: Sort/Search issues with lookup column from Sharepoint List

Does this work?

 

Items = Sort(Filter('Set Project Milestones', StartsWith(Site_x0020_Program.Value, TextSearchBox2.Text)), Site_x0020_Program.Value, If(SortDescending1, Descending, Ascending))

 

I changed SortByColumn to Sort.

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

jwim
Level: Powered On

Re: Sort/Search issues with lookup column from Sharepoint List

Bingo! I should have asked for help a long time ago. Thanks for the help!

 

Jason

jwim
Level: Powered On

Re: Sort/Search issues with lookup column from Sharepoint List

You would think that the 3rd app would be similar to the second, but nope. Exact same setup, blank Gallery again.

 

Sort(Filter('Project Status', StartsWith(Site_x0020_Program.Value, TextSearchBox3.Text)), Site_x0020_Program.Value, If(SortDescending1,Descending,Ascending))

 

I'm getting an error on the SortDescending1 for some reason.

 

Jason

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,812)