MS CRM Field, Entity Reference.

This post is a collection of example sql squeries and other information on the entities underlying Microsoft's CRM. The version in particular is 2011, however much would be relevant for previous versions (e.e. V4).

Notes Associated with an Entity

The notes - entitiy name is Annotations have an ObjectID field which maps to the ID field of the 'parent' record, for an esample with Cases (which have a database entity of 'Incidents'), the join query would be:


select i.ticketnumber , i.description, note.notetext
from dbo.Incident  i
inner join dbo.Annotation note on i.IncidentId = note.ObjectId
where i.TicketNumber = 'JOB-24944-P8R3'
order by note.CreatedOn desc