Expected fields are not returned with some API types (warning or message would be helpful)

[kmcd39]

I'm new to working with salesforce, but reporting unexpected behavior where it seems a warning or different behavior should be happening.

When I query all fields for an object in client's salesforce system using salesforcer::sf_query with default api_type, some fields are omitted with no warning. When I try to manually select omitted fields, same result. However, when I switch api_type="BULK 1.0", this works.

A message, warning, or other behavior would have been very helpful for me.

Code example. Given our Contact table, which has fields:
Id, MobilePhone, HomePhone and others:

# omits "HomePhone"
'SELECT FIELDS(ALL) FROM Contact LIMIT 200' %>% salesforcer::sf_query() 

# also omits "HomePhone"
'SELECT id, phone, mobilephone FROM Contact LIMIT 200' %>% salesforcer::sf_query() 

# shows expected behavior: all three columns are returned.
'SELECT id, phone, homephone FROM Contact LIMIT 200' %>%
  salesforcer::sf_query(api_type = 'Bulk 1.0'
                         ,object_name = 'Contact')

[StevenMMortimer]

Thanks for flagging @kmcd39. Is it possible that every one of those 200 records has a null value for homephone? If so, then you might have run into the same issue that another user flagged in #78.

In short, the REST or SOAP API will not return the requested column if all values are null in the results, so it can't be parsed. The only way that the sf_query() function would know that the column was requested is if it parsed the query string or checked the object metadata (in the case of using FIELDS(ALL)). I haven't implemented either solution because parsing can be error prone and will make the function slower. My recommendation for now is to use the Bulk APIs (which return all columns requested) or use the SOAP or Rest APIs and check that all columns exist after the results are returned.

If that is not the case, then I can try to investigate further. Happy to help.

[StevenMMortimer]

@kmcd39 Closing for now. Feel free to re-open if you have more to add on this issue. Thanks!