Scenario D Batch Import

In this scenario, an additional rule is created to import expense lines from a spreadsheet and chunk them into batches. If a parent record for an object does not exist, it is created.

Problem

You are a finance officer and want to import a spreadsheet full of expense lines to ClickLink so that you do not have to reenter the information. The spreadsheet contains date, description, GLA, Dr and Cr columns like this:

Example Sheet Containing Expense Lines

Date	Description	GLA	DR	CR
30/11/2015	November Credit Card Expenses VJG	Subscriptions	100.00
30/11/2015	November Credit Card Expenses VJG	Office Stationery	100.00
30/11/2015	November Credit Card Expenses VJG	Credit Card Control Account		200.00
30/11/2015	November Credit Card Expenses PP	Travel	150.00
30/11/2015	November Credit Card Expenses PP	Subsistence	200.00
30/11/2015	November Credit Card Expenses PP	Credit Card Control Account		350.00
30/11/2015	November Credit Card Expenses AG	Hotel	60.00
30/11/2015	November Credit Card Expenses AG	Subscriptions	5.00
30/11/2015	November Credit Card Expenses AG	Subscriptions	10.00
30/11/2015	November Credit Card Expenses AG	Credit Card Control Account		75.00

Solution

Create an object to import the data into and create ClickLink rules to convert that data into journals then use the Apex data loader to import the data into the objects. In this scenario, you first need to create Journal Import Data and Journal Batch custom objects to import the data into.

1. Create a Journal Import Data and Journal Batch custom objects and add custom fields to import the data into if they do not already exist:

   The Journal Import Data object should contain these fields:

   Expected Journal Import Data Object Fields

   Field	Data Type
   Amount	Number(16,2)
   Date	Date
   Description	Text(50)
   GLACode	Text(100)
   Journal Batch	Master Detail relationship with Journal Batch.
   Processed	Checkbox

   The Journal Batch object should contain these fields:

   Expected Journal Batch Object Fields

   Field	Data Type
   Amount	Roll-Up Summary (SUM Journal Import Data)
   Date	Date
   Description	Text(50)
   Journal Import Data	Lookup to Journal Import Data object
   Negative Amount	Formula (Number)
   Number of Children	Roll-Up Summary (COUNT Journal Import Data)
   Processed	Checkbox

   Note:

   Create a custom tab for the Journal Batch object and Journal Import Data objects. You need these to view the imported data and execute the ClickLink rules.

2. Create an Apex trigger on the Journal Import Data object to handle the imported data. The Apex trigger may look something like this:

   trigger JournalImportDataIntegrationRuleEngine on JournalImportData__c (before insert)
   {
   ffirule.IntegrationRuleEngine.triggerHandler();
   }

3. Create an Apex trigger on the Journal Batch object to handle the imported data. The Apex trigger may look something like this:

   trigger JournalBatchIntegrationRuleEngine on JournalBatch__c (after delete, after insert, after undelete, after update, before delete, before insert, before update)
   {
   ffirule.IntegrationRuleEngine.triggerHandler();
   }

4. Create a master ClickLink rule to Convert Journal Import Data to Journal Batches. For instance:

   Recommended Values for the Create Journal Batch ClickLink Rule

   Item	Value
   ClickLink Rule Name	Create Journal Batch
   Source Object	JournalImportData__c
   Source Object Name Field	Name
   Target Object	JournalBatch__c
   Target Object Name Field	Name
   Target Relation with Source	One to Many
   Processed Field on Target	Processed__c
   Relation Threshold	20
   Relation Count Field on Target	NumberofChildren__c

   Note:

   In this rule, a new batch is created when the number of journal lines reached 20.

5. Create ClickLink mappings for the rule you created in the previous step. For instance:

   Recommended Mappings for the Create Journal Batch ClickLink Rule

   Target Record Based On	Mapping Type	Source Field	Target Field
   Source Record	Source Field	JournalBatch__c	Id
   Source Record	Source Field	Date__c	Date__c
   Source Record	Source Field	Description__c	Description__c

To use the solution, use the Apex Data Loader to Import the data from the spreadsheet into the Journal Import Data object. Once you have done this, you can view the journal batches on the Journal Batch tab.

Tip:

You can also use this scenario in conjunction with Scenario B Multiple Targets from a Single Source with Roll-up Information to convert the Journal Batches to Journals.