Views:

Scenario Overview

This business requirement needs us to provide a way to take a set of Invoices & associated line items and clone them on a monthly basis. The requirement was around a recurring billing scenario of services that the company provided. The scenario calls for us to setup in the CRM system template Invoices which can be cloned each month.

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • A formula is setup to execute whenever the boolean field 'Clone For Monthly Billing' gets updated on the Invoice entity
  • The formula checks that the 'Clone For Monthly Billing' field is set to true 
  • If True it will clone the Invoice & all of its line items
  • After the Clone it will perform an Update on the newly cloned Invoice to set the due date to 30 days into the future
  • Finally a North52 Schedule is created to run on a monthly basis to find all the Invoices where the 'Clone For Monthly Billing' is set to true & execute the formula

N52 Formula Manager Steps

  • Create a new formula of type 'Save - Perform Action'
  • Set the Event to 'Update'
  • Set the Source Entity to 'Invoice'
  • Set the Source Property to 'Clone For Monthly Billing'
  • Copy & paste the formula below into the formula description field & click save

 
 

N52 Scheduler Manager Steps

  • Create a new North52 schedule 
  • Set the Name to 'Monthly Invoice Cloning'
  • Set the Frequency to 'Monthly'
  • Set the Schedule Type to 'Entity Update'
  • Set the desired start DateTime
  • Use the normal Advanced Find to generate a Fetch-Xml query & paste it into the Query field 
  • Click Save
  • Click N52 Commands -> Start Schedule
  • You can check the status of your Scheduled job under Settings -> System Jobs

Formula

if([invoice.new_cloneformonthlybilling] = true, 

       UpdateRecord('invoice',
                     Clone('invoice', 
                            [invoice.invoiceid], 
                            'invoice_details' ), 
                     SetAttribute('duedate', AddDays(LocalDate(), 30))
                           )
, 'NoOp')

How the schedule & formula work together

You may be asking yourself how does the monthly schedule execute the formula, see steps below,

-When the monthly schedule fires it executes the fetch-xml query 
-For all the Invoices returned it loops over them
-Then for each Invoice it executes an Update query to set the 'Clone For Monthly Billing' field to itself
-This has the effect of triggering the formula
-If you need to trigger different formulas on the 1 schedule then just include the required fields in the Attribute list of the fetch-xml

Wizard - Clone

Please see below the wizard you can use to create the Clone() function call used in this formula. 

 
 

Wizard - AddDays

Please see below the wizard you can use to create the AddDays() function call used in this formula. 

You will need to search for the function "LocalDate" in the function tab or type it in.