Salesforce Adapter Mk I

Introduction

This article will be useful to anyone who is integrating to a large web service API. It covers concepts of using caching to improve performance and auto-generating an adapter supporting – Create Read Update Delete – ‘CRUD’ operations for the application. This article is based on a talk I gave September 24th 2015 at our third annual user conference in Toronto.

The techniques in here would be helpful for our vendor customers that are looking to make it easy to connect up their own applications with good web APIs.

The highly popular salesforce.com CRM application has been coming up on our radar a lot lately. We’ve had a number of existing customers and prospects that have had projects where they needed to interface with it.

It turns out that salesforce.com itself has huge ambitions in healthcare. The CEO of salesforce.com, Marc Benioff, has been quoted as saying that healthcare will be their next billion dollar business. Since signing up for my developer account I learned that they have an entire healthcare cloud application which I have to admit does look pretty smooth. Watch out Epic, Cerner and McKesson! Yet another big player is coming to healthcare!

So anyway – back to interfacing. This article is focused on the core salesforce.com CRM application. A couple of weeks ago I found myself sitting in a customer’s office needing to help them connect up salesforce.com with their application. So that got me started. Afterwards I went a bit further and wrote a much better adapter and used it as the basis for my presentation at our user conference.

This article addresses solving three main problems with making a good adapter:

Firstly getting the credentials set up to connect to the saleforce.com API. I found this a little hard to follow the first time, but I think you will find it easy to follow my instructions to get yourself set up.
Secondly saleforce.com is a big application. There are over 100 built in objects with lots of properties that you will want to alter with their RESTful web api. I found a great way to code-generate an entire ‘CRUD’ API (Create Read Update Delete) using the meta data supplied by some helpful API calls into their API.
Thirdly like any web service API there is latency. Web queries can take a few seconds to return, which is fine for production, but it doesn’t play that nicely with being able to get quick annotations, auto-completion etc. writing the code in the translator IDE. By using caching with the store module (Iguana 5 documention) I was able to make the whole user experience delightfully responsive in terms of writing code for the integration.
The techniques I show in points 2 and 3 I think will be of great use to anyone who is building out an adapter for any web API in Iguana. A lot of our folks in our vendor community were excited at the conference about the idea of leveraging the same techniques I use here to make adapters for their own applications.

Connecting to salesforce.com [top]

Salesforce.com does support some ways to make an application like connection to it such that the user grants permission only to specific resources via the OAuth protocol. For this exercise I have not done that. I did it using a simpler password based authentication technique. I know customers that have done it and it could be the subject of another future article.

In order to get our hands on what salesforce.com calls a consumer_key and consumer_secret which are required to authenticate with the salesforce.com RESTful API, there are still quite a number of steps to go through.

Firstly you can go to: https://developer.salesforce.com/signup and sign up for a free developer account.

  1. Authenticate your email address, using the initial screen:
  2. Click the link in the confirmation email to open the Getting Started screen.
  3. Expand the Create option in the Build menu at the lower left, then click on Create>Apps:
  4. Click on the New button for Connected Apps at the bottom center of the page:
  5. Fill out the first two sections only, Basic Information and API (Enable OAuth Settings).
    • Use similar information to that shown below.
    • Notice the dummy “Callback URL”? The screen requires it even though we are not actually using the OAuth callback URL in this adapter.
      Note: You need to use a secure URL “https://” rather than “http://”
    • I selected full access for the adapter to make development easier (you can add restrictions later).
    • When you have filled out the fields, click the Save button at the top of the form.
  6. Click on the Continue button, in the New Connected App screen:
  7. Now we need to relax the IP restrictions on who can connect to the API.
    • Click the Manage button, to manage your app:

      Tip: If you went exploring after point 6, and got lost trying to get back the page with the Manage button (like I did).

      Then do the following to manage your app: Click the Setup link (top right on all screens), choose Build>Create>Apps from the menu at the left bottom of the screen, then click on the Manage link to manage your app.


      You can can also click the Connected App Name link to get back to the exact same screen with the [Edit], [Delete] and [Manage] buttons.

      Alternatively: Go to the list in Administer>Manage Apps>Connected Apps and click on the Master Label link which takes you directly to the Manage screen for the App.

    • Click the Edit button so we can change the “IP Relaxation:” setting:
    • Select Relax IP restrictions from the drop down, then click Save:
  8. We’re almost done. The last thing to do is to get the “consumer key” and “consumer secret” – our prize!
    • Click the the Back to List: Custom Apps link:
    • Then click on the link for your App in the Connected Apps panel at the bottom of the page:

      Tip: If you get lost trying to find the above page (like I did), do the following: Click the Setup link (top right on all screens), the choose Build>Create>Apps from the menu at the left bottom of the screen.

      Note: There is a similar list in Administer>Manage Apps>Connected Apps but this link in this list takes you to the wrong place (i.e., the Manage page not the Apps page).

    • By default the consumer secret is hidden, just Click to reveal as suggested:
    • Now you can see the Consumer Key and the Consumer Secret, we will need both of these to connect to Salesforce:

So with the password authentication method I used for this article we need these two values, plus a username and password for a user. I show a very simple “Hello World” example of using the saleforce.com API here:Screen Shot 2015-09-17 at 10.55.46 AM

Notice a few things:

  1. We have to query the salesforce API for an “access_token”. This then needs to be passed in with subsequent calls using an HTTP “Authorization” header.
  2. The API is using a SQL like query language called SOQL – Sales Object Query Language.

The form of the JSON data which comes back from getting the access token is like this:

Screen Shot 2015-09-17 at 11.07.18 AM

And the form of the JSON data which comes back from the query is like this:

Screen Shot 2015-09-17 at 11.08.40 AM

The sales force force API makes use of these HTTP methods to do the various CRUD operations:

  1. Create – HTTP PATCH.
  2. Read – HTTP GET
  3. Update – HTTP PATCH
  4. Delete – HTTP DELETE

Because the salesforce.com API is very uniform it’s easy to auto-generate an entire API to all the known objects in the API. I will cover that shortly, but first let’s talk about caching.

Caching data [top]

The translator environment is built on the assumption that the transformation of data is happening in a fraction of a second with a linear flow of data coming in and being transformed out into a destination.

That allowed us to build the translator IDE so that we execute the script from start to finish with every change and generate all the useful auto-completion and annotations that make the environment really convenient for doing integration.

Web services do throw a little bit of a spanner in the works – even the fastest of them can have a few seconds of latency. That slows down the speed at which the translator can generate auto-completion and annotations. It makes for a much less smooth and convenient experience.

This is where a bit of caching can give a big benefit. The store module (Iguana 5 documentation) makes it easy to cache the results coming back from HTTP calls. The improvement in usability is spectacular. In my salesforce.com adapter I wrote some helper functions to wrap around the HTTP calls to cache the authorization token and the results of HTTP GET queries.

Here’s a couple of examples, the first is caching of the access token:

Screen Shot 2015-09-17 at 11.37.26 AM

And the next example shows caching for a general HTTP GET:

Notice how the caching only happens within the context of the editor? This is when we need it – as we are writing the code and making use of auto-completion and annotations. Caching is switched off when we run the code in production.

Generating an API [top]

To do this I needed to use a couple of more advanced features in the Lua language called “closures” and “meta-tables” together with the translator’s own built in help system.

Don’t panic! I will gradually introduce these concepts one by one with some simple examples so it should be easy to understand. Closures and meta-tables are a pair of features in Lua that allow one to do ‘object orientated’ programming in the language.

Closures are not unique to Lua – other languages like Javascript support closures also. In Lua closures are functions which can access variables in the scope that they are created. We can create functions on the fly in Lua and this is technique I used in my sales force adapter to make the methods to access all the salesforce.com objects.

Have a look at this code fragment:

The code in question loops through the array listing ‘tools’ and then makes a method which is <tool name> + “Select”.

Notice how the functions use the “self” object which is the making use of the colon syntax in Lua.

The table “R” returned from the MakeMethods function is in fact a table of functions. If you were to click on it and browse a window of it’s contents this is what you would see:

Screen Shot 2015-09-25 at 10.04.49 PM

Like many other dynamic languages like JavaScript and Python functions can be stored as values in Lua. This next screenshot shows the how it all works nicely with auto-completion:

Screen Shot 2015-09-25 at 10.09.20 PM

I strongly encourage you to try out the code for yourself within a translator. Here it is in a format you can copy and paste:

-- We are using closures here to generate a set of methods
function MakeMethods(Owner,MethodList)
   local R = {}
   R.owner = Owner
   trace(R)
   for i=1,#MethodList do
      local Tool = MethodList[i];
      trace(Tool)
 
      R[Tool.."Select"] = function(self) return self.owner.." selected a "..Tool end 
    end
 return R
end

function main(Data)
   local MethodList={"rake", "shovel", "pick", "hammer"}
   trace(MethodList)
   local O = MakeMethods("Jake", MethodList)
   O:hammerSelect()
   O:rakeSelect()
   O:shovelSelect()
   O:pickSelect()
end

Now we can take this to next level using what Lua calls meta-tables. You can set a meta-table for a table in Lua. We’re going to use just one feature of meta-tables which is to have it define a set of default properties for a table. Here’s the code:

If you click on the MetaTable which is returned from MakeMetaTable, this is what you would see:

Screen Shot 2015-09-25 at 10.22.54 PM

For a meta-table the __index key has a special meaning:

  1. If you look up a property on a table and it has a meta table.
  2. And that meta table has the entry __index.
  3. And that entry is a table of values.

Then these will treated as properties of that table. You can learn more about this property in the Lua documentation.

Notice how we can use the same meta-table for more than one ‘object’ table – in this example the Mary and Jake tables. The advantage of the meta-table is just that’s it’s a more efficient way to define the set of methods since we only need to do it once and then we can reuse them again and again for many Lua tables. In this context you can think of Lua tables as ‘objects’.

If you know JavaScript well you might notice that Lua meta-tables are very similar to JavaScript prototypes.

Go ahead try out the code yourself and play with it. You can copy it into a translator right from here:

-- We are using closures here to generate a set of methods for a method table
function MakeMetaTable(MethodList)
   local R = {}
   for i=1,#MethodList do
      local Tool = MethodList[i];
      trace(Tool)
 
      R[Tool.."Select"] = function(S) return S.owner.." selected a "..Tool end 
    end
    local MetaTable = {}
    MetaTable.__index = R
    return MetaTable
end

function main(Data)
   local MethodList={"toothBrush", "pick", "hammer"}
   local MetaTable = MakeMetaTable(MethodList)
   local Mary = {owner="Mary"}
   local Jake = {owner="Jake"}
   setmetatable(Mary, MetaTable)
   setmetatable(Jake, MetaTable)
   Mary:hammerSelect()
   Jake:hammerSelect()
   Jake:toothBrushSelect()
end

The next layer to add is support for the translator’s help system. This is what gives the nice informative information and auto-completion of arguments for help functions. The beauty of the system is that one can generate help programmatically also rather than coding it up by hand.

If you look at one of the HelpInfo objects generated in the help method it looks like this:

Screen Shot 2015-09-25 at 10.41.00 PM

This means that in the auto-completion for the objects we see descriptions of the methods:

Screen Shot 2015-09-25 at 10.41.18 PM

And we select one of these functions we get suggestions on the names for the parameters:

Screen Shot 2015-09-25 at 10.41.47 PM

Here’s a version of the code you can copy and play with inside your own translator:

-- We are using closures here to generate a set of methods for a method table
function MakeMetaTable(MethodList)
   local R = {}
   for i=1,#MethodList do
      local Tool = MethodList[i];
      trace(Tool)
 
      R[Tool.."Select"] = function(S) return S.owner.." selected a "..Tool end 
   end
   local MetaTable = {}
   MetaTable.__index = R
   return MetaTable
end

function MakeHelp(MethodList, MetaTable)
   local Methods = MetaTable.__index;
   for i=1, #MethodList do
      local HelpInfo ={}
      local Tool = MethodList[i]
      local MethodName = Tool.."Select"
      trace(Tool, MethodName)
      HelpInfo.Desc = "Select a "..MethodList[i]
      HelpInfo.Title = MethodName
      HelpInfo.ParameterTable = true
      HelpInfo.Parameters = {}
      HelpInfo.Parameters[1]={live={Desc="Active live tool"}}
      trace(HelpInfo)
      help.set{input_function=Methods[MethodName], 
      help_data=HelpInfo}
   end
   help.example() 
end

function main(Data)
   local MethodList={"rake", "shovel", "pick", "hammer"}
   local MetaTable = MakeMetaTable(MethodList)
   MakeHelp(MethodList, MetaTable)
   local Mary = {owner="Mary"}
   local Jake = {owner="Jake"}
   setmetatable(Mary, MetaTable)
   setmetatable(Jake, MetaTable)
   Mary:hammerSelect()
   Jake:hammerSelect() 
end

So the salesforce.com adapter that I wrote makes use of all these techniques to code generate methods to handle the CRUD operations on all the saleforce.com objects we are interested in manipulating.

I came up with this format for defining a object:

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="Account ID 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."
 }}

Now the great thing is that’s not necessary to completely define this by hand. salesforce.com has some handy methods in it’s RESTful API that will give you all the properties of a given object. I was able to write a helper function to go retrieve that and print out my formatted meta data definition. The only hassle was that returned meta-data doesn’t include descriptions of the columns – I had to enter that information by hand.

Notice how a couple of the fields have trailing __c at the end? These are custom fields and the great thing about salesforce.com is that it makes it simple to add these custom fields to the GUI and easy access them from the RESTful api.

So putting everything together meant combining:

  1. Caching using the SQLite store module.
  2. Dynamically querying the meta data definitions.
  3. Generating methods with help using closures, meta-tables and making help on the fly.

Quite a few pieces to put together but the results are pretty fantastic. Let’s look at the actual adapter and how to use it.

Using the salesforce.com adapter [top]

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:

  1. limit – this is the maximum number of records to return.
  2. where – this is SOQL (SQL) like WHERE clause to use to filter the results.
  3. 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)

 

Leave A Comment?