Creating a Codeless Opportunity Payment Schedule in CRM

This blog is a republishing of the original I did for the Microsoft Dynamics CRM Team Blog which you can find here.

The new calculated fields provide a lot of opportunity for codeless configuration in Dynamics CRM. In this example I will walk through creating an automated payment schedule in CRM for an Opportunity using calculated fields and Dialogs.

Business Case

For larger sales, we sometimes stagger payments over a period of time. Perhaps this is a lease-purchase arrangement or maybe a lay-by payment system. While, in theory, aspects such as interest, deposits and final lump payments could also be incorporated, I will leave these as an exercise for the reader.

The Key Entities

For this example, I will work with two entities: Opportunities and a custom child entity called Payments. In terms of the fields I am using we have:

• Opportunity
  • Days Between Payments (Integer, How many days between each payment)
  • Payments Start Date (Date, When payments start)
  • Total Payments (Integer, The number of payments)
  • Estimated Revenue (Currency, The out of the box field to represent the total amount being paid back)
• Payment
  • Amount Remaining (Currency Calculated, After this payment, how much remains to be paid)
  • Days Between Payments (Same as the field of the same name on the Opportunity)
  • Due On (Date Calculated, When the payment is to be made)
  • Payment Amount (Currency Calculated, How much the payment is for)
  • Payment Number (Integer, The nth payment)
  • Payments Start Date (Same as the field of the same name on the Opportunity)
  • Total Amount (Same as the Estimated Revenue field on the Opportunity)
  • Total Payments (Same as the field of the same name on the Opportunity)

In an ideal world, I would not repeat the fields on both the Opportunity and the Payment record, keeping them on the Opportunity record. However, it is not possible to refer to parent fields in a calculated field so it is necessary to denormalize and keep the values in both places.

The Calculations

The formulae for the calculated fields are:

Amount Remaining

Due On

Payment Amount

In theory, with the values we set on the Opportunity and the Payment Number, all other numbers can be calculated.

Automating with Dialogs

In principle, we could leave the solution there and manually create Payment records from the Opportunity. Assuming we map the fields down from the Opportunity to the Payment entity, all we need do is create a new Payment record from the Opportunity, enter the Payment Number and the system will do the rest. However, for a schedule with 100 payments, this is a lot of work.

This is where Dialogs come to the rescue. With Dialogs we can automate the creation of all Payment records. To do this I will create two Dialogs:

• Payment Scheduler: This initializes the loop and creates the first payment record
• Payment Creator: This is a child Dialog that calls itself and creates all the other Payment records

What is interesting about this arrangement is that it appears to circumvent the infinite loop detection which can plague workflows when we try to call them to generate multiple records. I tried creating 100 payments and it while it took a few seconds of processing, it worked fine.

The Payment Scheduler looks like this:

Both Dialogs run from the Opportunity entity. In this one we confirm the four key constants, held on the Opportunity (Total Amount, Start Date, Days Between Payments and Total Payments) and write them back to the Opportunity with the Update step.

For some reason I could not get the Estimated Revenue to appear for the Prompt and Response default value, even though I could display it as part of the prompt. If this is a problem, you may need to create a separate Total Amount field.

We then create the first Payment.

Finally we call our looping Dialog to create the rest of the Payment records, passing it the total number of Payments.

The looping Dialog looks like this:

Firstly, it takes in the previous Payment’s number and if the last Payment was the final Payment i.e. Payment Number = 1, the Dialog ends. If this is not the case a new Payment record is created.

We then update this record to reduce the Payment Number by one.

The next step in our Dialog is a bit of a dirty trick. We want to loop the Dialog but we do not want a Dialog page to appear many times. Every Dialog requires a Page with an associated Prompt and Response so, to meet this criterion, we embed it in an If-Statement which will never succeed (in my case the associated Opportunity name must be blank, which will never be the case).

Once past this, the Dialog calls itself passing through the Payment’s Payment Number.

The Final Result

The end result is a set of Payments against the Opportunity, evenly dividing the Opportunity total across all Payment records. If we want to keep track of when payment are actually made, we could add additional fields like a receipt number field or a simple tick box.

To create the Payment records, the user simply fires up the Dialog from the Opportunity.

Then, after making sure the four fields have values, they click Next and the Dialog shows its Finished screen.

After clicking Finish, the Opportunity now has a full set of Payments against it.

Conclusions

While a simple application of the tools, this shows how combining Dynamics CRM functions like calculated fields and Dialogs can create very powerful and intuitive tools for the user. In the past creating a tool like this for the user required plugins and extensive development with limited capacity for maintenance by the CRM Administrator. With this approach the CRM Administrator gets complete control of the tool and its behavior.