Sort/Search issues with lookup column from Sharepoint List

[jwim]

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))

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))

Same Issue for Project Status.

[Shanescows]

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

[jwim]

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

[Shanescows]

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? 

[jwim]

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

[Shanescows]

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))

[jwim]

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

[Shanescows · Accepted solution]

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.

[jwim]

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

Jason

[jwim]

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