Inserting PDF Attachments into Salesforce.com using Talend and iText

I recently presented at the local Chicago Force.com Developer Group on using Talend to move data into and out of Salesforce.com. One question I fielded was how about moving documents into Salesforce.com. Yes you can use Talend to do this! For this demonstration I am going to use a few components and show you how to get the class path to execute properly for the ETL job.

Use Case:

For this demonstration, we are going to dynamically generate PDF content inside our ETL job. We are going to extract all our Account records from SFDC and dynamically generate PDF content from the fields on the Account records. We will then inser that data into an Attachment record for each of the Accounts as a PDF document created using iText.

Step 1 - Create Your Salesforce.com Metadata Connection

The first step is to create your connection for SFDC metadata. I have a video on YouTube which shows you how can do this. For this example you will want to pull in the Accounts and Attachements objects from SFDC. After following the instructions in the video for creating your SFDC metadata connections you should have them in your repository like figure below.

Step 2 - Download iText PDF Library

You will need the iText PDF library for the portions of the ETL job which will generate dynamic PDF content. You will also need to add the iText jar file to you java build path. You can do this by using the User Libraries feature as shown below:

Talend -> Preferences -> Java -> Build Path -> User Libraries

Step 3 - Create a new Job Design and Load Java Libraries

Talend allows you to load external Java libraries into your job which can have code executed inside your job. The component to load external Java libraries is called tLibraryLoad. You should use this component as one of the first steps in your job to load the any dependent jars you need. In this case, we are going to load the iText PDF library, as well as Apache Commons Codec so that we can Base64 encode our Attachment file content (more on that later).

Using tLibraryLoad component to load Java jar files.

Step 4 - Query SFDC Account Records

This step we simply use the tSalesforceInput component to read Accounts. For details you can see the video above on how to the Account records.

Step 4 - Create the Account Record PDF using tJavaRow

The tJavaRow component is a very powerful component. It allows you to code functionality into your ETL job using Java. In this example we are using the iText PDF Library to generate a simple PDF. The thing to remember about tJavaRow is that the code will execute for each record in the input step.

When you add and connect your tJavaRow component to the tSalesforceInput component, the first thing you need to do is click on the Advanced Settings tab and add any import for libraries that you need. These classes should reside in the jars you loaded in the previous step inside tLibraryLoad component.

Import Libraries for iText and Apache Commons Codec

Once you have the imports setup like above, you can then go to the Basic Settings tab. This will present you with the tJavaRow code editor where you can add your code. The first thing I do is click "Sync Columns" and "Generate Code" buttons. This will automatically create the code to simply move all the data from the input into your output row for this component.

Auto Code Generation and Column Sync

After I have let Talend do the heavy lifting of generating my getter and setter code, I then add two new fields. One called 'FileName' and one called "Content_Body". These fields will hold the filename in the format "Account Name.pdf" and the actual file content as a Base64 encoded string.

Add Fields to the Mapping for the Content and File Name

 The Salesforce API's use Base64 Strings to encode and pass file content. That is the reason we need to use Apache Commons in our job, to convert the PDF bytes into a format that SFDC and ingest. Using the output and input variable in the code, we can generate a PDF using the simple iText PDF objects. Here is the complete code. All this code goes inside the tJavaRow component.

//Code generated according to input schema and output schema

output_row.Id = input_row.Id;

output_row.IsDeleted = input_row.IsDeleted;

output_row.MasterRecordId = input_row.MasterRecordId;

output_row.Name = input_row.Name;

output_row.Type = input_row.Type;

output_row.ParentId = input_row.ParentId;

output_row.BillingStreet = input_row.BillingStreet;

output_row.BillingCity = input_row.BillingCity;

output_row.BillingState = input_row.BillingState;

output_row.BillingPostalCode = input_row.BillingPostalCode;

output_row.BillingCountry = input_row.BillingCountry;

output_row.ShippingStreet = input_row.ShippingStreet;

output_row.ShippingCity = input_row.ShippingCity;

output_row.ShippingState = input_row.ShippingState;

output_row.ShippingPostalCode = input_row.ShippingPostalCode;

output_row.ShippingCountry = input_row.ShippingCountry;

output_row.Phone = input_row.Phone;

output_row.Fax = input_row.Fax;

output_row.AccountNumber = input_row.AccountNumber;

output_row.Website = input_row.Website;

output_row.Sic = input_row.Sic;

output_row.Industry = input_row.Industry;

output_row.AnnualRevenue = input_row.AnnualRevenue;

output_row.NumberOfEmployees = input_row.NumberOfEmployees;

output_row.Ownership = input_row.Ownership;

output_row.TickerSymbol = input_row.TickerSymbol;

output_row.Description = input_row.Description;

output_row.Rating = input_row.Rating;

output_row.Site = input_row.Site;

output_row.OwnerId = input_row.OwnerId;

output_row.CreatedDate = input_row.CreatedDate;

output_row.CreatedById = input_row.CreatedById;

output_row.LastModifiedDate = input_row.LastModifiedDate;

output_row.LastModifiedById = input_row.LastModifiedById;

output_row.SystemModstamp = input_row.SystemModstamp;

output_row.LastActivityDate = input_row.LastActivityDate;

output_row.CustomerPriority__c = input_row.CustomerPriority__c;

output_row.SLA__c = input_row.SLA__c;

output_row.Active__c = input_row.Active__c;

output_row.NumberofLocations__c = input_row.NumberofLocations__c;

output_row.UpsellOpportunity__c = input_row.UpsellOpportunity__c;

output_row.SLASerialNumber__c = input_row.SLASerialNumber__c;

output_row.SLAExpirationDate__c = input_row.SLAExpirationDate__c;

output_row.Location__Latitude__s = input_row.Location__Latitude__s;

output_row.Location__Longitude__s = input_row.Location__Longitude__s;

//CREATE PDF CODE - FROM ITEXT PDF EXAMPLE        

        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        Document document = new Document();

        // step 2

        PdfWriter.getInstance(document, bos);

        // step 3

        document.open();

        // step 4

        document.add(new Paragraph("Account Name: " + output_row.Name + "/nAccountID: " + output_row.Id));

        // step 5

        document.close();

        

        byte[] bytes = bos.toByteArray();

        String stringToStore = new String(Base64.encodeBase64String(bytes));

        output_row.Content_Body = stringToStore;

        output_row.FileName = input_row.Name + ".pdf";

Step 5 - Insert the PDF into the Attachment Record 

Finally we pass along the values using a tMap component and the tSalesforceOutput component for Attachments. We map the output row fields from the tJavaRow onto our tSalesforceOutput row.

tMap Component

The complete demo job looks like this:

Step 6 - Export the Job and Run

The last step is exporting the job and running it on your machine. If you export the job design as a zip, you can get to the AccountPDFJob.sh file which executes the job. This video shows it running and you can see the final generate PDF attachment in SFDC.

I hope this helps folks. There are a lot of uses for this. You could generate PDF documents out of SFDC for all sorts of things with this approach. You could build a job that merges your SFDC data with an ERP and create a PDF invoice for example. Or you could use the Chatter objects and post files to a persons Chatter feed. There are lots of scenarios.

My Salesforce Mobile Developer Challenge Entry 2013

I just entered my Developer Force 2013 Mobile Challenge entry. For this challenge I built a HTML5 Hybrid Application leveraging Salesforce Mobile SDK, Cordova (PhoneGap), NFC Plugin, and HTML5 Canvas for signature capture. Here is the demo video:

And here is the SourceCode: https://github.com/corycowgill/MobileFieldService

The general idea for the application is a mobile application that field engineers could use. The application uses NFC technology to scan a tagged device and display the relevant case information to the engineer. The general idea being that if you can use NFC tags on your products, it would allow the Field Service agent to simply touch his phone or table to the device they are servicing and see all the details automatically.

Here are some additional screenshots to show the simple UI using jQuery Mobile.

I also added HTML5 Canvas to do signature capture and upload as a attachment to the Case in SFDC.

There was so much more I wanted to do but just didn't have the time. Therefore I decided to focus my time implementing the NFC plugin and functionality. My current client work just didn't afford me the time I would have liked to dedicate to this personal project. For example I would have liked to add:

1. SmartStore for Offline Access. For example, when working at a client site in a basement or where signal strength is weak.

2. GPS and Geolocation. Basically for address directions / map and checkin.

3. Checkin / Checkout status pushes for Chatter.

4. Add backend Javascript Framework instead of inline HTML / JS that I used to rapidly build this project. I just didn't have the time to learn a new framework like Angular or Backbone.js. Next Time!

5. Additional Error Handling Logic

6. User Interface Updates & Design - Add a little flair.

I had a lot of fun creating this challenge. I may re-vist this just to complete those items above.

Salesforce Mobile Developer Challenge 2013!

The Salesforce Mobile Developer Challenge 2013 is live. I'm pretty excited to see the entries that folks come up with this year. You have until June 3 to finish your applications so you have a few weeks to get moving. There are a number of Mobile Packs that you can use to get started rapidly building applications.

I have started on my entry last weekend. Once I submit my application on June 3rd I'll post my GitHub Repo and the YouTube video on this entry so folks can see what I built and how.

Person Account & Contact Formula Fields

With the latest Spring 13 release of Salesforce.com Formula Fields were enabled for Person Accounts. For those who are unfamiliar, Person Accounts are the B2C (Business to Consumer) feature for managing accounts. It is often used in Financial Services implementations where a sales person may sell to both Institutions as well as individual investors. Person Accounts blend Accounts and Contacts objects in the Salesforce.com front end to appear as one record.

Under the hood Person Accounts are actually two distinct records, one Account and one Contact. The Account object has a field called "PersonContactId" which links the Account to the contact record. Therefore there is a link from the Account to the Contact record (Account.PersonContactId), and a link from the Contact record to the Account (Contact.AccountId). A number of fields from the Contact record are then made available on the Account object. Standard Fields from the Contact are available on the Account via the "Person" prefix. For example, Account.PersonMailingStreet or Account.PersonTitle bring up the values from the underlying Contact record. Custom fields on a Contact are brought up to the Account record with fields ending in __pc. For example, Account.ContactTotalWorth__pc for a field called "Contact Total Worth" on the contact record.

Previous to Spring 13 you could not use Person Account fields in a formula field. For example, on an Opportunity record you could not do a Formula Field like "Account.PersonMailingStreet" to bring the field onto the Opportunity record. Now you can do this for all the standard and custom fields with the exception of PersonContactID.

For some reason PersonContactID is not available in Formula Fields. This also applies to any Formula Fields on the Contact that may use the Contact ID.

So when creating Formula Fields for Person Accounts just remember that the Contact ID is currently not accessible.

Creating Mirror Relationship Records in SFDC with Apex Triggers

Reciprocal or Mirror Records Overview

In this blogpost I will be discussing creating reciprocal relationship records in Force.com using Apex Triggers. I'd first like to say that this use case is one that should be scrutinized before you implement it. Creating reciprocal or mirror relationships of records in Force.com can sometimes create duplicate records. To keep your managed data down, you would often like to avoid this process if possible. However there are times when you just have to keep record values in sync, so carefully evaluate your use case before implementing this. Now that I have the disclaimer out the way here we go!

When working with records in Force.com we sometimes have a need to create a reciprocal relationship between two records. For example, sometimes we may have a use case which involves keeping two records in sync. When you update Record A, you may need to update some corresponding fields on Record B. Below is a simple diagram illustrating this.

Force.com Data Model

The best way to implement this is to have a lookup field on the object which is a lookup to it self. In this case I have create a lookup field on contact called "Linked Contact" and related it to the Contact record as shown in the schema builder diagram below.

Apex Trigger Flow

Now that I have the data model configured for the reciprocal relationships, I need to keep the records in sync automatically. This means that whenever Contact record A is updated, make sure to update Contact record B. We can accomplish this via Apex Triggers when a record is inserted or updated to keep the lookup fields in sync, thereby allowing us to "link" the records. However, there is problem with this scenario.

In an Apex Trigger you only have acces to the records Id in the After context, which makes sense. There cannot be an Id until after the record has been created. So the flow would look like this:

The problem as you may see is that in a After context in an Apex Trigger you cannot update the original record! So in this scenario we cannot update Rec A in the Apex Trigger because we will get a read only error as shown below:

So you may be asking yourself how can we make this work in the Apex Trigger? The answer is quite simple actually but it is not clearly documented to my knowledge. If we create a new instance of an SObject in the Apex Trigger in memory using the Id of the newly created record as provided in the After Trigger context, we can perform an Update DML statement and not get a read only error! This is because in Apex, the SObject is seen as a new reference (even though the records have the same SFDC ID) and therefore is eligible for DML operations! The below snippet of code illustrated this working and not working.

List<Contact> originals = new List<Contact>();

if(mirrorResultMap.values().size() > 0)

{

for(Contact origContact : contactRecs.values())

{

Contact mirrorContact = mirrorResultMap.get(origContact.Id);

//origContact.Linked_Contact__c = mirrorContact.Id; //Link the Original Record tot he Mirror Record WILL FAIL

Contact origContactUpdate = new Contact(Id=origContact.Id, Linked_Contact__c = mirrorContact.Id); //This will WORK

originals.add(origContactUpdate);

}

//update contactRecs.values(); //Update the Records -> THIS WILL FAIL AS ITS ORIGINAL RECORDS IN MEMORY

update originals;

}

With this code in place we will no longer get an error message, and the two records will now be linked! You can see the lookup fields are now populated to reference each other.

This is useful as I said when you want to link two records together to keep them in sync on DML operations. They can be two records of the same type as I illustrated in this example, or you could keep two different objects in sync via lookups if you needed to. Its a handy skill set to know so go forth and code!

Full Code Dump:

Apex Trigger:

trigger ContactTrigger on Contact (after delete, after insert, after undelete, 

after update, before delete, before insert, before update) 

{

if(trigger.isAfter)

{

if(trigger.isInsert)

{

ContactTriggerHandler.processAfterInsert(trigger.newMap);

}

}

}

Apex Class (Trigger Handler)

public class ContactTriggerHandler 

{

public static void processAfterInsert(map<Id,Contact> contactRecs)

{

//Query for the existing mirror records

Map<Id,Contact> mirrorContacts = new Map<Id,Contact>([Select c.Id, c.Linked_Contact__c From Contact c where c.Linked_Contact__c in: contactRecs.keySet() ]);

List<Contact> mirrorInserts = new List<Contact>(); 

Map<Id,Contact> updateReciprical = new Map<Id,Contact>();

//Iterate over the Trigger New Records

for(Contact contactRec : contactRecs.values())

{

if(contactRec.Linked_Contact__c == null)

{

Contact mirrorContact = mirrorContacts.get(contactRec.Id);

if(mirrorContact == null) //If this record does not have a linked mirror record, create one and link it to this record

{

Contact c = new Contact(FirstName = contactRec.FirstName, LastName = contactRec.LastName, Description = 'Mirror Record', Linked_Contact__c = contactRec.Id);

mirrorInserts.add(c);

}

}

}

Map<Id,Contact> mirrorResultMap = new Map<Id,Contact>();

if(mirrorInserts.size() > 0)

{

insert mirrorInserts; //After Insert DML, the ID's will be populated

for(Contact mirrorInsert : mirrorInserts) //Store the results in a Map so we can now use these Id's to link the records

{

mirrorResultMap.put(mirrorInsert.Linked_Contact__c,mirrorInsert);

}

}

List<Contact> originals = new List<Contact>();

if(mirrorResultMap.values().size() > 0)

{

for(Contact origContact : contactRecs.values())

{

Contact mirrorContact = mirrorResultMap.get(origContact.Id);

//origContact.Linked_Contact__c = mirrorContact.Id; //Link the Original Record tot he Mirror Record WILL FAIL

Contact origContactUpdate = new Contact(Id=origContact.Id, Linked_Contact__c = mirrorContact.Id); //This will WORK

originals.add(origContactUpdate);

}

//update contactRecs.values(); //Update the Records -> THIS WILL FAIL AS ITS ORIGINAL RECORDS IN MEMORY

update originals;

}

}

}

I'll be presenting at Dreamforce 2012!

I'm happy to announce that I will once again be presenting at Dreamforce! This year I will be presenting on NFC and the Force.com Mobile SDK. I'll be speaking about a simple Android Hybrid Application I wrote that uses NFC, PhoneGap, PhoneGap Plugins, and the Force.com REST API to upsert vCard encoded Tags into Salesforce.com. Attendees will leave my session with all the knowledge they will need to get started on their own mobile applications with NFC!

Dreamforce is an awesome event. Mind blowing. I'm excited to be speaking again and look forward to seeing everyone there. I will be in the Developer Zone a good portion of time, so if your at Dreamforce tweet me (@corycowgill) and we can meetup for coffee or chat about Force.com, Mobile Development, or Wealth Management \ Private Equity SFDC implementation best practices!

See you in San Francisco in September!

Activating Touch.Salesforce.com

You can get Touch.Salesforce.com activated in your orgs by sending a request via the Help menu in Salesforce. Simple send a feature activation case request through the self service portal and Salesforce will enable it.

I've been playing around with it and its pretty slick. As with most people, I'm chomping at the bit for the edit functionality, as well as the offline functionality. Currently in the public beta you can only add Tasks and Chatter Posts. Hoping when they go GA out of Beta that it will be enabled.

One thing to note is you must have Chatter enabled. Sorry Chatter haters, but if you want to roll with touch you'll need to enable it and embrace the future. ;)