Sort Gallery issue

[jsmith87]

Good morning, 

I hope you can help. I've created a Power app to allow people to select a region they're in then a trade they require. In my gallery There a label which calculates the distance between your region and the filtered trade you require.

Formula is:

BingMaps.GetRouteV3("Driving",Dropdown4_1.Selected.Region,end1.Text,{distanceUnit:"Mile"}).travelDistance

In my gallery i have the below filter:

Filter(Table9,Trade = Dropdown3_1.Selected.Trade)

It works perfectly but I'd like it to automatically sort the companies by distance in ascending order, or a button to sort. Is this possible? If so, could someone tell me the best way to do this? Obviously the formula is direct in PowerApps there's no column to reference. 

[SebS]

Hi @jsmith87 

Can you share Columns names in table 9 please

[jsmith87]

Hi, 

Just tried and it said i had some invalid arguments:

Sort(Filter(Table9,Trade = Dropdown3_1.Selected.Trade),Mileage,Ascending)

I also tried replacing travelDistance with Mileage which is the cell with the formula below in it, that i want to sort in ascending order. 

Mileage is the label i've used for the distance formula:

BingMaps.GetRouteV3("Driving",Dropdown4_1.Selected.Region,end1.Text,{distanceUnit:"Mile"}).travelDistance

[SebS]

@jsmith87 

You can't sort it by the label. It needs to be a column from Your table. What stores the data?

That's why I asked about the columns. Your table got, but I think you store only cities there, right, and nothing You could sort this with.

[jsmith87]

Hi, In my table I have Trade, Company name, postcode and contact details. I'm super new to this so appreciate the help. Would i need to make a patch? that will record the data into a new column which i could then sort by?

[SebS]

@jsmith87 

From what I see, the best way and simplest is to just create a column TravelDistance and flush it with the distance to each location, then sort it by that column

But this then will be impossible if You want to allow users to pick a postcode as there will be many to handle

There is a Scenario where You just picking from dropdown and You have fix locations and Fix outcome

and there is scenario where You just using Start as Postcode and finish as Location what is to many variables to generate due to the number of postcodes

[jsmith87]

Ok, in my table i have about 2500 contractors. and in my regions i have 128. If i was to give fixed outcomes it would be 320,000 lines long in my table, as  I'd need to work the distance for each contractor against the 128 regions. sounds like there may not be a way around it. 

[SebS]

@jsmith87 

now I uderstand.

If the outcome of filter is only 127 or less than 2000 you could use addColumn rather label and than sort all by that column when I get 10 min I will try to write You a formula for this scenario and rather insert the formula in the label You will just refer label to that created column.

[SebS]

@jsmith87 

For now, Try this Code

Sort(
  Filter(
            AddColumns(   
               Table9,
               "Distance",
               BingMaps.GetRouteV3("Driving",Dropdown4_1.Selected.Region,end1.Text,{distanceUnit:"Mile"}).travelDistance
        ),
            Trade = Dropdown3_1.Selected.Trade)
         ),
         Distance,
         Ascending
     )

In Gallery You should be able to create a label and refer to it ThisItem.Distance to show Miles

[jsmith87]

Thanks, just tried it and unfortunately it didn't work. I'm using a static excel for my data source as I have 2500 different contractors and didn't want to be limited by the 500/2000 rule. Could this be the issue?

Once you select trade it will filter the results in the gallery for that trade for example "plumbers"  you'll get around 100, "carpenters" the gallery will change again and you'll have around 80. Once you pick your region (Newcastle for example) from the dropdown then the formula in the Label will calculate that contractors distance to you.  

BingMaps.GetRouteV3("Driving",Dropdown4_1.Selected.Region,end1.Text,{distanceUnit:"Mile"}).travelDistance

is there a way to ammend that formula so it can write back to my excel spreadsheet? i can then use that column to sort distance? however, will that record be replaced when someone searches for that skillset for another region?

Really appreciate your help, it's the last piece to get this app functioning, without having an ascending list to pick the closest contractor it wont really work well