Perform a Mileage Expense Calculation Using EasyPlugins

We’ve recently deployed a few custom plugins based on the EasyPlugins tool for Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM 2015, available for free on CodePlex. In this blog we’d like to share some of the things we’ve learned, since the tool is great but not very well documented.

In this blog we’ll review how to calculate an expense mileage reimbursement amount. There are other ways to do this, but this way allows for business users to manage reimbursement rates and effective dates. I’m also making it it slightly complex because I want to demonstrate some of the things that aren’t covered in the official documentation.

In my example, I have:

• An entity called ics_expense that contains:
  • A date (ics_date) that, by business requirement, the user must be able to leave blank. If they do, the date will default to today.
  • The per-mile rate that was applied (ics_permilerate), a Currency value.
  • A distance value (ics_distance) as a Decimal value.
  • An Amount value (ics_amount), also a Currency, that represents the amount to be paid to the employee.
  • An Option Set (picklist) (ics_type)
    • 100000000 : Itemized Expense
    • 100000001 : Mileage
• An entity called ics_mileagereimbursementrate, which contains:
  • An effective start date (ics_effectivestartdate) as a Date
  • An effective end date (ics_effectiveenddate) as a Date
  • A reimbursement rate (ics_reimbursementrate) as Currency.

When the Expense is saved, this plugin should:

• Identify if the user filled in the date (ics_date), if not default it to today.
• Pull the mileage reimbursement rate that should be applied based on that date. (Design assumption is that there’s only one rate applicable for a given date.)
• Calculate the amount to reimburse.
• Fill in the total amount on the Expense but only if it’s of type Mileage.

Here’s the approach:

1. Stage the data (left side of the EasyPlugins screen)
   1. Pull down the user-input date, the type, and the distance and store them in variables local to EasyPlugins.
   2. Determine if I need to use the user’s date or default it to today.
   3. Pull down the appropriate rate.
   4.  Calculate the total reimbursement.
2. Update the Expense (right side of the EasyPlugins screen)
   1. Set the Per Mile and total expense fields on the Expense.
   2. Set the date on the Expense if the user left it blank.

Start by creating a new Plugin using EasyPlugins (easyplugins.codeplex.com) – install the package then access it under Settings -> EasyPlugins then selecting New -> Plugin. Give it a name and set it to run PostCreate, Post Update, and Synchronously.

First I need to stage up some data on the left side. First, I’m going to pull down the metadata from the entity. Add Attributes:

• ics_date – becomes #p1
• ics_distance – becomes #p2
• ics_type – becomes #p3

For these, we want the State to be “after action” since we want the value the user actually input.

Now, we determine whether to use the user’s date or ours. To do this, we check if the user input one and if so use that, otherwise make a new one. To do this we use C# syntax in a Calculated field:

[code lang=”Cpp”]
#p1 == null ?
DateTime.Today :
#p1
[/code]

Hit Test to make sure your expression is syntactically correct. This will place your new value in #p4.
You may find this syntax unfamiliar. In a more familiar format, it might look like:
[code lang=”js”]
if (p1 == null)
#p4 = new DateTime();
else
#p4 = #p1;
[/code]
Now, find the Expense reimbursement rate to use. We need to find the rate that applied as of the date of the Expense, which is now held in #p4. To do this, we need to find the rate with an effective start date in the past, an effective end date in the future. Construct an Advanced Find like this – the dates you use don’t matter at this point. Make sure the reimbursement rate is in the returned values – add it using Add Columns.

Download the FetchXML. A few edits you need to make:

1. Delete all attributes except ics_mileagereimbursementrate. That’s the number we’re really after.
2. In the date fields, replace the value of the date with your calculated date variable, #p4.

It’ll look like this when ready:
[code lang=”xml”]
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="ics_mileagereimbursementrate">
<attribute name="ics_reimbursementrate" />
<order attribute="ics_reimbursementrate" descending="false" />
<filter type="and">
<condition attribute="ics_effectivestartdate" operator="on-or-after" value="#p4" />
<condition attribute="ics_effectiveenddate" operator="on-or-before" value="#p4" />
</filter>
</entity>
</fetch>
[/code]

Remember, our assumption going in was that there’s only one reimbursement rate that applies for a given date. So this should only return one record.

Now drop it in a Request parameter. In this case the Aggregate Function is “First” since we are expecting only one record to come back and we want the value from the first returned record.

The results of this will be placed in an array called #p5.

The last thing to do is to use this rate to calculate the actual amount to reimburse. Enter a Calculated parameter and use this syntax:
[code lang=”c”]
Convert.ToDecimal(#p5[0]) * #p2
[/code]
We need to be careful here.

• #p2 is the mileage and is a Decimal value. So to make sure we’re calculating apples to apples, we need to convert the value returned from the query by using Convert.ToDecimal (C# syntax).
• #p5 is the results of our query. Since we didn’t use an aggregate function, it comes back as an array. Rather than using the variable #p5 straight-up, we need to force it to read the first record, thus the [0] syntax.

And that’s #p6, the actual amount to pay the employee.

Phew. A lot of work but our data is now staged and ready to go. To recap:

• #p1 is the date the user set on the entity.
• #p2 is the distance traveled as provided by the user.
• #p3 is the option set that indicates the type of Expense (mileage or otherwise).
• #p4 is the date the expense occurred – either provided by the user or defaulted to today.
• #p5 is the array of expense rates that encompass your effective date.
• #p6 is the total reimbursable amount (distance * rate).

First, we need to update the Expense with the mileage rate and the calculated value. On the right side, Add an Update record.

• Entity is ics_expense.
• Where ID = #id (Current ID) – we’re updating the current record, not anything related.

But how do we filter it so that this only happens if the type is Mileage on the Expense? We need to get it to read the Option Set (picklist) value. That’s stored in #p3. Option Sets are managed by EasyPlugins with a Code and a Label attribute. To get at them, you need to use:
[code lang=”c”]
#p3.Code
[/code]

But since we are looking for a Condition here, you need to force the Code value to a String and then check if it’s the value you’re after.
[code lang=”c”]
#p3.Code.ToString().Equals("100000001")
[/code]
Tricky, but that’s what worked for me. Then, just set the value of the amount and the rate.

Then, set:

• ics_amount = #p6
• ics_permilerate = #p5[0]

What’d we miss? We didn’t set the Date. We need to use a separate Action step because we only want to override it if the user didn’t fill it out, regardless of the type of Expense. (The last Action only applied to Mileage records.)

Last step: make another Update action.

• Entity = ics_expense
• Filter = #p1 == null (you can check if a variable is null with a simple == operator like this)
• Where ID = #id

And set the date field to #p4.
There you have it, folks.