C# CRM SDK Custom columns

Question

How do I build a CRM SDK QueryExpression where the values of two columns are combined to a new one? In MySQL, my query would look like that:

SELECT *, (`latest_maintenance`+`maintenance_interval`) as `next_maintenance` FROM `servers` ORDER BY `next_maintenance` DESC

In C#, however, I have only managed to do the following:

var retrieveRequest = new RetrieveMultipleRequest();

retrieveRequest.Query = new QueryExpression
                                    {
                                        EntityName = "server",
                                        ColumnSet = new ColumnSet(new[] {"latest_maintenance", "maintenance_interval"})
                                    };

var crmReponse = (RetrieveMultipleResponse) service.Execute(retrieveRequest);

How would I join "latest_maintenance" and "maintenance_interval" to "next_maintenace" in order to be able to use an OrderExpression?

EDIT: How would I make a simple string-based query for Microsoft Dynamics CRM? Seems like a way easier and more intelligible way than their default one.

score 2 · Accepted Answer · answered Jun 15 '12 at 13:45

2

If you are using the SDK for server-side code, then your choice is QueryExpression or FetchXml. Neither will do what you are asking, natively.

Remember that with QueryExpression you are returning typed objects, not just strings so if you are to concatenate two values, they need a container in which to be stored, i.e. an attribute.

Can you not just "order by" column1 then column2? I.e.

var myQueryExpression = new QueryExpression
                                {
                                    EntityName = "server",
                                    ColumnSet = new ColumnSet(new[]{
                                        "latest_maintenance", 
                                        "maintenance_interval"})
                                };
myQueryExpression.AddOrder("latest_maintenance", OrderType.Ascending);
myQueryExpression.AddOrder("maintenance_interval", OrderType.Ascending);

answered Jun 15 '12 at 13:45

Greg Owens

3,878
1
18
42

No, that would not sort items correctly where although the maintenance has been done not long ago, the maintenance interval is so low that they need to be maintained earlier than some of those that have been maintained a longer time ago. – arik Jun 15 '12 at 13:48
OK I understand now - your original question said you were "joining" the two fields (implied concatenation). You seem to be implying that you are adding a date interval to a date and you wish to sort by the newly calculated date. In this scenario your easiest supported solution is to create a new attribute called "next maintenance" on the "server" entity and calculate its value at runtime via a plugin whenever latest_maintenance or maintenance_interval changes. – Greg Owens Jun 15 '12 at 14:38
OK, how would I do that? I need next maintenance to be calculated automatically, I know how to add the field ;) – arik Jun 15 '12 at 14:38
Depends what form `maintenance_interval` takes. If it is an integer that represents, for example, a number of days then a simple post-update workflow could be used to add `maintenance_interval` days to `latest_maintenance` date to derive `next_maintenance`. In any other scenario you will need to write a plug-in, registered on create and update of the aforementioned fields which conducts the date-calculation and applies the new value to your `next_maintenance` attribute. – Greg Owens Jun 15 '12 at 14:43
Well, `next_maintenance` should be a `DateTime`-object that is calculated simply by adding the number of minutes given in `maintenance_interval` to `latest_maintenance`. – arik Jun 15 '12 at 14:47
1

Take a look at these extensions to the OOB workflow rules: (http://manipulationlibrary.codeplex.com/wikipage?title=Date%20Utilities&referringTitle=Home) It includes custom workflow assemblies for adding values to dates. This will allow you to increment a datetime value based on the value in another field. – Greg Owens Jun 15 '12 at 16:04
Please take a look at the fetch xml aggregate functions http://grandhah.blogspot.in/2017/06/crm-sdk-aggregate-fetch-xml.html – Sreerejith S S Aug 22 '17 at 04:56

C# CRM SDK Custom columns

1 Answers1