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

[NickK]

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)

[NickK]

just follow up (bump). 

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

Thanks so much, Nick

[v-xida-msft · Accepted solution]

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