Sunday, August 30, 2015

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.



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.


Venkatesh Ekkaluri said...

Hi, Ur post is very useful for me. I have a problem that is not related to this post. but i need some suggestions from you.

I have a workflow which sends a mail to the customer before 1 month of the warranty expiration date. It was working fine and i deactivated it because its not required now. But still the workflow is getting triggered and an e-mail going to the customer.

what is the reason. How can i stop it. I am using CRM 2011.

Can you please guide me how to solve this..
Thanks in advance.

Leon Tribe said...

My guess is the workflow triggered off of the creation of the record and is in a waiting state until one month before expiration. If you go to the workflow and look at the associated system jobs, you will see your waiting workflows. You will need to manually cancel them.

sherylm said...

Your post was very helpful. I applied it to create an Order Payment Schedule. The only challenge I have is every time an order is updated when the related fields (number of payments, payment start date, total amount) change, the new payment schedule appends to the existing. How did you address that?
Thanks in advance.

Leon Tribe said...

Hi Sherylm,

It sounds like you're running the dialog a second time and generating a new schedule. This is tricky as, in an ideal world, the creation of the new schedule would automatically delete the old one. I haven't worked out a way to mass update child records using a dialog or workflow.

You got me thinking and, short of doing an Advanced Find and manually deleting them, this might be a reasonable workaround. Add a tickbox to the order with the name "Delete Schedule". You then create a Bulk Deletion Job which looks for all Payment records where the parent Order has that box ticked. The fastest you can run Bulk Delete Jobs is once every seven day but, hopefully, that is sufficient for your needs. Otherwise, you may have to use a Windows Service.

Unknown said...

Hi Leon,

Thanks a lot for this. We were just discussing a way to do this and I found your post. I tried setting it all up but I think the payment number is not passing to the dialog. When I run the dialog I get over 500 payments scheduled that are all set as payment -1. Any idea what I missed?

sherylm said...

HI Leon,

Thanks for your suggestion. The issue is that I would need to depend on the user to select the tickbox every time a change is made. Also, the Bulk Delete Jobs would not work because ofthe seven day limitation. I am using your process without the dialog and it works very well. I just need to delete the payment schedule associated with the order whenever a change is made.

Leon Tribe said...

The problem we have is the only way to trigger a delete is with the bulk delete function. The only other option I can think of is to detach the payments from the payment so they 'disappear' and then clean them up later with the Bulk Delete.

However, running through and updating records is something I can't think how to do with just Dialogs and Workflows. We may need to throw some code at the problem I think.

Leon Tribe said...

Hi Brian,

It sounds like you may have forgotten to increment the counter or it is not passing it to the next iteration of the loop. Maybe activate the hidden page and populate a field on there with the key parameters to see what is happening in each iteration. Then when it is fixed, hide the page again.