#force.com

Unlock the full potential of Force.com® with Algoworks' proven methodology! Discover how our expert team leverages industry best practices to deliver exceptional Force.com® solutions.

Unlock the Power of Salesforce with Expert Force.com® Solutions for Seamless Business Growth.

Unleash Your Business Potential!

Translate ideas into deployed applications in record time using Force.com. Spark productivity and boost innovation across your business.

Senior Salesforce Developer, Raleigh, NC, USA

Need resources with status: EADs(H4/L2), GC, USC

Send resume to: [email protected]

Senior Salesforce Developer

Raleigh, NC (Non-Local, OK)

C to P – 6 month contract to perm, will sponsor

OPT /H1 not accepted but all other visas welcome

Phone/Skype

Requirements:

Qualifications

BS in computer science or equivalent or working towards a BS degree

MS in computer science or equivalent preferred.

6-10…

I recently started a business and the premise is that we can integrate everything and automate everything. This is in fact the goal and the driving force behind the business. So to start off, why not take on a project that requires integrating an application with the two monoliths, Quickbooks and Salesforce.  The client wants a customer portal that will show quickbooks data and salesforce data based on the customer that is signed in.  I played around with two potential ideas 1. Download all the client data and seed a database that we will control and maintain.  A daemon (or similar) will be used to poll Quickbooks and Salesforce for updates to the database. 2. Make an api call to quickbooks and salesforce with the user data that we store in a database when the user logs in. The results of the query will then be stored in a database as JSON with the account name as the key and a timestamp, sort of like an extended cache system. Then, after a certain amount of time passes, the past query is determined expired and a new query will be made when the user logs in.

We investigated both of these options and spoke more with the client about their expectations and what we found was that 

quickbooks api cannot work without getting an application certified and registered with them

there will not be a lot of traffic or a lot of updates to the data in Salesforce or Quickbooks and

The client has the expectation of a tight turn around (under 2 weeks) 

In the end, the decision was made to go with version 2.  There were a lot of hurdles, here is a rundown on how we made it work with the following technologies: Google App Engine (more on this here) Python Flask Firebase (more on this here) AWS S3 Excel Spreadsheets Salesforce API + SOQL Python and Flask are used to manage the backend of the service. My previous post discusses the use of AJAX and jQuery to interact with the backend. After a user is successfully authenticated using firebase (blog post here), the backend has information about the user’s email, name and their token. This is stored in a claim object, but the claim object does not have information about the user’s salesforce id, quickbooks id, region etc. because this is information the user does not know and will not sign up with. In this case we had to make the connection between the two.  So the first hurdle is managing the users data needed for quickbooks and salesforce. In this case we created a google datastore model of the user’s information and will use the user’s email to query the information.  # [START User] class User_ext(ndb.Model):     # NDB model class for a user, which contains their information    sf_id = ndb.StringProperty()    qb_company_name = ndb.StringProperty()    qb_emails = ndb.StringProperty()    account_owner = ndb.StringProperty()    account_email = ndb.StringProperty()    region = ndb.StringProperty() # [END User] now when a user logs in we can get their sf_id and the qb_company_name which we will use for getting their information. This information can be easily added or updated using an admin backend like flask-admin. or programmatically added by seeding the database. Now that we have the user information we will make our queries to salesforce and quickbooks. 

Salesforce

Salesforce ended up being the easier of the two. Using a python module called simple-salesforce I could easily access the salesforce instance and make salesforce queries. There are some particulars with SOQL that make it more difficult to use than a vanilla SQL. I am not an expert here, but with the developer tools from salesforce and some nice walkthroughs online, it is fairly easy to get what you want back. I ended up querying for the opportunity line items for a particular client using their salesforce id. Here is a very straightforward walkthrough of getting started with simple-salesforce  from Salesforce themselves.  Once the data was pulled from salesforce it was stored into an object that would be sent back to the front end with the other user information. here is an example code snippet of a query: sf = Salesforce(username='username', password='password', security_token='this-is-my-token') #first query for opportunity line items by account id op_id_payload = sf.query_all("SELECT id, account.name from opportunity where account.id = "+ "'" + customer + "'")['records'] # get list of opportunity ids op_ids = [] for op_id in op_id_payload: op_ids.append(op_id['Id'])

# another query for the product information based on opId data = [] for opId in op_ids: op_line_item = sf.query_all("SELECT Id, Name, (Select Id, Quantity, TotalPrice, PricebookEntry.Product2.ProductCode, PricebookEntry.Product2.Name From OpportunityLineItems) From Opportunity Where Id = " + "'" +opId+"'")

Quickbooks

Quickbooks quickly became another story... After playing around with a few different python modules and understanding how to query from quickbooks, it became apparent that we couldn’t actually go this route because releasing a production app for quickbooks would take too long and too many resources. We had to find another way. The solution - have the client upload 2 reports generated from quickbooks every week or so. The reports would be parsed based on the customer information and it would essentially act like a datastore. the question became, what was the easiest and fastest way to allow our users to upload the required documents and how should they be parsed because there was a lot of mismatching information between inventory on hand, company name and products on invoices. A very robust method was needed to match this information.

Upload 

S3 to the rescue. using s3 and flask admin, we could create an admin page that allowed the users to upload the documents to an s3 bucket. This is easy to set up with

tutorials on s3

and a

flask-admin plugin

. 

Download

Based on the workflow we decided upon, when a user logs in, the request will then be made first to the database, and if nothing exists or the record is too old, it will get the information from the latest uploaded files (transactions and inventory) with the correct name in the s3 bucket. 

To do this, our flask backend requests the s3 resource and then will read the excel document . Below is an example of reading an excel file with xlrd and urllib from an s3 bucket:

def read_excel(filename, header_row = 0, start_row = 1, start_col = 0):

opener = urllib.URLopener()

myurl = "https://s3-<region>.amazonaws.com/<bucket-name>/" + filename

myfile = opener.open(myurl).read() # Open the workbook

xl_workbook = xlrd.open_workbook(file_contents = myfile)

# List sheet names, and pull a sheet by name

sheet_names = xl_workbook.sheet_names() sheet = xl_workbook.sheet_by_name(sheet_names[0])

# Pull the headers using the provided inputs by index

headers = [sheet.cell(header_row, col_index).value for col_index in xrange(sheet.ncols)]

# make a dictionary with the headers

dict_list = []

for row_index in xrange(start_row, sheet.nrows):

d = {headers[col_index]: sheet.cell(row_index, col_index).value

for col_index in xrange(sheet.ncols)}

dict_list.append(d) return(dict_list)

return dict_list

Parsing

Pandas, numpy and google app engine. I usually use pandas and numpy to manipulate data in csv or excel formats. Its quick and easy and saves a lot of time. Unfortunately google app engine only allows numpy==1.6.1, which does not play well with pandas, which requires numpy=>1.7.1. So this meant I had to do it the long way, but what is probably the better method with less dependencies. Part of this is in the example listed above. 

The next hurdle was matching the names. There would be one variation of the company name from our database of customers and we had many variations of the name in the transactions spreadsheet as well as the inventory spreadsheet. I used a combination of regex, fuzzy string matching (thank you

fuzzywuzzy

), tuning and unambiguous name portion matching to create a ranking algorithm of whether names were a “match” or not. The matches are then returned as objects which are stored in the database of records with a timestamp and then returned to the front end with other user information. 

Conclusion