Using the FetchXML Library

Previously on Dr. Lambda's blog:

The most common way for users to query a Microsoft Dynamics CRM system is using the Advanced Search functionality. Advanced Search provides an interface where we can choose which attributes to display and set up conditions on which records to retrive. Behind the scenes this is represented in as XML more specifically FetchXML. FetchXML is the way we are going to query the system. XML is not the nicest interface for humans, thus we abstract away the syntax and start introducing types.

module FetchXml =
  type condition
  type 'a fetchxml
  let init : 'a Crm.Entities.entity

  let set_count : int -> 'a fetchxml -> 'a fetchxml

  let add_condition : 'a -> condition -> 'a fetchxml -> 'a fetchxml

  let add_attribute : 'a -> 'a fetchxml -> 'a fetchxml

  let generate : 'a fetchxml -> string

Now, the continuation...

Off the Shelf Usage

This library is itself mostly a utility for other tool we want to implement. However, let's look at some examples.

Example 1

In our first example we want to retrieve a specific users guid. This is not possible using the online interface.

FetchXml.init (Crm.Entities.systemuser)
|> FetchXml.set_count 1
|> FetchXml.add_condition Crm.Attributes.systemuser.Fullname (FetchXml.Equals "CRM System")
|> FetchXml.generate
|> fun f -> xrm.OrganizationService.RetrieveMultiple (FetchExpression f)
|> fun ec -> (ec.Entities.Item 0).Id

Returns:

1594eb93-de4c-4f11-bcf8-c27446d96378

Example 2

Let's do a slightly more complicated example, a generalized version of the built-in "My Open Opportunities". It is generalized because we can supply it with any users guid, and it retrieves all records, not limited by the usual maximum of 250.

FetchXml.init (Crm.Entities.opportunity)
|> FetchXml.add_condition Crm.Attributes.opportunity.Ownerid (FetchXml.Equals "{1594eb93-de4c-4f11-bcf8-c27446d96378}")
|> FetchXml.add_condition Crm.Attributes.opportunity.Statecode (FetchXml.Equals "0")
|> FetchXml.generate
|> fun f -> xrm.OrganizationService.RetrieveMultiple (FetchExpression f)
|> fun ec -> ec.Entities

Going Beyond

In the examples above, we notice that the two final steps are quite similar. Further, the steps exhibit the |> fun x ->-pattern which I don't find very attractive. Thus, we can abstract these steps into a function.

let fetch fxml = 
  xrm.OrganizationService.RetrieveMultiple (FetchExpression fxml)
  |> fun ec -> ec.Entities

Another useful addition to the library is a lookup function, which finds a unique element, based on one fields value.

let lookup ent f v =
  let es =
    FetchXml.init ent
    |> FetchXml.set_count 2
    |> FetchXml.add_condition f (FetchXml.Equals v)
    |> FetchXml.generate
    |> fetch in
  if es.Count = 0 then
    failwithf "Lookup failed: no %A records found with %A = %s" ent f v
  else if es.Count > 1 then
    failwithf "Lookup failed: multiple %A records found with %A = %s" ent f v
  else
    (es.Item 0).Id

Example: Audit History

I was recently in a situation where I needed to retrieve a lot of audit data. Currently, there is no convinient way to do this in CRM. Thus, it is a perfect way to demonstrate the usefulness of our new library. We are going to find the owner – before last update – of all open opportunities currently owned by a specific user.

FetchXml.init (Crm.Entities.opportunity)
|> FetchXml.add_condition Crm.Attributes.opportunity.Ownerid (FetchXml.Equals "{1594eb93-de4c-4f11-bcf8-c27446d96378}")
|> FetchXml.add_condition Crm.Attributes.opportunity.Statecode (FetchXml.Equals "0")
|> FetchXml.generate
|> fetch
|> Seq.map (fun o ->
  let req = RetrieveRecordChangeHistoryRequest () in
  req.Target <- EntityReference("opportunity", o.Id);
  let resp = xrm.OrganizationService.Execute(req) :?> RetrieveRecordChangeHistoryResponse in
  let oldVal = (resp.AuditDetailCollection.Item 0 :?> AttributeAuditDetail).OldValue in
  if oldVal <> null && oldVal.Attributes.ContainsKey("ownerid")
  then
    string (o.Attributes.["name"]) + "; " +
    (oldVal.Attributes.["ownerid"] :?> EntityReference).Name
  else
    "")