Sunday, March 16, 2014

A "What If" experiment... Evaluate Excel formulas in Dynamics CRM Plug-ins

What if you could combine an Excel engine (one that can evaluate formulas) with Dynamics CRM in a plug-in...

Well, it turns out that you can.

I've only worked on this for a couple of hours this morning (the family is awake now) but here's what I've been able to do so far...
  1. Download and install SmartXLS, a .NET Excel-compatible library.
  2. Create a simple plug-in that calls the SmartXLS library to generate a random number (using the RAND formula function; SmartXLS supports most Excel functions). The plug-in simply updates the accountnumber field for a new Account with the random value.
  3. Use ILMerge to merge SmartXLS's XL.DLL with my plug-in assembly. This allows you to register the plug-in and the SmartXLS functionality in a single assembly so there's no need to GAC anything... and this should allow it to work with CRM Online.
  4. Register a plug-in step for Create message for the account entity, pre-operation, synchronous.
  5. Create an account record. Voila! We have a random account number. (Not terribly useful, but you get the idea.)
What's exciting about this is that it's possible to tap into Excel (well, most of it) to execute hundreds of formulas and functions. If the plug-in could simply replace values in a pre-defined workbook, then theoretically a single plug-in could process formulas for any CRM entity.

Here's the part of the plug-in code that creates a worksheet, sets a formula, gets the random number and sets the accountnumber field for the new Account in CRM...

WorkBook wkbook = new WorkBook();
wkbook.NumSheets = 1;
wkbook.Sheet = 0;
wkbook.setFormula(0, 0, "RAND()");
wkbook.recalc();
randValue = wkbook.getNumber(0, 0);
entity["accountnumber"] = randValue.ToString();


Here's the plug-in registration details:



And our Account record with the randomly generated account number:



Where to take this next...?  Here are some ideas:
  • Since SmartXLS can read in XLS files, it's possible to pre-defined formulas in Excel and somehow feed the XLS into the plug-in (perhaps as an attachment to a custom entity). The plug-in will replace value in the worksheet, recalc, and update CRM with the results.
  • If a formula needs data from other related entities... that's trickier but certainly possible. A FetchXml statement on a worksheet could define the data to get from CRM. Once the data is retrieved, a formula can get whatever value is needed from the resulting data (e.g., min, max, average, etc.).
  • A formula updates values brought in from a FetchXml query. The plug-in updates the CRM records with the resulting values. Think bulk-update with the power of Excel formulas.
Of course, I know that there are off the shelf solutions like N52 Formula Manager, but wouldn't it be more fun to build the same functionality yourself?  (I didn't say it would be cheaper or as robust, at least not at first, but definitely more fun.)

Well, that concludes my What If experiment this morning. I hope it inspires you to harness the Dynamics CRM platform to do some creative things to solve business needs... or just pass some time while the family sleeps.

Cheers,

-Tim

1 comment:

  1. Perhaps the best use of this is to provide Excel-based formulas for extending the capabilities of workflows. For example, if you run into a limitation when building a workflow (i.e., date or financial calculation, complex if/then/else logic) then the workflow can call the Excel-based workflow activity (plug-in) to perform the calculation and return the result. This would allow someone familiar with Excel to construct the formula and "publish" the Excel file to CRM (in a custom entity) for use in workflows. Again, this is theoretical at this point, but certainly technical possible. If only there were 36 hours in a day...

    ReplyDelete