Calculate the distance between 2 locations (latitude & Latitude) using BingMaps.GetRouteV2

I am working on a Power Apps canvas application, an I have a sharepoint Asset list with latitude & Latitude and i am getting a single asset using this formula:-

Set(relatedAsset,LookUp(Assets,ID=LookUp('Asset Faces',ID= LookUp('ticket Details',ID=If(IsBlank(varticketID),varNavigationID,varticketID)).'Asset Face ID').'Asset ID'));

then i am getting the latitude & Latitude of the current technicians from SharePoint list as follow:-

ClearCollect(colRelatedticketTechnicianLocation,Filter('Technicians Locations','ticket ID'=If(IsBlank(varticketID),varNavigationID,varticketID)));

add a column to the collection:-

AddColumns(colRelatedticketTechnicianLocation,"Distance",0);

then i am trying to Forall the items inside the collection and calculate the distance between the Asset and the Technician:-

ForAll(colRelatedticketTechnicianLocation As ticketLocation,

Patch(colRelatedticketTechnicianLocation,LookUp(colRelatedticketTechnicianLocation,ID=ticketLocation.ID),{Distance: BingMaps.GetRouteV2()});
Blank()
)
 

but currently i am facing these 2 issues:-

1. i am not sure how to pass the source and distention latitude & Latitude inside the BingMaps.GetRouteV2 method?

2. inside the final statement , i am getting that it can not find a column named Distance, although i added it using the Addcolumns, any advice?

Thanks

I can't speak to BingMaps.GetRouteV2, but there is a problem with the way you are adding the Distance column. A Collection retains its column names even when all rows have been removed. That means that, after you run:

AddColumns(colRelatedticketTechnicianLocation,"Distance",0);

a single time, the collection colRelatedticketTechnicianLocation will have a column named Distance from then on. Even when you run clear() or clearCollect(), the column names remain.

That means that, when you then try to run AddColumns() again, you should be getting some sort of error there. I honestly forget how you can completely remove those column names. Maybe by removing all references to that collection from your app, running the app, it and then re-adding the references.

BUT... here is the right way to do it: add the column in the same step as filtering 'Technicians Locations' and clearCollect.

Replace this:

ClearCollect(colRelatedticketTechnicianLocation,Filter('Technicians Locations','ticket ID'=If(IsBlank(varticketID),varNavigationID,varticketID)));

With this:

ClearCollect(colRelatedticketTechnicianLocation,AddColumns(Filter('Technicians Locations','ticket ID'=If(IsBlank(varticketID),varNavigationID,varticketID))),"Distance",0);

I know this makes the code more cumbersome, but you are now ensuring that colRelatedticketTechnicianLocation always has a "Distance" column the way you want it to. But, since you are "adding the column" to a temporary table (i.e., the results from the Filter() function BEFORE it is adding to the collection), you KNOW that you are starting with a table that DOES NOT have a "Distance" column and ending with a table that DOES have a "Distance" column.

As a note, you could also combine the distance calculation into the same step by not giving the Distance column value 0 but rather the actual formula, like this:

ClearCollect(colRelatedticketTechnicianLocation,AddColumns(Filter('Technicians Locations','ticket ID'=If(IsBlank(varticketID),varNavigationID,varticketID))),"Distance",BingMaps.GetRouteV2());

It is entirely up to you if this is better or worse. It makes the code shorter, and you never end up with a 0 distance value, which may be misleading, but it also makes the code more cumbersome. It would also, however, combine a thing which is currently not working (the Bing call) with a step that should be straightforward. Again, it's your call.