views:

117

answers:

1

I'm trying to build a Dynamics CRM 4 query so that I can get calendar events that are named either "Event A" or "Event B".

A QueryByAttribute doesn't seem to do the job as I cannot specify a condition where the field called "event_name" = "Event A" of "event_name" = "Event B".

When using the QueryExpression, I've found the FilterExpression applies to the Referencing Entity. I don't know if the FilterExpression can be used on the Referenced Entity at all. The example below is something like what I want to achieve, though this would return an empty result set as it will go looking in the entity called "my_event_response" for a "name" attribute. It's starting to look like I will need to run several queries to get this but this is less efficient than if I can submit it all at once.

ColumnSet columns = new ColumnSet();
columns.Attributes = new string[]{ "event_name", "eventid", "startdate", "city" };

ConditionExpression eventname1 = new ConditionExpression();
eventname1.AttributeName = "event_name";
eventname1.Operator = ConditionOperator.Equal;
eventname1.Values = new string[] { "Event A" };

ConditionExpression eventname2 = new ConditionExpression();
eventname2.AttributeName = "event_name";
eventname2.Operator = ConditionOperator.Equal;
eventname2.Values = new string[] { "Event B" };

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.Or;
filter.Conditions = new ConditionExpression[] { eventname1, eventname2 };

LinkEntity link = new LinkEntity();

link.LinkCriteria = filter;

link.LinkFromEntityName = "my_event";
link.LinkFromAttributeName = "eventid";

link.LinkToEntityName = "my_event_response";
link.LinkToAttributeName = "eventid";

QueryExpression query = new QueryExpression();
query.ColumnSet = columns;
query.EntityName = EntityName.mbs_event.ToString();
query.LinkEntities = new LinkEntity[] { link };

RetrieveMultipleRequest request = new RetrieveMultipleRequest();
request.Query = query;

return (RetrieveMultipleResponse)crmService.Execute(request);

I'd appreciate some advice on how to get the data I need.

+1  A: 

The QueryExpression object has a Criteria property that you can set. If you're looking for "my_event" records that have name A or name B, just set it up like this:

ColumnSet columns = new ColumnSet();
columns.Attributes = new string[]{ "event_name", "eventid", "startdate", "city" };

ConditionExpression eventname1 = new ConditionExpression();
eventname1.AttributeName = "event_name";
eventname1.Operator = ConditionOperator.Equal;
eventname1.Values = new string[] { "Event A" };

ConditionExpression eventname2 = new ConditionExpression();
eventname2.AttributeName = "event_name";
eventname2.Operator = ConditionOperator.Equal;
eventname2.Values = new string[] { "Event B" };

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.Or;
filter.Conditions = new ConditionExpression[] { eventname1, eventname2 };

QueryExpression query = new QueryExpression();
query.ColumnSet = columns;
query.EntityName = EntityName.mbs_event.ToString();
query.Criteria = filter;

RetrieveMultipleRequest request = new RetrieveMultipleRequest();
request.Query = query;

return (RetrieveMultipleResponse)crmService.Execute(request);

If you're only looking for events that have responses, keep the LinkEntity part in, but move the FilterExpression over to the QueryExpression object like I have above.

Matt
Thanks Matt. I completely missed the Queryexpression.Criteria property. Works a treat.
Mike