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

Sort gallery by closest location after search (is it possible?)

Hello, 

 

My app lists retail stores under our brand, currently my gallery filter uses Search and SortbyColumns. I would like to sort the gallery list by stores that are closest to the user (the device location). I was able to create a label that displays the KM distance in the gallery list item (Code #1). My trouble is getting the gallery filter to sort by that distance. I've tried nesting the "AddColumns" function to try and append the distance to the main table (Table1), the table comes from a static Excel data source which I think might be the problem. 

 

Any help will be greatly appreciated, even a completely different approach to acheive this. 

 

Thanks, Nick

 

 

Code #1 - working km distance label inside gallery list items

ACOS(Cos(Radians(90-DeviceLat))*Cos(Radians(90-ThisItem.Latitude))+Sin(Radians(90-DeviceLat))*Sin(Radians(90-ThisItem.Latitude))*Cos(Radians(DeviceLong-ThisItem.Longitude)))*6371

Code #2 - current working gallery filter

SortByColumns(Search(Table1,TextInput1.Text,"Address","City","Mall Name","Location / Dealer Name (Primary)","DBM/AM"),"Location / Dealer Name (Primary)")

Code #3 - not-working gallery filter where I try to sort gallery list items by distance instead of dealer/location name

SortByColumns(AddColumns('Table1',"Distance",Acos(Cos(Radians(90-DeviceLat))*Cos(Radians(90-Latitude))+Sin(Radians(90-DeviceLat))*Sin(Radians(90-Latitude))*Cos(Radians(DeviceLong-Longitude)))*6371),Search(Table1,TextInput1.Text,"Address","City","Mall Name","Location / Dealer Name (Primary)","DBM/AM"),"Distance",Ascending)
1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @NickK,

Based on the Code # 3 formula that you provided, I think there is something wrong with it.

Please take a try to modify your formula within the Items property of the Gallery control as below:

 

SortByColumns(
      Search(
          AddColumns(
Table1,
"Distance",
ACOS(Cos(Radians(90-DeviceLat))*Cos(Radians(90-ThisItem.Latitude))+Sin(Radians(90-DeviceLat))*Sin(Radians(90-ThisItem.Latitude))*Cos(Radians(DeviceLong-ThisItem.Longitude)))*6371
), TextInput1.Text, "Address","City","Mall Name","Location / Dealer Name (Primary)","DBM/AM" ), "Distance",
Ascending )

Or

Set the OnVisible property of the first screen of your app to following:

 

ClearCollect(
   RecordsCollection,
   AddColumns(
        Table1, 
        "Distance",
        ACOS(Cos(Radians(90-DeviceLat))*Cos(Radians(90-ThisItem.Latitude))+Sin(Radians(90-DeviceLat))*Sin(Radians(90-ThisItem.Latitude))*Cos(Radians(DeviceLong-ThisItem.Longitude)))*6371
   )
)

Set the Items property of the Gallery control to following:

 

SortByColumns(
       Search(RecordsCollection, TextInput1.Text,"Address","City","Mall Name","Location / Dealer Name (Primary)","DBM/AM,
       "Distance",
        Ascending
)

check if the issue is solved.

If the issue still exists, please consider take a try to save your Excel file (contains the Excel table) into your OneDrive folder, then re-create a connection to the Excel table from your app, try above formula again to check if the issue is solved.

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
NickK
Helper I
Helper I

just follow up (bump). 

 

Has anyone else tried doing something like this or have a solution?

 

Thanks so much, Nick

v-xida-msft
Community Support
Community Support

Hi @NickK,

Based on the Code # 3 formula that you provided, I think there is something wrong with it.

Please take a try to modify your formula within the Items property of the Gallery control as below:

 

SortByColumns(
      Search(
          AddColumns(
Table1,
"Distance",
ACOS(Cos(Radians(90-DeviceLat))*Cos(Radians(90-ThisItem.Latitude))+Sin(Radians(90-DeviceLat))*Sin(Radians(90-ThisItem.Latitude))*Cos(Radians(DeviceLong-ThisItem.Longitude)))*6371
), TextInput1.Text, "Address","City","Mall Name","Location / Dealer Name (Primary)","DBM/AM" ), "Distance",
Ascending )

Or

Set the OnVisible property of the first screen of your app to following:

 

ClearCollect(
   RecordsCollection,
   AddColumns(
        Table1, 
        "Distance",
        ACOS(Cos(Radians(90-DeviceLat))*Cos(Radians(90-ThisItem.Latitude))+Sin(Radians(90-DeviceLat))*Sin(Radians(90-ThisItem.Latitude))*Cos(Radians(DeviceLong-ThisItem.Longitude)))*6371
   )
)

Set the Items property of the Gallery control to following:

 

SortByColumns(
       Search(RecordsCollection, TextInput1.Text,"Address","City","Mall Name","Location / Dealer Name (Primary)","DBM/AM,
       "Distance",
        Ascending
)

check if the issue is solved.

If the issue still exists, please consider take a try to save your Excel file (contains the Excel table) into your OneDrive folder, then re-create a connection to the Excel table from your app, try above formula again to check if the issue is solved.

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

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

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,076)