New salesforce connector doesn't support SOQL syntax

Boilard, Andre 46 Reputation points
2024-06-11T16:23:08.8133333+00:00

trying to convert to new salesforce connector and getting 'unbound' error when trying to return all fields for an object such as select fields(all) from profile where id = 'xxx'. using version 60.0 in linked service Seems like only the select fields(standard) works. And, returning compound fields on the account table also throws errors.

I don't want to return the whole object but require a where clause to return one id at a time.

Status Code: BadRequest, Error message: [{"errorCode":"API_ERROR","message":"The SOQL FIELDS function is not supported with an unbounded set of fields in this API."}]

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,162 questions
{count} votes

Accepted answer
  1. Sina Salam 7,286 Reputation points
    2024-06-11T18:14:52.7166667+00:00

    Hello Boilard, Andre,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    I understand that the error you're encountering is due to limitations in the SOQL FIELDS function when used with certain APIs and versions. Because, the FIELDS(ALL) and compound fields are not supported in the context you're trying to use them.

    Solution

    The error you're encountering is due to limitations in the SOQL FIELDS function when used with certain APIs and versions.

    You can use this work around to resolve the issues:

    1. You can explicitly list the custom fields you need along with the standard fields. Example:
            SELECT FIELDS(STANDARD), CustomField1__c, CustomField2__c FROM Profile WHERE Id = 'xxx'
      
    2. Retrieve the list of fields programmatically and construct the SOQL query dynamically.

    Example: If you use Python.

          # Pseudocode example
          fields = get_fields_for_object('Profile')
          field_list = ', '.join(fields)
          query = f"SELECT {field_list} FROM Profile WHERE Id = 'xxx'"
          
          #You can use the Salesforce `describe` call to get the field list for the object:
          
          
          describe_result = sf.Profile.describe()
          fields = [field['name'] for field in describe_result['fields']]
          field_list = ', '.join(fields)
          query = f"SELECT {field_list} FROM Profile WHERE Id = 'xxx'"
    
    1. For compound fields like Address, you typically need to handle the subfields individually. For instance, instead of querying BillingAddress, you query BillingStreet, BillingCity, BillingState, etc.
    2. Finally, If possible, check if using a different API version or method (e.g., REST API, Bulk API) provides the needed functionality. Different versions may have different support for certain SOQL features.

    Accept Answer

    I hope this is helpful! Do not hesitate to let me know if you have any other questions.

    ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

    Best Regards,

    Sina Salam


1 additional answer

Sort by: Most helpful
  1. Bhargava-MSFT 28,956 Reputation points Microsoft Employee
    2024-06-11T18:44:22.61+00:00

    Hello Boilard, Andre,

    Welcome to the Microsoft Q&A forum.

    It seems like this is a limitation from the salesforce end. SOQL FIELDS function is used with an unbounded set of fields, which is not supported by the API.

    There is no issue from ADF end.

    From the below salesforce documentation, FIELDS(ALL) is not currently supported on Unbounded queries. You you need to use FIELDS(STANDARD)

    https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_fields.htm

    Please check the below threads:
    https://stackoverflow.com/questions/73427678/how-to-query-more-than-200-field-salesforce-soql-fields

    https://salesforce.stackexchange.com/questions/340948/soql-fields-function-gives-error-even-with-limit

    I hope this answers your question.

    1 person found this answer helpful.
    0 comments No comments