cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AW
Advocate I
Advocate I

Filter by one table, sort by another

Stumped on something that may be easier than I'm realizing...

  • I have 2 Sharepoint data sources (LocationList and SalesData)
    (SalesData uses the LocationList site column to choose the Location for the sales information)
  • I have a filter set based on the location area in a dropdown field pulled from the LocationList
  • I need to then sort the results by the SalesData.Rank field

Currently, my query is:

Sort(Filter(LocationList, Area = AreaChoice.Selected.Result),Location,Ascending)

I used Location to complete the query and it works since it's sorting from the same LocationList table. I can't figure out how to incorporate the sort by Rank which is in the SalesData table. 

Any help is appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi@AW,

Do you want to filter the LocationList based on the Area dropdown selected and sort the SalesData by Rank combined with the filtered result before?

Since you have a Area relationship between LocationList and SalesData, I strongly recommend you create a LookUp column in the SalesData to retrieve area choices from the LocationList.

I have a test on my side, please take a try as below.

If you do not want to have a LookUp column to connect LocationList and SalesData, try as follows:

1).create a Gallery to filled with the 'SalesData' 

2).create a form to filled with the 'SalesData' and set the Item as below:

 

Gallery1.Selected

 

3).add a Dropdown and set Items as below:

 

Distinct(LocationList,Area)

 

4).set the Default property of the TextInput corresponding to the location column in the 'SalesData' within the form as below:

 

Dropdown1.Selected.Result

 

5). add a submit button and set OnSelect as below:

 

SubmitForm(Form1)

 

Once you select the item in the Gallery, it will navigates you to the form to make you pick the area choices for the location column, and then click the submit button to save this location value to the 'SalesData'.

6). set the Items of the Gallery to incorporate the sort by Rank which is in the SalesData table as below:

 

Sort(SalesData,Rank,Ascending)

 

GIF001.gif

Hope it could help.

Regards,

Qi

View solution in original post

2 REPLIES 2
Drrickryp
Super User II
Super User II

Hi @AW 

I think you could use AddColumns() to lookup the site and reference the rank from the Sales list if there is a common field in both lists.  Then you could sort the list on the new field (salesrank).  Perhaps something like this.

Sort(
      AddColumns(
                   Filter(
                           LocationList, Area = AreaChoice.Selected.Result
                   ),"SalesRank", Lookup(
                                          SalesData,LocationID=ThisItem.ID, Rank
                                   )
       ),SalesRank, Ascending
)

I'm not sure of the LocationID=ThisItem.ID part but that would be the link between the two lists. 

v-qiaqi-msft
Community Support
Community Support

Hi@AW,

Do you want to filter the LocationList based on the Area dropdown selected and sort the SalesData by Rank combined with the filtered result before?

Since you have a Area relationship between LocationList and SalesData, I strongly recommend you create a LookUp column in the SalesData to retrieve area choices from the LocationList.

I have a test on my side, please take a try as below.

If you do not want to have a LookUp column to connect LocationList and SalesData, try as follows:

1).create a Gallery to filled with the 'SalesData' 

2).create a form to filled with the 'SalesData' and set the Item as below:

 

Gallery1.Selected

 

3).add a Dropdown and set Items as below:

 

Distinct(LocationList,Area)

 

4).set the Default property of the TextInput corresponding to the location column in the 'SalesData' within the form as below:

 

Dropdown1.Selected.Result

 

5). add a submit button and set OnSelect as below:

 

SubmitForm(Form1)

 

Once you select the item in the Gallery, it will navigates you to the form to make you pick the area choices for the location column, and then click the submit button to save this location value to the 'SalesData'.

6). set the Items of the Gallery to incorporate the sort by Rank which is in the SalesData table as below:

 

Sort(SalesData,Rank,Ascending)

 

GIF001.gif

Hope it could help.

Regards,

Qi

View solution in original post

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

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (63,837)