Microsoft has a real gem with Dynamics CRM. It is both an application and a platform, highly customizable without writing a line of code, and once you throw in the code you can do most anything (depending on how unsupported you want to go)
Internally, CRM has a number of calculated fields. The customer lookup, for example is defined in the database as COALESCE(accountid, contactid). If you wanted to be really bold, I supposed you could create a column then modify it to be a function within the database, but that would be an extremely bad idea. The correct way is to use plugins and / or javascript.
Form JavaScript is fine when you are only considered with maintaining the value when a use opens the form, but what if they print? Or use the field in a view? Or another application uses the web services to retrieve the value?
You need a plugin, and that plugin has a lot of work to do.
I recently created a plugin that permits the creation of lookups that target multiple entities. After finally handling (or chosing not to handle) all the contingent scenarios I learned a few things.
1. Your plugin has to handle the Execute message, as this is the message used for FetchXml. You will have to parse the fetchxml, determine if your entity is in it, determine if your column is in it (and you have to recurse through the link-entities).
You can either register on the post stage and perform your own retrievals to get the data for the calculation, or you can sit on the pre and post stages and modify the fetchxml in the pre stage to include the extra columns needed for your calculated field. This is MUCH MUCH faster. You will need to determine what the ResultXml looks like based on how the fetch is designed.
2. Despite what the Microsoft Documentation suggests, you do not have to register the ReteieveMultiple message for all entities – just the entity in question.
(Unless you intend to handle your calculated field being used in a filter condition. This could get extremely complicated and it is better to just make the field non-searchable)
3. You should make sure your calculated field is configured as non-searchable. This prevents it being used as a filter condition from the UI (advanced find and the like)
4. The same performance recommendation holds for the Retrieve message – register pre and post, modify the ColumnSet to contain the fields needed for the calculation, then modify the result set.
5. CRM doesn’t care if extra columns show up in the result set.
6. If your field is editable (the calculation is two-way) you will need to handle the create and update, filtered for your calculated field. Add the changes to the underlying fields to the InputParameters Target propertybag.
A couple notes about CRM 2011:
I haven’t found explicit documentation of this fact, but Fetch requests no longer use the Execute message – they are instead RetrieveMultiple.
There are two reasons I have inferred for this:
1: It is now possible because the QueryExpression in CRM 2011 allows LinkEntities to have a ColumnSet – thus providing feature parity between Queries and Fetch.
2: Plugins generally now execute within the CRM transaction, so most likely the FetchXml is parsed into a QueryExpression before the plugin is fired.
This is good and bad – it reduces the number of messages you have to listen for, but it means you do have to hook the global RetrieveMultiple.
Since CRM 4.0 is pretty dated I’m guessing there isn’t too much demand for a multilookup akin to the Customer lookups – but I have one, and I have managed to retool the basic functionality to work in CRM 2011 as well. (Who knows, it may end up as a solution!)