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
    FieldData Type
    AmountNumber(16,2)
    DateDate
    DescriptionText(50)
    GLACodeText(100)
    Journal BatchMaster Detail relationship with Journal Batch.
    ProcessedCheckbox

    The Journal Batch object should contain these fields:

    Expected Journal Batch Object Fields
    FieldData Type
    AmountRoll-Up Summary (SUM Journal Import Data)
    DateDate
    DescriptionText(50)
    Journal Import DataLookup to Journal Import Data object
    Negative AmountFormula (Number)
    Number of ChildrenRoll-Up Summary (COUNT Journal Import Data)
    ProcessedCheckbox
    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
    ItemValue
    ClickLink Rule NameCreate Journal Batch
    Source ObjectJournalImportData__c
    Source Object Name FieldName
    Target ObjectJournalBatch__c
    Target Object Name FieldName
    Target Relation with SourceOne to Many
    Processed Field on TargetProcessed__c
    Relation Threshold20
    Relation Count Field on TargetNumberofChildren__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 OnMapping TypeSource FieldTarget Field
    Source RecordSource Field JournalBatch__cId
    Source RecordSource FieldDate__cDate__c
    Source RecordSource FieldDescription__cDescription__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.