I’m working on a project with a straightforward requirement that’s typically solved with out-of-the-box Power Apps platform features. The customer needs to capture employment application data as part of their online hiring process. In addition to entering standard employment data, applicants enter a typical month’s worth of budgeted expenses to paint a clear financial picture which serves as one factor in their suitability for employment.
The Requirements
The solution consists of an Application entity and a related Expense entity to track multiple expenses per Application. The Application entity needs to capture aggregate expense values based on expense categories. The Expense entity stores the institution or business receiving the payment, the Expense Type (lookup), the monthly payment in dollars, and the balance owed in dollars. Expense Types are associated with “Expense Categories.” An Expense Category is associated with one or more Expense Types. The Application entity tracks 15-20 aggregate expense values that need to be calculated from the individual expense entries.
For example, the expense types “Life Insurance” and “Auto Insurance” are both associated with the expense category “Insurance.” The Application entity has an aggregate field called “Total Insurance Expenses” to capture the sum of all monthly insurance-related expenses.
The basic entity model is shown below:
To summarize all that detail (which is probably hard to follow in paragraph form), the essential requirements are:
- Capture standard employment data for applicants
- Capture monthly budgeted expenses associated with an expense type and category
- Calculate 15-20 aggregate expenses based on expense category
- The solution must accommodate the addition of new expense categories
The aggregate fields on the Application entity fall into one of four categories: 1) a monthly payment total by expense category, 2) an outstanding balance total by expense category, 3) a monthly payment total across all categories, and 4) a total outstanding balance across all categories.
The breakdown for each of the expense categories and their associated aggregations on the Application entity can be depicted as such:
| Expense Category | Category Payment Rollup | Category Balance Rollup | All Payments Rollup | All Balances Rollup |
| Automobile | Total Automobile Expenses | Total Auto Balances Owed | Total Monthly Expenses | Total Outstanding Debt |
| Credit Card | Total Credit Card Expenses | Total Credit Card Balances Owed | Total Monthly Expenses | Total Outstanding Debt |
| Food/Clothing | Total Food/Clothing Expenses | Total Monthly Expenses | ||
| Housing | Total Housing Expenses | Total Housing Balances Owed | Total Monthly Expenses | Total Outstanding Debt |
| Insurance | Total Insurance Expenses | Total Monthly Expenses | ||
| Medical/Dental | Total Medical Expenses | Total Monthly Expenses | ||
| Other Debt | Total Other Debt Expenses | Total Other Debt Balances Owed | Total Monthly Expenses | Total Outstanding Debt |
| Utilities | Total Utility Expenses | Total Monthly Expenses |
The table shows that some expense categories require a total of four aggregate calculations, whereas others only require two aggregate calculations. The calculations should occur when an Expense is created, the values for a monthly payment or balance owed change, an Expense is deactivated/activated (state change), or when an Expense is deleted. “Total Monthly Expenses” is calculated for all expense entries. Only four categories require the category balance and total outstanding balance calculations.
Platform Limitations
Maximum Number of Rollup Fields
Dynamics 365 only allows for a maximum of 10 rollup fields on any given entity — these are fields that take advantage of the “Rollup” field type customized in the solution, the values for which are automatically calculated by the platform on a predetermined interval, e.g., every 12 hours.
One option to overcome this limitation — only available in on-premises Dynamics 365 implementations — is to modify the maximum number of rollup fields per entity in the MSCRM_CONFIG database. There are rare circumstances wherein modifying table values in this database are beneficial. However, given the possibility of a disaster recovery situation in which Dynamics 365 needs to be reinstalled and/or recovered, any modifications made to the MSCRM_CONFIG database could be lost. Even if an organization has well-documented disaster recovery plans that account for these modifications, there’s always a chance the documented procedures will not be followed, or steps possibly skipped.
Another consideration is the potential to move to the cloud. If the customer intends to move their Dynamics 365 application to the cloud, they’ll want to ensure their solution remains on a supported path, and eliminate the need to re-engineer the solution if that day comes.
Rollup Calculation Filters
Rollup fields in Dynamics 365 are indeed a powerful feature, but they do come with limitations that prevent their use in complex circumstances. Rollup fields only support simple filters when defining the rollup field aggregation criteria.
To keep this in the context of our requirements, note above that the Expense Type and Expense Category are lookup values in our solution. If we need to calculate the sum of all credit card expenses entered by an applicant, this is not possible given our current design, because the Expense Type is a lookup value on the expense entry. You’ll notice that when I try to use the Expense Type field in the filter criteria for the rollup field, I’m only given the choices “Does not contain data” and “Contains data.” Not only can’t I use actual values of the Expense Type, but I can’t drill down to the related Expense Category to include it in my aggregation filter.
Alternatives
The limitations above could be overcome by redesigning our solution, for example, by choosing to configure both the Expense Type and Expense Category fields as Option Sets instead of lookups, along with some sophisticated Business Rules that appropriately set the Expense Category based on the selected Expense Type. That’s one option worth considering, depending on the business requirements with which you’re dealing. We could also choose to develop a code-heavy solution by writing plugin code to do all these calculations, thus side-stepping the limitation on rollup fields and accommodating the entity model I’ve described.
The Solution
Ultimately, however, the customer wants a solution that allows them to update their expense tracking requirements without needing developers to get the job done. For example, the organization may decide they no longer want to track a certain expense category or may want to add a new one. Choosing to create entities to store the necessary lookup values will afford them that kind of flexibility. However, that still leaves us the challenge of calculating the aggregate expense values on the Application entity.
The final solution may still require the involvement of their IT department for some of the configuration steps but ideally will not require code modifications.
Lookup Configuration
The first step toward our solution is to add four additional fields to the Expense Category entity. These four fields represent the four aggregation categories described above: 1) Category Payment Rollup, 2) Category Balance Rollup, 3) Payments Rollup (for all categories), and 4) Balances Rollup (for all categories).
These fields will allow users to define how aggregate values are calculated for each Expense Category, i.e., by identifying the target fields on the Application entity for each aggregation.
Custom Workflow Activity
The next step is to write a custom workflow activity to perform the aggregate calculations described above. Custom workflow activities present several benefits to the customer, primarily centered on the ease of configuration within the Dynamics 365 UI itself (run asynchronously, run on-demand, and/or when specific events occur on the target record type, e.g., create, update, delete, or state change). Custom workflow activities can accept user-defined parameters configured in the workflow definition.
This means that — as you might have guessed — the custom workflow activity can be written in such a way to allow users to add new Expense Categories so that the aggregate calculations “just work” without requiring code modifications or changes to the workflow configuration in the solution.
Here’s the custom workflow activity class that runs the calculations followed by the workflow definition. As you can see below, I’ve included the Application and Expense Category fields as required input parameters for the workflow activity. (I’ll likely refactor this solution to accept the four fields as inputs, but for now, this gets the job done. Thanks to my good friend, Matt Noel, for that suggestion.) Further down, you’ll notice that for each aggregate field, we run a custom fetch query configured appropriately to perform the required calculation.
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Workflow;
using System;
using System.Activities;
namespace Project.Workflow.Activities
{
public class CalculateExpenses : CodeActivity
{
#region Input Parameters
[RequiredArgument]
[Input("Application")]
[ReferenceTarget("new_application")]
public InArgument<EntityReference> Application { get; set; }
[RequiredArgument]
[Input("Expense Category")]
[ReferenceTarget("new_expensecategory")]
public InArgument<EntityReference> ExpenseCategory { get; set; }
private readonly string _categoryPaymentRollupField = "new_categorypaymentrollup";
private readonly string _allPaymentsRollupField = "new_paymentsrollup";
private readonly string _categoryBalanceRollupField = "new_categorybalancerollup";
private readonly string _allBalancesRollupField = "new_balancesrollup";
#endregion
protected override void Execute(CodeActivityContext executionContext)
{
var tracer = executionContext.GetExtension<ITracingService>();
var context = executionContext.GetExtension<IWorkflowContext>();
var serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
var orgService = serviceFactory.CreateOrganizationService(null);
var orgContext = new OrganizationServiceContext(orgService);
string[] _rollupFields =
{
_categoryPaymentRollupField,
_allPaymentsRollupField,
_categoryBalanceRollupField,
_allBalancesRollupField
};
var expenseCategory = GetEntity(orgService, ExpenseCategory.Get(executionContext), _rollupFields);
var applicationRef = Application.Get(executionContext);
var application = new Entity("new_application")
{
Id = applicationRef.Id
};
// Set the Category Payment Rollup
if (expenseCategory.GetAttributeValue<string>(_categoryPaymentRollupField) != null)
{
var paymentRollup = expenseCategory.GetAttributeValue<string>(_categoryPaymentRollupField);
application[paymentRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, false, true));
}
// Set the rollup for all Monthly Payments
if (expenseCategory.GetAttributeValue<string>(_allPaymentsRollupField) != null)
{
var allPaymentsRollup = expenseCategory.GetAttributeValue<string>(_allPaymentsRollupField);
application[allPaymentsRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, false, false));
}
// Set the rollup for Category Balances
if (expenseCategory.GetAttributeValue<string>(_categoryBalanceRollupField) != null)
{
var categoryBalanceRollup = expenseCategory.GetAttributeValue<string>(_categoryBalanceRollupField);
application[categoryBalanceRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, true, true));
}
// Set the rollup for all Category Balances
if (expenseCategory.GetAttributeValue<string>(_allBalancesRollupField) != null)
{
var allBalancesRollup = expenseCategory.GetAttributeValue<string>(_allBalancesRollupField);
application[allBalancesRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, true, false));
}
// Execute the update on the Application rollup fields
try
{
//trace
orgService.Update(application);
}
catch (Exception e)
{
//trace
throw new InvalidPluginExecutionException("Error updating Application: " + e.Message);
}
}
private static Entity GetEntity(IOrganizationService service, EntityReference e, params String[] fields)
{
return service.Retrieve(e.LogicalName, e.Id, new ColumnSet(fields));
}
private decimal GetExpenseAggregate(IOrganizationService service, Guid applicationId, Guid expenseCategoryId, bool balanceRollup, bool includeCategory)
{
var sum = 0m;
var aggregateField = balanceRollup ? "new_balanceowed" : "new_monthlypayment";
var fetchXML = @"<fetch distinct='false' mapping='logical' aggregate='true' >" +
"<entity name='new_expense' >" +
"<attribute name='" + aggregateField + "' aggregate='sum' alias='sum' />" +
"<filter type='and' >" +
"<condition attribute='statecode' operator='eq' value='0' />" +
"<condition attribute='new_applicationid' operator='eq' value='" + applicationId + "' />" +
"</filter>";
var endFetch = "</entity></fetch>";
if (includeCategory)
{
var categoryFetch = @"<link-entity name='new_expensetype' from='new_expensetypeid' to='new_expensetypeid' link-type='inner' alias='ExpenseType' >" +
"<link-entity name='new_expensecategory' from='new_expensecategoryid' to='new_categoryid' link-type='inner' alias='ExpenseCategory' >" +
"<filter type='and' >" +
"<condition attribute='new_expensecategoryid' operator='eq' value='" + expenseCategoryId + "' />" +
"</filter>" +
"</link-entity>" +
"</link-entity>";
fetchXML += categoryFetch + endFetch;
}
else
{
fetchXML += endFetch;
}
FetchExpression fetch = new FetchExpression(fetchXML);
try
{
EntityCollection aggregate = service.RetrieveMultiple(fetch);
foreach (var c in aggregate.Entities)
{
if (((AliasedValue)c["sum"]).Value is Money)
{
sum = ((Money)((AliasedValue)c["sum"]).Value).Value;
//tracer.Trace("Sum of payments is: {0}", sum);
}
}
}
catch (Exception e)
{
//tracer.Trace(e.Message);
throw new InvalidPluginExecutionException("Error returning aggregate value for " + aggregateField + ": " + e.Message);
}
return sum;
}
}
}
Workflow definition:
Configuration of custom inputs:
Testing
After building and registering my workflow assembly, I created expense entries for all expense types ensuring that all expense categories were represented. The following images depict the successful aggregation of payments and balances:
Conclusion
Custom workflow activities are a powerful tool that balances the need for a highly maintainable solution after deployment, with complex requirements that need a coded solution. The design gives end-users the flexibility to adapt their data collection needs over time as their requirements change, and they can do so with little or no involvement from IT.
As I mentioned above, an alternative approach to this requirement could involve writing a plugin to perform the calculations. This approach would still require some entity-based configurations for flexibility but would suffer from limited end-user configuration needed if or when requirements change. I can also update the custom workflow activity to accept the four aggregate fields as optional arguments to the workflow. Doing so would enable users to run separate workflow processes for each expense type/category, giving them additional configuration control over these automated calculations.
