cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwim
Helper I
Helper I

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

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

12 REPLIES 12
Shanescows
Super User
Super User

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

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

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

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

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

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

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

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

 

Jason

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (3,936)