Using the salesforce.com adapter
Contents
So this is seeing the salesforce.com adapter in action:
I have attached a zip file with source code and sample data for the To Translator component for this channel which includes the salesforce.com adapter here: SalesforceTranslator.zip
Now the great thing about this adapter is that it’s really snappy. That’s because of the SQLite caching. It’s interesting to try turning setting the clear_cache flag to true. It’s like night and day in terms of the responsiveness of the editor. With caching it’s blindingly fast and easy to do your work.
Caching does have some drawbacks – I picked a default time of 60 seconds. Cached data can be out of date – if you don’t keep it in mind it could result in unexpected results like if you do a query of data after deleting an object then it will still be there. I made it possible to override the default cache time for each query call into the adapter.
Another really neat thing about the salesforce.com module is that once you take away the meta definitions for each of the salesforce objects is that it is less than 300 lines of code. The meta definitions themselves make up the majority of the code. It makes the whole thing a breeze to maintain since there is so little code and any changes in the salesforce.com objects can easily expressed by updating the meta defintions. Beautiful!
You will need to spend some time making your comfortable with the salesforce.com data model. One interesting tit-bit I found useful was this little line:
local QueueList = S:groupList{where=“Type = ‘Queue’”}
This gives you a list of all the user defined ticket “queues” which is a workflow concept used a lot in salesforce.com. It’s not 100% obvious from the API, but I find with salesforce.com there is a lot of good information one can get via Google for these types of questions. One page I would recommend bookmarking is the object model of salesforce.com which I found had great information.
So to finish things off I will walk through how we do the CRUD operations with this API and also how to get a fresh copy of the meta data to modify, add or remove which of the salesforce.com objects we want to expose. In general I think with most salesforce.com implementations most companies will select a subset of salesforce.com to work with – it’s the old 80-20 rule.
So doing queries with this API is very convenient. The “List” methods take the optional arguments of:
- limit – this is the maximum number of records to return.
- where – this is SOQL (SQL) like WHERE clause to use to filter the results.
- cache – this allows one to override the default time of 60 seconds to another time threshold – or 0 for no caching.
I guess one could easily enhance my implementation to add an optional order by clause too. This screenshot shows the results coming back from a list of contacts:
Notice the custom fields “MRN__c” and “Languages__c”?
Creating and updating records is done using the xxxxModify{} methods. A new record will be created if the “id” field is not present, if “id” is present than an existing object will be updated. salesforce.com uses unique GUIDs for every object in the database.
The modify methods are really easy to use since the properties of the object are all expressed as optional parameters. This screenshot gives you an idea of how it works:
And delete methods are really obvious – xxxxDelete{id=<id of object to delete>}. Both the modify and delete methods support the “live” flag. By default in the editor the live flag is set to false since that would alter the salesforce.com application in the editor which one usually doesn’t want.
Now I deliberately left out a lot of the salesforce.com objects since I think many are probably not going used by most organizations. But it’s dead easy to add them. To do it just use the handy apiDefinition method I have supplied. If you were to do say, S:apiDefinition(‘Idea’) then this is what you would get back from that method:
objectDefs.idea = {object='Idea', fields={
Categories="?",
LastModifiedDate="?",
LastCommentDate="?",
CreatedDate="?",
Body="?",
LastCommentId="?",
CreatedById="?",
LastModifiedById="?",
Status="?",
SystemModstamp="?",
IsHtml="?",
NumComments="?",
ParentIdeaId="?",
RecordTypeId="?",
IsLocked="?",
Title="?",
CommunityId="?",
VoteTotal="?",
VoteScore="?",
IsDeleted="?"}}
Notice how the descriptions of the parameters come up as ‘?’ This is because the API that salesforce.com does supply the description of for the fields of each object. You’ll have to enter that by hand – still it’s not all that onerous to do that and I find the names that salesforce.com selected were all pretty easy to understand.
I put some smarts into the code so that if the existing meta definitions have a description of a parameter that the apiDefinition method re-uses those descriptions which is nice feature to have. So if you put descriptions in for fields you won’t lose them if you refresh the definition.
If you have any questions or comments about the code and the techniques it uses do feel free to ask. This is my first draft of the article too, so if you spot any typos then let me know. Here’s the salesforce module:
salesforce = {}
store = require 'store'
objectDefs = {}
objectDefs.contact = {object='Contact', fields={
OtherPhone="Alternative phone number",
FirstName="First name of contact",
AssistantPhone="Their secretary's phone",
MailingState="State mailing address",
MRN__c="MRN number",
Salutation="How to greet them",
Languages__c="Languages they speak",
Phone="Phone number of the contact",
Email="Email of contact",
AccountId="AccountID of the contact",
Birthdate="Birthdate of the contact",
Description="Description of the contact",
HomePhone="Home phone number",
LastName="Last name of contact",
LastActivityDate="Last activity date",
CreatedById="Who created this",
LastModifiedDate="When last modified",
LastModifiedById="Who modified this",
CreatedDate="Date created",
MailingPostalCode="Postal code for mailing address",
Department="What department",
MobilePhone="Their mobile phone",
MailingCity="City to mail to.",
MailingStreet="Street to mail to",
OwnerId="Owner of this contact",
IsDeleted="Has this contact been deleted",
Fax="FAX number",
Title="Title of contact - CEO etc."
}}
objectDefs.case = {object='Case', fields={
SuppliedPhone="Supplied phone number",
Description="Description",
Origin="Origin of the phone",
CreatedDate="When created",
IsEscalated="Has this been escalated",
ClosedDate="When closed",
Reason="Reason for ticket",
CaseNumber="Case number",
CreatedById="Who created it",
LastModifiedById="?",
Status="?",
Priority="?",
SuppliedEmail="?",
SuppliedName="?",
ParentId="?",
ContactId="?",
OwnerId="?",
IsDeleted="?",
Subject="?",
SystemModstamp="?",
LastModifiedDate="?",
IsClosed="?",
SuppliedCompany="?",
AccountId="?"}}
objectDefs.queueSobject = {object='QueueSobject', fields={
SystemModstamp="A field",
QueueId="A field",
SobjectType="A field",
CreatedById="A field"}}
objectDefs.group = {object='Group', fields={
Type="A field",
LastModifiedDate="A field",
CreatedDate="A field",
DoesIncludeBosses="A field",
Email="A field",
OwnerId="A field",
Name="A field",
LastModifiedById="A field",
RelatedId="A field",
SystemModstamp="A field",
DoesSendEmailToMembers="A field",
CreatedById="A field"}}
objectDefs.note = {object='Note', fields={
IsPrivate="A field",
LastModifiedDate="A field",
CreatedDate="A field",
ParentId="A field",
Body="A field",
OwnerId="A field",
IsDeleted="A field",
CreatedById="A field",
LastModifiedById="A field",
SystemModstamp="A field",
Title="A field"}}
objectDefs.community = {object='Community', fields={
IsActive="A field",
Description="A field",
CreatedById="A field",
LastModifiedById="A field",
CreatedDate="A field",
Name="A field",
SystemModstamp="A field",
LastModifiedDate="A field"}}
objectDefs.contentDocument = {object='ContentDocument', fields={
LastModifiedDate="A field",
CreatedDate="A field",
LatestPublishedVersionId="A field",
OwnerId="A field",
IsDeleted="A field",
Title="A field",
LastModifiedById="A field",
PublishStatus="A field",
SystemModstamp="A field",
CreatedById="A field"}}
objectDefs.event = {object='Event', fields={
IsPrivate="A field",
StartDateTime="A field",
IsArchived="A field",
CreatedDate="A field",
IsAllDayEvent="A field",
ActivityDate="A field",
CreatedById="A field",
RecurrenceInstance="A field",
IsGroupEvent="A field",
RecurrenceEndDateOnly="A field",
ActivityDateTime="A field",
WhatId="A field",
RecurrenceActivityId="A field",
EndDateTime="A field",
Subject="A field",
Location="A field",
IsChild="A field",
AccountId="A field",
RecurrenceType="A field",
Description="A field",
IsRecurrence="A field",
WhoId="A field",
RecurrenceDayOfWeekMask="A field",
RecurrenceStartDateTime="A field",
SystemModstamp="A field",
LastModifiedById="A field",
ReminderDateTime="A field",
RecurrenceDayOfMonth="A field",
DurationInMinutes="A field",
RecurrenceMonthOfYear="A field",
OwnerId="A field",
IsDeleted="A field",
ShowAs="A field",
RecurrenceInterval="A field",
RecurrenceTimeZoneSidKey="A field",
IsReminderSet="A field",
LastModifiedDate="A field",
GroupEventType="A field"}}
objectDefs.user = {object='User', fields={
UserPreferencesApexPagesDeveloperMode="A field",
FirstName="A field",
Division="A field",
FederationIdentifier="A field",
Alias="A field",
UserPreferencesHideCSNDesktopTask="A field",
DelegatedApproverId="A field",
EmailEncodingKey="A field",
LastLoginDate="A field",
UserType="A field",
OfflineTrialExpirationDate="A field",
UserPermissionsKnowledgeUser="A field",
CallCenterId="A field",
EmployeeNumber="A field",
City="A field",
LastModifiedById="A field",
UserPermissionsInteractionUser="A field",
CommunityNickname="A field",
MobilePhone="A field",
Extension="A field",
ContactId="A field",
State="A field",
UserRoleId="A field",
SmallPhotoUrl="A field",
Fax="A field",
UserPermissionsOfflineUser="A field",
UserPermissionsMobileUser="A field",
UserPreferencesEventRemindersCheckboxDefault="A field",
ForecastEnabled="A field",
UserPreferencesHideCSNGetChatterMobileTask="A field",
LastPasswordChangeDate="A field",
UserPreferencesDisableAutoSubForFeeds="A field",
Country="A field",
Phone="A field",
DigestFrequency="A field",
Email="A field",
UserPreferencesActivityRemindersPopup="A field",
ReceivesInfoEmails="A field",
AboutMe="A field",
UserPermissionsCallCenterAutoLogin="A field",
CompanyName="A field",
LastModifiedDate="A field",
ReceivesAdminInfoEmails="A field",
LastName="A field",
Street="A field",
Username="A field",
FullPhotoUrl="A field",
CurrentStatus="A field",
ProfileId="A field",
CreatedDate="A field",
AccountId="A field",
SystemModstamp="A field",
OfflinePdaTrialExpirationDate="A field",
Department="A field",
CreatedById="A field",
UserPreferencesOptOutOfTouch="A field",
PostalCode="A field",
UserPreferencesReminderSoundOff="A field",
LanguageLocaleKey="A field",
IsActive="A field",
UserPermissionsSupportUser="A field",
LocaleSidKey="A field",
UserPreferencesTaskRemindersCheckboxDefault="A field",
UserPermissionsSFContentUser="A field",
TimeZoneSidKey="A field",
ManagerId="A field",
UserPermissionsMarketingUser="A field",
Title="A field",
Name="A field"}}
objectDefs.product2 = {object='Product2', fields={
Description="A field",
CreatedDate="A field",
ProductCode="A field",
IsActive="A field",
IsDeleted="A field",
Name="A field",
LastModifiedById="A field",
Family="A field",
CreatedById="A field",
SystemModstamp="A field",
LastModifiedDate="A field"}}
objectDefs.taskStatus = {object='TaskStatus', fields={
LastModifiedDate="A field",
CreatedDate="A field",
MasterLabel="A field",
SortOrder="A field",
CreatedById="A field",
LastModifiedById="A field",
IsDefault="A field",
IsClosed="A field",
SystemModstamp="A field"}}
objectDefs.document = {object='Document', fields={
Description="A field",
CreatedDate="A field",
Body="A field",
NamespacePrefix="A field",
LastModifiedById="A field",
Url="A field",
SystemModstamp="A field",
AuthorId="A field",
BodyLength="A field",
DeveloperName="A field",
Keywords="A field",
LastModifiedDate="A field",
CreatedById="A field",
IsInternalUseOnly="A field",
IsDeleted="A field",
Name="A field",
Type="A field",
IsPublic="A field",
FolderId="A field",
ContentType="A field",
IsBodySearchable="A field"}}
objectDefs.taskPriority = {object='TaskPriority', fields={
LastModifiedDate="A field",
CreatedDate="A field",
MasterLabel="A field",
SortOrder="A field",
CreatedById="A field",
LastModifiedById="A field",
IsHighPriority="A field",
SystemModstamp="A field",
IsDefault="A field"}}
objectDefs.idea = {object='Idea', fields={
Categories="?",
LastModifiedDate="?",
LastCommentDate="?",
CreatedDate="?",
Body="?",
LastCommentId="?",
CreatedById="?",
LastModifiedById="?",
Status="?",
SystemModstamp="?",
IsHtml="?",
NumComments="?",
ParentIdeaId="?",
RecordTypeId="?",
IsLocked="?",
Title="?",
CommunityId="?",
VoteTotal="?",
VoteScore="?",
IsDeleted="?"}}
objectDefs.task = {object='Task', fields={
IsArchived="A field",
CreatedDate="A field",
ActivityDate="A field",
CreatedById="A field",
RecurrenceInstance="A field",
Priority="A field",
RecurrenceStartDateOnly="A field",
CallType="A field",
WhatId="A field",
CallDurationInSeconds="A field",
Subject="A field",
ReminderDateTime="A field",
RecurrenceInterval="A field",
AccountId="A field",
RecurrenceType="A field",
Description="A field",
IsRecurrence="A field",
WhoId="A field",
RecurrenceDayOfWeekMask="A field",
CallObject="A field",
Status="A field",
SystemModstamp="A field",
RecurrenceTimeZoneSidKey="A field",
RecurrenceDayOfMonth="A field",
LastModifiedDate="A field",
RecurrenceMonthOfYear="A field",
CallDisposition="A field",
IsDeleted="A field",
LastModifiedById="A field",
IsClosed="A field",
RecurrenceEndDateOnly="A field",
IsReminderSet="A field",
RecurrenceActivityId="A field",
OwnerId="A field"}}
objectDefs.account = {object='Account', fields={
NumberofLocations__c="A field",
CreatedDate="A field",
UpsellOpportunity__c="A field",
Industry="A field",
BillingCountry="A field",
AnnualRevenue="A field",
Sic="A field",
Type="A field",
TickerSymbol="A field",
Phone="A field",
ShippingStreet="A field",
NumberOfEmployees="A field",
SLA__c="A field",
AccountNumber="A field",
Description="A field",
ShippingPostalCode="A field",
BillingCity="A field",
ShippingCity="A field",
BillingState="A field",
SLASerialNumber__c="A field",
BillingPostalCode="A field",
LastModifiedById="A field",
Active__c="A field",
CustomerPriority__c="A field",
SystemModstamp="A field",
Website="A field",
LastModifiedDate="A field",
CreatedById="A field",
ShippingState="A field",
Site="A field",
MasterRecordId="A field",
Rating="A field",
ParentId="A field",
LastActivityDate="A field",
OwnerId="A field",
IsDeleted="A field",
Name="A field",
Ownership="A field",
ShippingCountry="A field",
Fax="A field",
SLAExpirationDate__c="A field",
BillingStreet="A field"}}
local function GetCache(Key, CacheTimeout)
if (CacheTimeout == 0) then
return nil
end
local CacheTime = store.get(Key.."T")
if (os.ts.difftime(os.ts.time(), CacheTime) < CacheTimeout) then
local CachedData = store.get(Key)
local R = json.parse{data=CachedData}
return R
end
return nil
end
local function PutCache(Key, Value)
store.put(Key, Value)
store.put(Key.."T", os.ts.time())
end
-- We only cache in development
local function cachedHttpGet(T)
local R, Key
if iguana.isTest() then
Key = T.url;
for K,V in pairs(T.parameters) do
Key = Key..K..V
end
trace(Key)
R = GetCache(Key, T.cache or 60)
if (R) then return R end
end
T.cache = nil;
local P = net.http.get(T)
R = json.parse{data=P}
if iguana.isTest() then
PutCache(Key, P)
end
return R
end
local function GetAccessTokenViaHTTP(CacheKey,T)
local Url = 'https://login.salesforce.com/services/oauth2/token'
local Auth = {grant_type = 'password',
client_id = T.consumer_key,
client_secret = T.consumer_secret,
username = T.username,
password = T.password}
local J = net.http.post{url=Url,
parameters = Auth,
live=true}
PutCache(CacheKey, J)
local AccessInfo = json.parse(J)
return AccessInfo
end
local function CheckClearCache(DoClear)
if DoClear then
store.resetTableState()
end
end
local salesmethods = {}
local MetaTable = {}
MetaTable.__index = salesmethods;
function salesforce.connect(T)
CheckClearCache(T.clear_cache)
local P = GetCache(T.consumer_key, 1800) or
GetAccessTokenViaHTTP(T.consumer_key, T)
setmetatable(P, MetaTable)
return P
end
local helpinfo = {} HelpConnect = [[{"SeeAlso":[{"Title":"Salesforce","Link":"http://www.salesforce.com"}],
"Returns":[{"Desc":"The salesforce.com website."}], "Title":"salesforce.connect",
"Parameters":[{"username":{"Desc":"User ID to login with."}}, {"password":{"Desc":"Password of that user ID"}}
{"consumer_key":{"Desc":"Consumer key for this connected app."}}, {"consumer_secret":{"Desc":"Consumer secret for this connected app."}}, {"clear_cache":{"Opt" : true,"Desc":"If this is set to true then then the SQLite cache used to improve performance will be cleared."}},],
"ParameterTable": true,
"Usage":" local C = salesforce.connect{clear_cache=false,
username='sales@interfaceware.com',
password='mypassword',
consumer_secret='585519048400883388',
consumer_key='3MVG9KI2HHAq33RyfdfRmZyEybpy7b_bZtwCyJW7e._mxrVtsrbM.g5n3.fIwK3vPGRl2Ly2u7joju3yYpPeO' }",
"Desc":"Returns a connection object to salesforce instance"}]]
help.set{input_function=salesforce.connect, help_data=json.parse{data=HelpConnect}}
local function ParseResult(Returned)
if #Returned == 0 then
return {}
end
local R = json.parse{data=Returned}
if #R > 0 and R[1].errorCode then
error(R[1].message,4)
end
return R
end
local function patchObject(S, T, ObjectName)
local Live = not iguana.isTest() or T.live
local Path = S.instance_url..
'/services/data/v20.0/sobjects/'..ObjectName..'/'
local Method
if (T.id) then
trace("Updating");
Method = 'PATCH'
Path = Path..T.id
T.id = nil;
else
trace("New record");
Method = 'POST'
end
trace(Path)
T.live = nil;
local Headers={}
Headers['Content-Type']='application/json'
Headers.Authorization ="Bearer ".. S.access_token
local Returned = net.http.put{data=json.serialize{data=T}, method=Method,headers=Headers,
url=Path,live=Live}
return ParseResult(Returned)
end
function salesmethods:describe(Object)
local S = self;
local Url = S.instance_url..'/services/data/v20.0/sobjects/'..Object..'/describe/'
trace(Url)
local Headers={}
Headers['Content-Type']='application/json'
Headers.Authorization ="Bearer ".. S.access_token
return cachedHttpGet({headers=Headers, live=true, url=Url, parameters={}}, 50000)
end
-- Used to generate API set
local function PrettyPrint(List, Name)
local Def = List[Name]
local R = "objectDefs."..Name.." = {"
R = R.."object='"..Def.object.."', fields={\n"
for K,V in pairs(Def.fields) do
R = R..' '..K..'="'..V..'",\n'
end
R = R:sub(1, #R-2).."}}\n\n"
return R
end
function ObjectName(Name)
return Name:sub(1,1):lower()..Name:sub(2)
end
local function GenerateAPI(S, Object)
local Info = S:describe(Object)
local CName = ObjectName(Object)
local Def = {}
Def.object = Object
Def.fields ={}
for i=1, #Info.fields do
local Name = Info.fields[i].name
trace(Name)
trace(Info.fields[i])
if Name ~= 'Id' then
if (objectDefs[CName] and objectDefs[CName].fields[Name] ) then
Def.fields[Name] = objectDefs[CName].fields[Name]
else
Def.fields[Name] = "?"
end
end
end
return Def
end
-- Example objects QueueSobject, Account, Community, Contact, ContentDocument, Document, Product2, Event, Group, Note, Profile, Task, TaskPriority, TaskStatus, User
-- See https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_list.htm
function salesmethods.apiDefinition(S, Name)
local Def = {}
local DefName = ObjectName(Name)
Def[DefName] = GenerateAPI(S, Name)
return PrettyPrint(Def, DefName)
end
function GenerateModifierMethod(Name, Info)
local FName = Name..'Modify'
salesmethods[FName] = function (S,T) return patchObject(S, T, Info.object) end
local F = salesmethods[FName]
local Help = {}
Help.Desc = "Create or update a "..Name
Help.ParameterTable = true
Help.Parameters = {}
Help.Parameters[1] = {id={Opt=true, Desc="Unique id of "..Name..". If not present a new field will be created."}}
Help.Parameters[2] = {live={Opt=true, Desc="Set to true to make this command work in the editor. Default is false."}}
for K,V in pairs(Info.fields) do
Help.Parameters[#Help.Parameters+1] = {}
Help.Parameters[#Help.Parameters][K] = {Opt=true, Desc=V}
end
help.set{input_function=F, help_data=Help}
end
function queryObjects(S, T)
if (T.where) then
T.query = T.query.." WHERE "..T.where
end
if (T.limit) then
T.query = T.query.." LIMIT "..T.limit
end
local P ={parameters={q=T.query}, url=S.instance_url..T.path,
headers={Authorization="Bearer ".. S.access_token}, cache=T.cache, live=true}
local R = cachedHttpGet(P)
if #R > 0 and R[1].errorCode then
CheckClearCache(true)
error(R[1].message,4)
end
return R
end
local function selectQuery(T)
local R = 'SELECT Id';
for K,V in pairs(T.fields) do
R = R..","..K
end
R = R.." FROM "..T.object
return R
end
local function listObjects(S,T,D)
T = T or {}
T.query = selectQuery(D)
T.path = '/services/data/v20.0/query'
return queryObjects(S,T)
end
function GenerateListMethod(Name, Info)
local FName = Name..'List'
salesmethods[FName] = function(S,T) return listObjects(S,T,Info) end;
local F = salesmethods[FName]
local Help = {}
Help.Desc = "Query list of "..Name
Help.ParameterTable = true
Help.Parameters = {}
Help.Parameters[1] = {limit={Opt=true, Desc="Limit the number of results - default is no limit."}}
Help.Parameters[2] = {where={Opt=true, Desc="Give a WHERE clause."}}
Help.Parameters[3] = {cache={Opt=true, Desc="Specific time to cache results (seconds). Default is 60 seconds."}}
help.set{input_function=F, help_data=Help}
end
local function deleteObject(S, T, ObjectName)
local Live = not iguana.isTest() or T.live
local Path = S.instance_url..
'/services/data/v20.0/sobjects/'..ObjectName..'/'..T.id
local Headers={}
Headers['Content-Type']='application/json'
Headers.Authorization ="Bearer ".. S.access_token
local Returned = net.http.put{data=json.serialize{data=T}, method='DELETE',headers=Headers,
url=Path,live=Live}
return ParseResult(Returned)
end
function GenerateDeleteMethod(Name, Info)
local FName = Name..'Delete'
salesmethods[FName] = function (S,T) return deleteObject(S,T,Info.object) end
local F = salesmethods[FName]
local Help = {}
Help.Desc = "Delete a "..Name
Help.ParameterTable = true
Help.Parameters = {}
Help.Parameters[1] = {id={Desc="Unique id of "..Name.." that will be deleted."}}
Help.Parameters[2] = {live={Opt=true, Desc="Set to true to make this command work in the editor. Default is false."}}
help.set{input_function=F, help_data=Help}
end
function BuildMethods(Objects)
for K,V in pairs(Objects) do
GenerateListMethod(K,V)
GenerateModifierMethod(K,V)
GenerateDeleteMethod(K,V)
end
end
BuildMethods(objectDefs)
Back to: Intro