0

I am trying to either use have DISTANCE as a column or Sort by DISTANCE. But the field I need to look at is at Parent level. FOr ORDERING, I got Internal Salesforce.com Error.

List<AccountContactRelation> accConList = [SELECT Id, Account.BilingAddress FROM AccountCOntactRelation WHERE DISTANCE(Account.BillingAddress, GEOLOCATION(:geoLatitude, :geoLongitude), 'mi') <:miles ORDER BY DISTANCE(Account.BillingAddress, GEOLOCATION(:lat, :lon), \'mi\') ASC]

I found this documentation where it says you need to individually bring latitude, longitude fields to get from parent to be used in DISTANCE, but I am not sure how to use that in SOQL. https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/compound_fields_limitations.htm

1 Answer 1

0

Unfortunately DISTANCE (and possibly GEOLOCATION) cannot access the coordinate through a lookup, so:

DISTANCE(Account.BillingAddress, ...

is not valid. When I try this as a returned column, I get a query exception like "No such column".

Additionally, it isn't possible to use DISTANCE with two composed GEOLOCATIONS, so:

DISTANCE(GEOLOCATION(Latitude__c, Longitude__c), GEOLOCATION(...

is also not valid. I mention this to indicate that you cannot use two formula fields that get the related object's (i.e. the Account's) latitude and longitude respectively.

AFAIK, the only solution you have is to introduce a custom Geolocation type field on your AccountContactRelation and write the necessary triggers to synchronize this value from the Account's BillingAddress coordinate when a relation is created or when the Account's coordinate changes. You can then use this custom Geolocation field in your SOQL.

1
  • Thanks for the detailed explanation. One question I have is how does it let me use that lookup in WHERE clause?
    – P.Shri
    Apr 15, 2020 at 11:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .