Dream to Learn is shutting down...

We are very sorry to say that Dream to Learn will be shutting down as of December 28th, 2019. If you have content that you wish to keep, you should make a copy of it before that date.


0COMMENTS1RECOMMENDS

An Idiot's Guide to Integrating SALESFORCE with R - Part 2 - Basic Read and Write

83
POSTED IN: Data Analytics & Visualization Blog

 

Let's Connect and Read / Write Data!

Building on the basic connectivity from library(RForcecom) - which seems to be a great package and good starting point - I'm going to jump right in to more 'doing' and less Surveying in Part 1.  

One of the key questions I have is this:  Can SalesForce be used as a generic 'dashboard' for skunk-works teams working in large organizations?

I.e. if there are clusters of folks working with Data, and working in R - who want to start to distribute results to a wider audience, in a safe and secure way, and without needing to stand up any new infrastructure (and leverage existing IT spend) - can we use SF as a window into R?

So as I take the small steps - the goal is to get a basic Array of information uploaded from R to SF - and then chart / export / report / alert from SF.  Hack SF to serve our R needs ;)

 

Let's have a mini-hero moment!

 

STEP 1 - Connect

######### Idiot's Guide to Integrating SalesForce & R  - PART 2
#  First section guided by instructions here: https://hiratake55.wordpress.com/2013/03/28/rforcecom/
#  IF you want to go a little deeper - this is some 'long form' code to play with
#########

# install.packages("RForcecom") # # this installed OK for me on 4/26/2015 R 3.1.2 running on a PC Win 7
library(RForcecom)

username <- "ryan[at]dreamtolearn[d0t]com"
password <- "YOURPASSWORD_YOUR TOKEN"
instanceURL <- "https://na24.salesforce.com/"
apiVersion <- "27.0"  ## 
session <- rforcecom.login(username, password, instanceURL, apiVersion)

 

rforcecom.getServerTimestamp(session)

## BASIC TEST - working? connected?
soqlQuery <- "SELECT Id, Name, Phone FROM Account WHERE AnnualRevenue > 50000 LIMIT 5"
rforcecom.query(session, soqlQuery)

 

 

STEP 2 - Capture Schema and Fields

#### # Retrieve object descriptions NAMES for ACCOUNT (filter out junk)
### I tried LABEL (very similar) but NAME field work better for calls here
objectName <- "Account"
#rforcecom.getObjectDescription(session, objectName)
obj_desc <- rforcecom.getObjectDescription(session, objectName) # 54 columns
obj_desc <- data.frame(obj_desc$name) # kill everything except name row (54 columns)
obj_desc # these are the 54 attributes 

 

Account$NAME

#### ACCOUNT$NAME
obj_desc 
'
          obj_desc.name
1                    Id
2             IsDeleted
3        MasterRecordId
4                  Name
5                  Type
6              ParentId
7         BillingStreet
8           BillingCity
9          BillingState
10    BillingPostalCode
11       BillingCountry
12       ShippingStreet
13         ShippingCity
14        ShippingState
15   ShippingPostalCode
16      ShippingCountry
17                Phone
18                  Fax
19        AccountNumber
20              Website
21                  Sic
22             Industry
23        AnnualRevenue
24    NumberOfEmployees
25            Ownership
26         TickerSymbol
27          Description
28               Rating
29                 Site
30              OwnerId
31          CreatedDate
32          CreatedById
33     LastModifiedDate
34     LastModifiedById
35       SystemModstamp
36     LastActivityDate
37               Jigsaw
38      JigsawCompanyId
39          CleanStatus
40        AccountSource
41           DunsNumber
42           Tradestyle
43            NaicsCode
44            NaicsDesc
45          YearStarted
46              SicDesc
47       DandbCompanyId
48  CustomerPriority__c
49               SLA__c
50            Active__c
51 NumberofLocations__c
52 UpsellOpportunity__c
53   SLASerialNumber__c
54 SLAExpirationDate__c
'

Contact$NAME

#####  CONTACT NAMES NOW!
objectName <- "Contact"
#rforcecom.getObjectDescription(session, objectName)
obj_desc <- rforcecom.getObjectDescription(session, objectName) # 48 columns
obj_desc <- data.frame(obj_desc$name) # kill everything except label row (48 columns)
obj_desc # these are the 48 attributes  for CONTACT

# CONTACT$NAME
'
        obj_desc.name
1                  Id
2           IsDeleted
3      MasterRecordId
4           AccountId
5            LastName
6           FirstName
7          Salutation
8                Name
9         OtherStreet
10          OtherCity
11         OtherState
12    OtherPostalCode
13       OtherCountry
14      MailingStreet
15        MailingCity
16       MailingState
17  MailingPostalCode
18     MailingCountry
19              Phone
20                Fax
21        MobilePhone
22          HomePhone
23         OtherPhone
24     AssistantPhone
25        ReportsToId
26              Email
27              Title
28         Department
29      AssistantName
30         LeadSource
31          Birthdate
32        Description
33            OwnerId
34        CreatedDate
35        CreatedById
36   LastModifiedDate
37   LastModifiedById
38     SystemModstamp
39   LastActivityDate
40  LastCURequestDate
41   LastCUUpdateDate
42 EmailBouncedReason
43   EmailBouncedDate
44             Jigsaw
45    JigsawContactId
46        CleanStatus
47           Level__c
48       Languages__c
'

User$NAME

#####  USER$NAME
objectName <- "User"
#rforcecom.getObjectDescription(session, objectName)
obj_desc <- rforcecom.getObjectDescription(session, objectName) # 103 columns
obj_desc <- data.frame(obj_desc$name) # kill everything except label (NOTE THER IS NAME ROW HERE -
LOOKS SIMILAR)
obj_desc # these are the 103 attributes for USER

'
                                         obj_desc.name
1                                                   Id
2                                             Username
3                                             LastName
4                                            FirstName
5                                                 Name
6                                          CompanyName
7                                             Division
8                                           Department
9                                                Title
10                                              Street
11                                                City
12                                               State
13                                          PostalCode
14                                             Country
15                                               Email
16                                               Phone
17                                                 Fax
18                                         MobilePhone
19                                               Alias
20                                   CommunityNickname
21                                            IsActive
22                                      TimeZoneSidKey
23                                          UserRoleId
24                                        LocaleSidKey
25                                  ReceivesInfoEmails
26                             ReceivesAdminInfoEmails
27                                    EmailEncodingKey
28                                           ProfileId
29                                            UserType
30                                   LanguageLocaleKey
31                                      EmployeeNumber
32                                 DelegatedApproverId
33                                           ManagerId
34                                       LastLoginDate
35                              LastPasswordChangeDate
36                                         CreatedDate
37                                         CreatedById
38                                    LastModifiedDate
39                                    LastModifiedById
40                                      SystemModstamp
41                          OfflineTrialExpirationDate
42                       OfflinePdaTrialExpirationDate
43                        UserPermissionsMarketingUser
44                          UserPermissionsOfflineUser
45                  UserPermissionsCallCenterAutoLogin
46                           UserPermissionsMobileUser
47                        UserPermissionsSFContentUser
48                        UserPermissionsKnowledgeUser
49                      UserPermissionsInteractionUser
50                          UserPermissionsSupportUser
51                UserPermissionsJigsawProspectingUser
52             UserPermissionsSiteforceContributorUser
53               UserPermissionsSiteforcePublisherUser
54                   UserPermissionsChatterAnswersUser
55                                     ForecastEnabled
56               UserPreferencesActivityRemindersPopup
57        UserPreferencesEventRemindersCheckboxDefault
58         UserPreferencesTaskRemindersCheckboxDefault
59                     UserPreferencesReminderSoundOff
60                 UserPreferencesDisableAllFeedsEmail
61                UserPreferencesDisableFollowersEmail
62              UserPreferencesDisableProfilePostEmail
63            UserPreferencesDisableChangeCommentEmail
64             UserPreferencesDisableLaterCommentEmail
65              UserPreferencesDisProfPostCommentEmail
66                       UserPreferencesContentNoEmail
67                UserPreferencesContentEmailAsAndWhen
68               UserPreferencesApexPagesDeveloperMode
69          UserPreferencesHideCSNGetChatterMobileTask
70             UserPreferencesDisableMentionsPostEmail
71              UserPreferencesDisMentionsCommentEmail
72                   UserPreferencesHideCSNDesktopTask
73             UserPreferencesDisCommentAfterLikeEmail
74                     UserPreferencesDisableLikeEmail
75                  UserPreferencesDisableMessageEmail
76                        UserPreferencesOptOutOfTouch
77                       UserPreferencesJigsawListUser
78                 UserPreferencesDisableBookmarkEmail
79                UserPreferencesDisableSharePostEmail
80                UserPreferencesEnableAutoSubForFeeds
81  UserPreferencesDisableFileShareNotificationsForApi
82             UserPreferencesShowTitleToExternalUsers
83           UserPreferencesShowManagerToExternalUsers
84             UserPreferencesShowEmailToExternalUsers
85         UserPreferencesShowWorkPhoneToExternalUsers
86       UserPreferencesShowMobilePhoneToExternalUsers
87               UserPreferencesShowFaxToExternalUsers
88     UserPreferencesShowStreetAddressToExternalUsers
89              UserPreferencesShowCityToExternalUsers
90             UserPreferencesShowStateToExternalUsers
91        UserPreferencesShowPostalCodeToExternalUsers
92           UserPreferencesShowCountryToExternalUsers
93                                           ContactId
94                                           AccountId
95                                        CallCenterId
96                                           Extension
97                                FederationIdentifier
98                                             AboutMe
99                                        FullPhotoUrl
100                                      SmallPhotoUrl
101                                    DigestFrequency
102                  DefaultGroupNotificationFrequency
103                          JigsawImportLimitOverride
'
################# 
##### Ok - one more (you can keep going :)  - # You can play with other Objects --
# Contact, Account, User, Task, and Note all work fomr here:
# https://www.salesforce.com/developer/docs/api/Content/sforce_api_objects_list.htm 
# but some do not (not sure why)

Task$NAME

#####  LAST ONE TASK$NAME
objectName <- "Task"
obj_desc <- rforcecom.getObjectDescription(session, objectName) # 35 columns
obj_desc <- data.frame(obj_desc$name) # kill everything except label
obj_desc # these are the 35 attributes for USER

'
1                        Id
2                     WhoId
3                    WhatId
4                   Subject
5              ActivityDate
6                    Status
7                  Priority
8                   OwnerId
9               Description
10                IsDeleted
11                AccountId
12                 IsClosed
13              CreatedDate
14              CreatedById
15         LastModifiedDate
16         LastModifiedById
17           SystemModstamp
18               IsArchived
19    CallDurationInSeconds
20                 CallType
21          CallDisposition
22               CallObject
23         ReminderDateTime
24            IsReminderSet
25     RecurrenceActivityId
26             IsRecurrence
27  RecurrenceStartDateOnly
28    RecurrenceEndDateOnly
29 RecurrenceTimeZoneSidKey
30           RecurrenceType
31       RecurrenceInterval
32  RecurrenceDayOfWeekMask
33     RecurrenceDayOfMonth
34       RecurrenceInstance
35    RecurrenceMonthOfYear
'

Step 3 - Be a Hero!  

# OK - I went into SF UI and created 4 dummy tasks and want to see a read (included "fresh apples" and "fresh pears" in two comment sections)
############### SEARCH RECORD
queryString <- "fresh"
rforcecom.search(session, queryString)
## this works - returns the two ID's of the tasks I made

objectName <- "Task"
#fields <- c("Id", "Subject", "status", "priority") # DOES NOT APPEAR TO BE CASE SENSITIVE
fields <- c("Id","WhoId","WhatId","Subject","ActivityDate","Status","Priority")
rforcecom.retrieve(session, objectName, fields)

##### OK - great - that's reading - how about writing?
objectName <- "Task"
fields <- c(Subject = "R Testing", Status="Not Started", Priority="High")
rforcecom.create(session, objectName, fields)

## Woo hoo!  Mini Hero moment! - works we can write meaningful info to Objects and Fields we choose!

 

 

 

Interested in more content by this author?

About the Author

Ryan Anderson

Ryan Anderson

Hi! I like to play with data, analytics and hack around with robots and gadgets in my garage. Lately I've been learning about machine learning.

About this blog

Description is...<br/>Data Analytics & Visualization Blog - Generating insights from Data since 2013

Created: July 25, 2014

Englishfrançais

Up Next