Exporting Annotation (Note) Attachment

A requirement I had recently was to move data between CRM environments, some of this data was files loaded into CRM as an attachment to an annotation (note). I’ve included a snippet below which has been cut down to bare bones. Its basically a function that uses fetch xml to retrieve the annotations, decode the file data and save to disk. So each annotation record has three attributes which are important here;

  • filename – name of the file (note: different from the subject of the annotation)
  • documentbody – Base64 encoded string which represents the document – hence I decode it before writing to disk
  • mimetype – I don’t actually use it for anything in the snippet but its required for reimporting – its an internet mime type and describes the type of file, e.g. image/jpeg

If you are going to use this you will probably want to change the fetch to; get more records (count) and have some filtering, i used a link-entity, e.g. get attachments regarding contacts

public void ExportDocuments(IOrganizationService service, String filePath)
{
     String fetch = @"<fetch mapping='logical' count='100' version='1.0'>
          <entity name='annotation'>
               <attribute name='filename' />
               <attribute name='documentbody' />
               <attribute name='mimetype' />
          </entity>
     </fetch>";

     foreach (Entity e in service.RetrieveMultiple(new FetchExpression(fetch)))
     {
          if (!String.IsNullOrWhiteSpace(e.Attributes["documentbody"].ToString()))
          {
               byte[] data = Convert.FromBase64String(e.Attributes["documentbody"].ToString());

               File.WriteAllBytes(filePath + e.Attributes["filename"].ToString(), data);
          }
     }
}
Advertisements

Querying CRM Relationships

Recently I had to create a query that for a given primary entity would return a set of related target entities and support both 1-many and many-many relationships.

I knew how to do this in separate queries, so I could have some branching logic to see which type of query I needed to perform but that wasn’t a particularly elegant solution. I found the RelationshipQueryCollection, this allows you create a RetrieveRequest against your primary entity and then link in any related targets. All you have to do is specify the relationship name and it will support both 1-many and many-many relationships. If your are using self-referencing relationship be sure the populate relationship.PrimaryEntityRole.

IEnumerable<Guid> GetRelatedEntities(IOrganizationService service, string primaryEntity, Guid primaryEntityId, string relationshipName, string targetEntity)
{
//the related entity we are going to retrieve
QueryExpression query = new QueryExpression();
query.EntityName = targetEntity;
query.ColumnSet = new ColumnSet();

//the relationship that links the primary to the target
Relationship relationship = new Relationship(relationshipName);
relationship.PrimaryEntityRole = EntityRole.Referenced; //important if the relationship is self-referencing

//the query collection which forms the request
RelationshipQueryCollection relatedEntity = new RelationshipQueryCollection();
relatedEntity.Add(relationship, query);

//the request to get the primary entity with the related records
RetrieveRequest request = new RetrieveRequest();
request.RelatedEntitiesQuery = relatedEntity;
request.ColumnSet = new ColumnSet();
request.Target = new EntityReference(primaryEntity, primaryEntityId);

RetrieveResponse r = (RetrieveResponse)service.Execute(request);

//query the returned collection for the target entity ids
return r.Entity.RelatedEntities[relationship].Entities.Select(e => e.Id);
}

Edit based on comment from daryllabar:

To better explain this function here is a practical example.

  • There is a one to many relationship between contact and account, it is named “account_primary_contact”.
  • The foreign key on the account is named “primarycontactid”
  • There is one contact, this is the primary contact of three accounts (A, B & C).
  • There is another account which does not have an primary contact (D).

In this example we will retrieve all three related account ids using the above function.

//Create the contact
Entity contact = new Entity("contact");
contact["firstname"] = "Relationship";
contact["lastname"] = "Test";
Guid contactId = proxy.Create(contact);

//Create the accounts, linking them to contact
Entity accountA = new Entity("account");
accountA["name"] = "Relationship Test A";
accountA["primarycontactid"] = new EntityReference("contact", contactId);
Guid accountAId = proxy.Create(accountA);

Entity accountB = new Entity("account");
accountB["name"] = "Relationship Test B";
accountB["primarycontactid"] = new EntityReference("contact", contactId);
Guid accountBId = proxy.Create(accountB);

Entity accountC = new Entity("account");
accountC["name"] = "Relationship Test C";
accountC["primarycontactid"] = new EntityReference("contact", contactId);
Guid accountCId = proxy.Create(accountC);

Entity accountD = new Entity("account");
accountD["name"] = "Relationship Test D";
Guid accountDId = proxy.Create(accountD);

//Get the ids of related accounts by performing a query against the relationship with the contact id
List<Guid> accountIds = GetRelatedEntities(proxy, "contact", contactId, "account_primary_contact", "account").ToList();

//Check that we actually recieved the ids we wanted
Console.WriteLine("Account A: " + accountIds.Contains(accountAId));
Console.WriteLine("Account B: " + accountIds.Contains(accountBId));
Console.WriteLine("Account C: " + accountIds.Contains(accountCId));
Console.WriteLine("Account D: " + accountIds.Contains(accountDId));

Output:
Account A: True
Account B: True
Account C: True
Account D: False

So, we received the result we wanted, but in reality its not very often we just want the ids, often we will need some columns as well, and have to peform some filtering of the results. In which case this function doesn’t achieve this goal. So lets take the function as a template and break it out to add some additional filtering and selection of columns (this could eventually be wrapped up into some clever generic function but for now I’ll keep it simple).

So here, we are going to get the account names and filter out any account name ending in C, this means we should only get accounts A & B. Note: I’ve cut out the entity creation bit from this snippet but its the same as above (e.g. proxy.Create(contact), etc, etc).


//the related entity we are going to retrieve
QueryExpression query = new QueryExpression("account");
query.ColumnSet = new ColumnSet("name");
//we dont want the account ending in C
query.Criteria.AddCondition(new ConditionExpression("name", ConditionOperator.DoesNotEndWith, "c"));

//the relationship that links the primary to the target
Relationship relationship = new Relationship("account_primary_contact");

//the query collection which forms the request
RelationshipQueryCollection relatedEntity = new RelationshipQueryCollection();
relatedEntity.Add(relationship, query);

//the request to get the primary entity with the related records
RetrieveRequest request = new RetrieveRequest();
request.RelatedEntitiesQuery = relatedEntity;
request.ColumnSet = new ColumnSet("lastname");
request.Target = new EntityReference("contact", contactId);

RetrieveResponse r = (RetrieveResponse)proxy.Execute(request);

//write the results
Console.WriteLine("Primary entity: {0}, Name: {1}", r.Entity.LogicalName, r.Entity["lastname"].ToString());

//parse the related entities
foreach (Entity e in r.Entity.RelatedEntities[relationship].Entities)
{
Console.WriteLine("Related entity: {0}, Name: {1}", e.LogicalName, e["name"]);
}

Output:

Primary entity: contact, Name: Test
Related entity: account, Name: Relationship Test A
Related entity: account, Name: Relationship Test B

Page-less CRM Dialogs

Normally CRM won’t allow you to create a dialog without pages. This is probably because the value of a user input form without any actual way to input something is pretty limited.

However there are circumstances where a page-less dialog could be useful:

  1. The dialog contains some common logic used by other dialogs (as a linked dialog), these actions require no user input, so it doesn’t make sense to show the user a blank page when this logic is performed.
  2. As  a link, e.g: The end user has a phone call open, they are going to have conversation with a contact, update the contact record and close the phone call.
  • They will update the contact record by running through a dialog.
  • It therefore makes sense to have that dialog on the contact record.
  • It would be better for the user to just run the dialog from the phone call and not open the contact record.
  • A page-less dialog on the phone call as a header could be used to link to the phone call, without making the user click through an empty page.

If we take the second case as an example normally CRM will prevent a page-less dialog as shown below.

Continue reading

Want to edit the CRM 2011 Ribbon?

If you have ever had to edit the CRM 2011 Ribbon by hand you have probably come close to considering a change in career. Fortunately your worries are over; Scott Durrow at Develop1 has created a really impressive editor – Ribbon Workbench.

I cannot recommend it highly enough, it has a wide range of features, can handle complex buttons such as flyouts, allows easy editing and layout, binds to web resources and basically removes any need for hand editing. He has also published a set of blog posts and articles to describe its usage.

Get it here.