0

SOQL query

SELECT Id, Name FROM Opportunity
    WHERE College__r.Phone LIKE '%7411162213'
        OR College__r.OtherPhone LIKE '%7411162213'

on running on developer console it gives result total rows fetched 1

I want its equivalent SOSL query. I converted to following:

FIND {7411162213} IN ALL FIELDS
    RETURNING Opportunity(Id, Name WHERE College__r.Phone LIKE '%7411162213'
                                       OR College__r.OtherPhone LIKE '%7411162213')

but this does not give me any result on developer console. Total rows fetched is 0.

Am I missing anything in above SOSL query?

5
  • 1
    You have SOSL and SOQL the wrong way round. You need to wildcard the FIND value.
    – Phil W
    May 7, 2021 at 7:04
  • 1
    I don't think relationships are supported in SOSL or a WHERE clause. May 7, 2021 at 7:06
  • 1
    I think he wants FIND {7411162213} IN ALL FIELDS RETURNING Opportunity(Id, Name, CollegePhoneCalc__c, OtherPhoneCalc__c ) May 7, 2021 at 7:08
  • 1
    @CasparHarmer indeed. As per the documentation this must be "The name of a field for the specified object".
    – Phil W
    May 7, 2021 at 7:08
  • Except I think I'm wrong about the WHERE clause... of course this query doesn't actually NEED a WHERE clause May 7, 2021 at 7:13

1 Answer 1

1

Ok, so assuming you are actually after SOSL:

First, you can't have relationship fields. So replace them with calculated fields. (Ie, College__r.OtherPhone becomes CollegeOtherPhone__c

Secondly, you don't need the WHERE clause - you are already searching by the same value in the find part. If you DID want to filter a bit more, you'd do that in the WHERE clause, but you'd be excluding records based on different criteria.

Which leaves you with something like this:

FIND {7411162213} IN ALL FIELDS 
RETURNING Opportunity(Id, Name, CollegePhoneCalc__c, OtherPhoneCalc__c )
7
  • thanks. understood that where clause is not needed. May 7, 2021 at 7:58
  • but still needs to figure out how to use relationships May 7, 2021 at 7:59
  • FIND {7411162213} IN ALL FIELDS RETURNING Opportunity(Id, Name, CollegePhone__c, CollegeOtherPhone__c ) RETURNING Opportunity(Id, Name, CollegePhone__c, CollegeOtherPhone__c ^ ERROR at Row:1:Column:65 No such column 'CollegePhone__c' on entity 'Opportunity'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names. May 7, 2021 at 7:59
  • Oh, you have to go and make that field. You would create it as a formula field on Opportunity? and it's just equal to College__r.OtherPhone for example May 7, 2021 at 8:04
  • ok . can relationships not be used like sosl? FIND {9122065057*} IN ALL FIELDS RETURNING Opportunity(Id, Name, College__r.OtherPhone, College__r.Phone) May 7, 2021 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.