Spreading Utility Bill Costs Between Departments

This case study uses Accounting and ClickLink to carry out a conversion on a utility bill. For information about creating ClickLink rules, see Using the Application. You can create custom fields with different names to those shown in this case study, however you must ensure that you change the name in all locations that reference it.

Problem

Your company receives an invoice and you want to work out the cost generated by each department based on the head count of your organization.

Solution

Create ClickLink rules to work out the cost from each department. To do this:

  1. Create a new custom setting with a number field for each department. For instance, you could call the custom setting Headcount by Dept and add fields such as G & A, Operations, Sales and Support.
  2. Edit the custom setting and enter the number of employees for each department field that you added in the previous step. The numbers you enter are used to generate the cost allocation for each department.
  3. Add a formula number custom field on the Payable Invoice object for each field that you added to the custom setting you created in step 1 with the following formula:

    Round(c2g__InvoiceTotal__c * ($setup.[YourCustomSetting]__c.[[YourCustomFieldThisField]__c / ($setup.[YourCustomSetting]__c.][YourCustomField]__c + $setup.[YourCustomSetting]__c.[YourCustomField]__c +$setup.[YourCustomSetting]__c.[YourCustomField]__c +$setup.[YourCustomSetting]__c.[YourCustomField]__c)),2)

    Replace [YourCustomFieldThisField] with the field for the department that you are creating the formula custom number field for. Replace .[YourCustomSetting] with the custom setting you created in step 1 and replace [YourCustomField] with the custom field that you created in step 2.

    For instance, if you are creating a formula number custom field for the GA department, the formula might look something like this:

    Round(c2g__InvoiceTotal__c * ($Setup.HeadcountByDept__c.GA__c/($Setup.HeadcountByDept__c.Sales__c+$Setup.HeadcountByDept__c.GA__c + $Setup.HeadcountByDept__c.Support__c +$Setup.Headcount.Operations__c )),2)

  4. In the Payable Invoice object, create a custom checkbox field called Allocated.
  5. In the Payable Invoice object, create the following custom fields with the text formula c2g__Account__r.c2g__CODADefaultExpenseAccount__r.Name:
    • Allocation Expense Support GLA
    • Allocation Expense Operations GLA
    • Allocation Expense Sales GLA
    • Allocation Expense G&A GLA
    • Allocation Expense GLA
  6. In the Payable Invoice object, create a formula (currency) field called Allocation_Reversing_Amount with this formula: c2g__InvoiceTotal__c * -1.
  7. Add a Departmental Allocation section to the Payable Invoice page layout with the fields that you set up in the previous steps.
  8. Create a new ClickLink rule to Allocate Departmental Expenses.
    1. Click the ClickLink Rules tab.
    2. Click New.
    3. Complete the fields described in Rule Fields
  9. Create these ClickLink mappings for the ClickLink rule that you created in the previous step:
    Recommended Mappings for the Allocate Departmental Expenses ClickLink Rule
    Target Record TypeTarget Record Created FromMapping TypeMapping LiteralSource FieldTarget Field
    DefaultSource RecordSource Field Namec2g__Reference__c
    DefaultSource RecordLiteralDepartmental Headcount Allocation c2g__JournalDescription__c
    DefaultSource RecordSource Field c2g__InvoiceDate__cc2g__JournalDate__c
    DefaultSource RecordSource Field NamePayable_Invoice__c

    To create a ClickLink mapping:

    1. Click New ClickLink Mapping.
    2. Complete the fields described in Mapping Fields.
    3. Click Save.
  10. Create this ClickLink rule to Map payable invoices to Allocation Journal Lines:
    Recommended Values for the Map Payable Invoice to Journal Lines ClickLink Rule
    ItemValue
    ClickLink Rule NameMap Payable invoice to Journal Lines
    Source Objectc2g__codaPurchaseInvoice__c
    Target Objectc2g__codaJournalLineItem__c
    Source Object Name FieldName
    Target Object Name FieldName
  11. Create ClickLink mappings for the ClickLink rule that you created in the previous step. For instance, if you are allocating the expense using the departments in step 1, you might include the following mappings:
    Recommended Mappings for the Map Payable Invoice to Journal Lines ClickLink Rule
    Target Record TypeTarget Record Created FromMapping TypeMapping LiteralSource FieldTarget Field
    DefaultSource RecordSource Field Allocation_Expense_GLA__cc2g__GeneralLedgerAccount__c
    DefaultSource RecordLiteralAllocation Rule - Reversing entry for unallocated expense c2g__LineDescription__c
    DefaultSource RecordSource Field Allocation_Reversing_Amount__cc2g__Value__c
    DefaultSource RecordLiteralGeneral Ledger Account c2g__LineType__c
    DefaultSource RecordSource Field Allocation_for_G_A__cc2g__Value__c
    G&ASource RecordSource Field Allocation_Expense_G_A_GLA__cc2g__GeneralLedgerAccount__c
    G&ASource RecordLiteralAllocation for G & A Dept c2g__LineDescription__c
    G&ASource RecordLiteralGeneral Ledger Account c2g__LineType__c
    G&ASource RecordLiteralG & A c2g__Dimension3__c
    OperationsSource RecordLiteralGeneral Ledger Account c2g__LineType__c
    OperationsSource RecordSource Field Allocation_for_Operations__cc2g__Value__c
    OperationsSource RecordSource Field Allocation_Expense_Operations_GLA__cc2g__GeneralLedgerAccount__c
    OperationsSource RecordLiteralAllocation for Operations Dept c2g__LineDescription__c
    OperationsSource RecordLiteralOperations c2g__Dimension3__c
    SalesSource RecordSource Field Allocation_Expense_Sales_GLA__cc2g__GeneralLedgerAccount__c
    SalesSource RecordSource Field Allocation_for_Sales__cc2g__Value__c
    SalesSource RecordLiteralAllocation for Sales Dept c2g__LineDescription__c
    SalesSource RecordLiteralGeneral Ledger Account c2g__LineType__c
    SalesSource RecordLiteralSales c2g__Dimension3__c
    SupportSource RecordSource Field Allocation_for_Support__cc2g__Value__c
    SupportSource RecordSource Field Allocation_Expense_Support_GLA__cc2g__GeneralLedgerAccount__c
    SupportSource RecordLiteralAllocation for Support Dept c2g__LineDescription__c
    SupportSource RecordLiteralGeneral Ledger Account c2g__LineType__c
    SupportSource RecordLiteralSupport c2g__Dimension3__c
  12. Create the following ClickLink Relationship:
    Recommended Relationship for the Map Payable Invoice to Allocation Journal Lines ClickLink Rule
    ClickLink Relationship NameRelationship ClickLink Rule Relationship Source Object Relationship Target Object Relationship Target Field
    Journal Lines Items to JournalMap Payable Invoice to Allocation Journal Linesc2g__codaPurchaseInvoice__cc2g__codaJournalLineItem__cc2g__Journal__c
  13. Create Visualforce pages to display the rule information on.
  14. Create ClickLink buttons to access the Visualforce pages you created in the previous step.
  15. Add Custom buttons in the Payable Invoice object for the Visualforce pages you created in the previous step.
  16. Modify Page layouts and list views to include the buttons created in the previous step.
  17. Add a Departmental Allocation section to the Payable Invoices page layout with the fields you created from step 1.

To test the ClickLink rule, open a payable invoice and select the custom button you added to the page layout.