RForcecom accessing unknown field names

Question

My final aim is to extract data from Salesforce accounts for general use in R. I spotted the RForcecom package (https://hiratake55.wordpress.com/2013/03/28/rforcecom/) which looks very useful indeed, thanks @hiratake55 for writing it! Unfortunately I am having a small problem.

I am able to sign in to my account and then access objects within and store as a data.frame just as the intro notes say.

The problem is that I want to access certain fields within the SOQL object and I don't know the names of those fields.

Here is what I have so far:

    library(RForcecom)

    username <- "" # my email address
    password <- "" # my website password + security token
    instanceURL <- "https://eu5.salesforce.com/"
    apiVersion <- "34.0"
    session <- rforcecom.login(username, password, instanceURL, apiVersion)

    # R Query
    objectName <- "Contact"
    fields <- c("Id", "Name", "Phone")
    rforcecom.retrieve(session, objectName, fields)

This works fine and returns a data.frame just as RForcecom says on the tin. Now I also want to extract for example the field "Contact Owner Alias" (the field has this name on the web interface of SalesForce). I tried the following:

    fields <- c("Id", "Name", "Phone", "Contact Owner Alias")
    rforcecom.retrieve(session, objectName, fields)

This gave an error:

    Error in rforcecom.query(session, soqlQuery) : 
      MALFORMED_QUERY: Id, Name, Phone, Contact Owner Alias FROM Contact
                                  ^
    ERROR at Row:1:Column:38
    unexpected token: Alias

QUESTION

Is there a way to retrieve all of the filenames in R? Or is there a way to return data from all fields without knowing their names.

CAVEAT

I know that part of the problem is my unfamiliarity with SOQL but it thought I would ask around to see if this had been solved inside R. If the answer is "go learn SOQL" that's ok, I just thought I would ask first.

Thanks for any help!

Staats

In the meantime, I found a workaround by going to [workbench](workbench.developerforce.com). – Staats

Answer 1

With both a retrieve() and and SOQL query() function call you will need to use the Salesforce API name of the fields in question.

With a custom field like "Contact Owner Alias" you will most likely be looking for an API name like "ContactOwnerAlias__c". Note the __c suffix that indicates it is a custom field rather than standard field.

You can get the API name by inspecting the field in the Salesforce UI, or, as you found, using an external tool like workbench to find it in the field metadata.

I don't know R, but I found there is a rforcecom.getObjectDescription() method in RForcecom presentation at the UseR! 2014. This would likely return the field metadata for the object in question, that you could then use to get the API field name.

Karie

Answer 2

Sorry for the delay and thank you Daniel B. To update I solved it with your suggestion as follows

# grab SForce Data
library(RForcecom)

# session login etc 
username <- "Nope"
password <- "Nope"
instanceURL <- "https://eu5.salesforce.com/"
apiVersion <- "34.0"
session <- rforcecom.login(username, password, instanceURL, apiVersion)

# query
objects <- rforcecom.getObjectList(session)

# pull all fields of an object
getAllFields <- function(objectName) {
  description <- rforcecom.getObjectDescription(session, objectName)
  fields <- as.character(description$name)
  rforcecom.retrieve(session, objectName, fields)
}

# grab the data 
accounts <- getAllFields("Account")

Staats

Thanks Roman. Now if only the bug would get fixed in getObjectDescription that's stopping me from using your method. – Bystreet

Answer 3

library(gsubfn) # this is used for fn$indentity

account.nm <- rforcecom.getObjectDescription(session, "Account")
nm <- toString( sprintf("%s", account.nm$name))
account.query <- fn$identity( "SELECT $nm FROM Account")

Account <- rforcecom.query(session, account.query)

Carbarn

The salesforcer package has a method sf_describe_object_fields() which will list all fields on an object and their metadata. – Daren