I’m not sure if this concept is obvious when reading my previous post, so I thought I’d run through it with a specific example.
Let’s say that you’ve created what can only be described as an exemplary application in the form of a Managed Package. Although your user interface is beyond compare, you’d also like to populate some of your core objects with example data. Some options that immediately spring to mind are:
- Get the person that installs the package to call you, and you can do it post-installation.
- Get the person that installs the package to create sample data manually post-installation.
- Give the user a “Start Here” page with a call-to-action – such as a commandButton – that fetches the data from some API and parses into object data.
Option 3 is pretty excellent, especially now that you can package Remote Site Settings but I think we can do one better. And when I say better I mean simpler and with fewer potential points of failure.
The Premise
I postulate that we can use a version of option 3 that makes the implementation more robust by using Static Resources. This is my second post in quick succession on Static Resources, I am championing their cause, and if they were sentient and physical I think we might even exchange BFF bracelets. Sentiment aside we’ll need the following ingredients for this bodacious dish:
- A simple CSV mark-up language
- Plaintext CSV formatted files, uploaded as Static Resources
- A class to interpret the mark-up
- 1 x Apex Controller + 1 x Visualforce Page
A CSV Meta-language
Before we can populate objects from CSV files I’d like to stipulate some requirements:
- The tool must be able to parse data from different objects in the same CSV file
- The tool must be able to populate any number of object fields
- The tool must be able to relate object records if required
The language itself is very simple.
- Key-value pairs denoted by key : value.
- Some key names are reserved by the parser i.e. object (the object name), relatedTo (the parent object details) and extId (the Id that identifies the record in the parser)
- The relatedTo value has a special syntax i.e. relatedFieldName#parentObjectExtId
- All other key-value pairs represent an object field name and a corresponding value
Here is some text that conforms to these standards:
object:Account, name:CardMunch, extId:A01
object:Contact, firstname: Wesley, lastname: Nolte, relatedTo: accountId#A01, extId:C01
object:Contact, firstname: Jeffery, lastname: Douglas, relatedTo: accountId#A01, extId:C02I’ve dropped these lines in a text file and uploaded it as a static resource.
The Parser
Parsers aren’t the most fun to write so I’ve elected to leave out most (all?) error checking. The intent of the code should be quite clear.
/*
* Make sure your class is using the latest API version. Some features in this class
* will only work with API v19 or higher.
*/
public class CsvToSObjectParser {
private final String RESOURCE_NAME = 'object_csv';
private List<String> lines = new List<String>();
// Map instead of list since I need the .remove() method AND
// Map instead of set since DML can't be performed on set of SObject
private Map objsToInsert = new Map();
// A map to associate our external Ids as defined in the CSV with SObject Ids.
// This helps the code associate related objects. Using two similar maps
// since this one will hold ALL records (think reference-Map) but the one
// above holds a running list of uncommited records (think action-Map)
private Map extIdMap = new Map();
public StaticResource theStaticResource{get;set;}
public CsvToSObjectParser(){
fetchResource(RESOURCE_NAME);
}
public CsvToSObjectParser(String resourceName){
fetchResource(resourceName);
}
// Run over the lines in the files
public List parseCsv(){
String bodyText = theStaticResource.body.toString();
// Break the body into it's constituent lines
List bodyLines = bodyText.split('\n');
for(String line: bodyLines){
// Ignore comment and empty lines
if(line.trim().length() == 0){
continue;
}
System.debug('Key-value pair: '+line);
SObject s = munch(line);
}
List objs = objsToInsert.values();
return objs;
}
// Robotic muncher. Eats CSV lines and poops
// SObjects.
private SObject munch(String s){
SObject obj;
// Rearrange the CSV line into a list of string values
List keyValuePairs = s.split(',');
String eId;
for(String pairString: keyValuePairs){
// Some boilerplate splitting
List<String> pairs = pairString.split(':');
String key = pairs[0].trim(); // Don't forget to Trim!
String value = pairs[1].trim(); // Waste not, want not.
// Reserved keyword in the CSV markup - used
// to denote the object name
if(key == 'object'){
obj = createSObject(value);
// Reserved keyword - denotes the parent record Id
} else if(key == 'relatedTo'){
// More boilerplate
List referenceFields = value.split('#');
String fieldName = referenceFields[0];
String extId = referenceFields[1];
// Find the parent record. Now here we violate the
// 'No DML in a loop' golden rule because the parent
// record Id is required for the association. There is
// way to get around this by using a few lists and a map
// or two. I don't deem it necessary since I have direct
// control over the number of records in the sample file,
// but it'll make a fun exercise for y'all in your spare
// time
SObject parentObj = extIdMap.get(extId);
if(parentObj.id == null){
insert parentObj;
objsToInsert.remove(extId);
}
obj.put(fieldName, parentObj.id);
// Reserved keyword - used to associate my CSV record
// Id and the salesforce record Id
} else if( key == 'extId' ){
eId = value;
// Everything else i.e. the real field values
} else {
obj.put(key, value);
}
}
objsToInsert.put(eId, obj);
extIdMap.put(eId, obj);
return obj;
}
// Helper that instantiates a generic SObject
private SObject createSObject(String objectName){
Schema.SObjectType t = Schema.getGlobalDescribe().get(objectName);
SObject s = t.newSObject();
return s;
}
private void fetchResource(String resourceName){
try{
theStaticResource = [SELECT id, Body FROM StaticResource WHERE name = :resourceName];
}catch(System.QueryException e){
System.debug(e);
// You should always let the admins/devs know if
// something unexpected happened.
// e.g. ExceptionUtils.mail(Administrator);
}
}
}Execution! (or “Off with his Head!”)
You have some options here if you want this to run post-deployment. You could for example:
- Have a button in one of your pages that kicks off the process and get the user to click this.
- Have a page that itself has an action method that kicks off the process. I’d imagine this being your “Default landing page” for your app.
Where ever the action is called from, the core code should look something like this:
CsvToSObjectParser ctop = new CsvToSObjectParser();
List<SObject> objects = ctop.parseCsv();
insert objects;And should result in:
Easy as pie eh? You might even choose to insert your Custom Settings this way!
In Summary
So what have I demonstrated here? At least 2 concepts:
- The use of generic SObjects in a polymorphic way
- Using static resources to populate object data programmatically
But what demons loom in the lessons of this post?
- Make sure your Apex class API is up to date, we’re using some new-ish features here around SObject instantiation.
- Scripted statements limits. If you’re creating tens of thousands of records you might get in trouble with these. You might see some gains if you change your CSV-represented data rows to use an XML schema instead.
- I’ve used a CSV file for my data because it’s very quick to do. It’s not necessarily the best way but demonstrates the concept well. Additionally my parser probably has a few holes too but it’s (as always) a time VS reward trade-off. I think I’ve found a good middle ground.
- You cannot perform DML on a Set of SObjects, so work-arounds are sometimes required.
Bravo, Wes. Thanks for sharing in detail.
Bravo, Wes. Thanks for sharing in detail.
I’ve often wondered if there is some way to set up test data this way. Maybe you could have another static resource w/ the test data and run the parser as part of the test setup? Limits are a bit tighter though so might have a problem. Thoughts?
I think you’d have to be mindful of the DML limits but everything else should be okay. Personally I have data factories that I use for testing, let’s me get all specific with my test data.
I’ve often wondered if there is some way to set up test data this way. Maybe you could have another static resource w/ the test data and run the parser as part of the test setup? Limits are a bit tighter though so might have a problem. Thoughts?
I think you’d have to be mindful of the DML limits but everything else should be okay. Personally I have data factories that I use for testing, let’s me get all specific with my test data.
Very slick. Love innovated solutions such as these.
Thanks J 🙂
Neat. How would you extend this enable user sharable data packs? What are the security implications?
Good questions. Off the top of my head I’d answer:
1. Programmatically create sharing rules using Apex. You could extend the parser and CSV-language to include an extra tag or two. Perhaps even give the user access to picklists and pull these into the parsing process.
2. The data would be created as the user it ran as so this might be managed by business process? In the case where you’d want to “auto-populate” your custom setting data it would be a requirement that an Admin opens the page/clicks the button/invokes the spell 🙂
Hey Wes,
This is very creative, and rather clever… but I’m curious.
We do a very similar thing in the setup of one of our applications, ie – have a VF ‘setup’ page with a button to create sample data…
public Pagereference Setup(){
// Create account
List account = [select Name from Account where Name = ‘Tquila Ltd’ limit 1];
if (account.size() <= 0)
{
account.add( new Account(Name = 'Tquila Ltd', Phone = '+44 203 13000',
Type = 'Technology Partner', Industry = 'Technology',
BillingCity = 'London', Website = 'www.tquila.com' )
);
insert account;
}
// Create CMS site Configuration
List siteconfig = [select Name from gateway__CMS_Site_Configuration__c where Name = ‘Gateway Demo Site’ limit 1];
if (siteconfig.size()<=0)
{
siteconfig.add( new gateway__CMS_Site_Configuration__c(Name = 'Gateway Demo Site', gateway__Default_Site_Template__c = 'gateway__CMS_Test_Page',
gateway__Site_Stylesheet__c = 'gateway_demo' ));
insert siteconfig;
}
ETC ETC….
It's pretty easy to accomplish this way… just wondered why the need to do the stat resource etc? is there something I'm missing about our approach?
Ben, there’d be a few advantages although your approach would be perfect most of the time. Some of them are:
1. Separation of data and controller. This is probably just a best practice thing but I’d say you should keep the creation of the data (controller) and the data itself (model) apart.
2. Point 1 implies this but you get more flexibility by using splitting out the Model and Controller of MVC i.e. to add more data with your approach requires a code change. With the static resource you’d just add one (or more) data lines.
3. Testing. This builds on point 2, but for every additional data row you programmatically create (as you have) you’d want to add a unit test case. With the parser – once it’s fully built and tested – you won’t need to write additional tests if you’d like to augment, add or remove data.
4. Probably not as big a deal as the other points but your code contains hardcoded String values – of course these could be replaced with constants but imagine you suddenly needed 100 records with different field values!
Damn! I wish I could get my code to poop sObjects! This is awesome!
Wes
I think this is cool.
I would like to know if this approach could be extended to custom object picklist values as well?
I like the standard picklist fields and the functionality that come with them, but as part of a package I would like to offer different config for the values, allowing people to choose.
Can you think of a way to configure picklist values from with Apex as part of deployment?
Metadata api is good, but I can’t get it working from within apex, calling it’s self…….
Thanks
Great question, and one I’ve troubled with before. The approach in this post is not the one you’re looking for though – unless you create VF components that are driven by data. Obviously then some post deployment configuration is needed.
What I’ve created before is a Setup Wizard that runs after deployment and makes a call out to a Google App Engine service that then calls back into the same instance through the metadata API. A fun little project but it’ll be an exercise of cost vs benefit for you I’m sure. I did this back in the day with GAE but I’d probably use Heroku if I did it today.
Wes
I think this is cool.
I would like to know if this approach could be extended to custom object picklist values as well?
I like the standard picklist fields and the functionality that come with them, but as part of a package I would like to offer different config for the values, allowing people to choose.
Can you think of a way to configure picklist values from with Apex as part of deployment?
Metadata api is good, but I can’t get it working from within apex, calling it’s self…….
Thanks
Hello, I’m trying to install this class in our instance but am getting a bunch of errors. Has anyone tried this out in the last 5 years and had success?
Thanks