Help with Query to return Contacts where Parent Account is certain status.

Friday, December 28, 2012 7:54 PM

I have a custom plugin which updates contact records. What I want to happen, is when a contact is updated, the query will find all other contacts that have the same email address AND the Parent Account of the contact is in a certain status. I was able to get it to work so it finds all contacts with the same email address but when I include the logic for the Parent Account, it doesn't work the way it is supposed to.

Can anyone take a look at the code below and see if I'm doing something wrong?

QueryExpression newQuery = new QueryExpression("contact");
                            newQuery.ColumnSet = new ColumnSet("new_leadscore", "new_leadsource");
                            newQuery.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, email);
                            //Up to here works and updates all contacts fine.


                            LinkEntity link = new LinkEntity
                            {
                                LinkFromEntityName = "contact",
                                LinkToEntityName = "account",
                                LinkFromAttributeName = "parentcustomerid",
                                LinkToAttributeName = "accountid"
                            };


                            link.LinkCriteria.AddCondition("new_leadtypename", ConditionOperator.Equal, "Prospect"); //Prospect
                            link.LinkCriteria.AddCondition("new_leadtypename", ConditionOperator.Equal, "Lead"); //Lead
                            link.LinkCriteria.Filters.Add(new FilterExpression { FilterOperator = LogicalOperator.Or });

                            newQuery.LinkEntities.Add(link);
                            
                           
                            

                            
                            EntityCollection retrieved = service.RetrieveMultiple(newQuery);

Edited by DSRP Friday, December 28, 2012 8:10 PM

All Replies

Saturday, December 29, 2012 8:46 AM

0
Hi,

Try to execute following FetchXML-

If new_leadtypename is OptionSet type then use following FetchXML.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="contact">
<attribute name="fullname" />
<attribute name="new_leadsource" />
<attribute name="contactid" />
<order attribute="fullname" descending="false" />
<filter type="and">
    <condition attribute="emailaddress1" operator="eq" value= 'Email Address' />
<condition attribute="parentcustomerid" operator="eq" value= 'GUID of the parent account' />
    <condition attribute="new_leadtypename" operator="in">
<value>Prospect</value>
<value>Lead</value>
</condition>
    </filter>
</entity>
</fetch>

If new_leadtypename is text type then use following FetchXML.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="contact">
<attribute name="fullname" />
<attribute name="new_leadsource" />
<attribute name="contactid" />
<order attribute="fullname" descending="false" />
<filter type="and">
<filter type="or">
<filter type="and">
<condition attribute="emailaddress1" operator="eq" value= 'Email Address' />
<condition attribute="parentcustomerid" operator="eq" value= 'GUID of the parent account' />
</filter>
<condition attribute="new_leadtypename" operator="eq" value="Prospect"/>
<condition attribute="new_leadtypename" operator="eq" value="Lead"/>
</filter>
</filter>
</entity>
</fetch>

Please check the following link, how to execute FetchXML.

http://msdn.microsoft.com/en-us/library/gg328117.aspx
Hope this helps. If you get answer of your question, please mark the response as an answer and vote as helpful !
Vikram !
- Marked As Answer by DSRP Monday, December 31, 2012 8:39 PM
- Reply
- Quote

Monday, December 31, 2012 7:54 PM

So I'm trying to execute FetchXML and convert it to a query. Here is my code now but I am getting an error at ServiceContext.SaveChanges(); Complete error is below the code. Any ideas what might be wrong?

FetchXmlToQueryExpressionRequest req = new FetchXmlToQueryExpressionRequest();
                            req.FetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
                                "<entity name='contact'>" +
                                "<attribute name='fullname' />" +
                                "<attribute name='accountrolecode' />" +
                            "<attribute name='emailaddress1' />" +
                            "<attribute name='parentcustomerid' />" +
                            "<attribute name='contactid' />" +
                            "<order attribute='fullname' descending='false' />" +
                            "<filter type='and'>" +
                            "<condition attribute='emailaddress1' operator='eq' value='test@test.com' />" +
                            "</filter>" +
                            "<link-entity name='account' from='accountid' to='parentcustomerid' alias='aa'>" +
                            "<filter type='and'>" +
                            "<condition attribute='new_leadtype' operator='in'>" +
                            "<value>100000002</value>" +
                            "<value>100000001</value>" +
                            "</condition>" +
                            "</filter>" +
                            "</link-entity>" +
                            "</entity>" +
                            "</fetch>";

                       

                            FetchXmlToQueryExpressionResponse resp = (FetchXmlToQueryExpressionResponse)service.Execute(req);

                            QueryExpression newQuery = resp.Query;


                            //  Query passed to the service proxy
                            EntityCollection retrieved = service.RetrieveMultiple(newQuery); //queryByExpression


                            //  Iterate through returned collection
                            foreach (var c in retrieved.Entities)
                            {

                                Entity e = (Entity)c;
                                if (newLeadScore != oldLeadScore)
                                {
                                    e["new_leadscore"] = entity.GetAttributeValue<string>("new_leadscore");
                                }
                                if (newLeadSource != oldLeadSource)
                                {
                                    e["new_leadsource"] = entity.GetAttributeValue<string>("new_leadsource");
                                }


                                ServiceContext.Attach(e);
                                //Update contact record
                                ServiceContext.UpdateObject(e);
                                //Save contact record
                                ServiceContext.SaveChanges();
                            }

Error:

Unhandled Exception: Microsoft.Xrm.Sdk.InvalidPluginExecutionException: An error occured while processing this request.
at Microsoft.Crm.Asynchronous.EventOperation.CreateAsyncResultFromException(AsyncExecutionContext context, Exception e)
at Microsoft.Crm.Asynchronous.EventOperation.InvokePlugin(AsyncExecutionContext context, IPlugin pluginInstance)
at Microsoft.Crm.Asynchronous.EventOperation.InternalExecute(AsyncEvent asyncEvent)
at Microsoft.Crm.Asynchronous.AsyncOperationCommand.Execute(AsyncEvent asyncEvent)
at Microsoft.Crm.Asynchronous.QueueManager.PoolHandler.ProcessAsyncEvent(AsyncEvent asyncEvent)
Inner Exception: Microsoft.Xrm.Sdk.SaveChangesException: An error occured while processing this request.
at Microsoft.Xrm.Sdk.Client.OrganizationServiceContext.SaveChanges(SaveChangesOptions options)
at ASR_Plugin.ContactsUpdate.PostContactUpdate.ExecutePostContactUpdate(LocalPluginContext localContext)
at ASR_Plugin.ContactsUpdate.Plugin.Execute(IServiceProvider serviceProvider)
at Microsoft.Crm.Asynchronous.V5ProxyPlugin.Execute(IServiceProvider serviceProvider)
at Microsoft.Crm.Asynchronous.EventOperation.InvokePlugin(AsyncExecutionContext context, IPlugin pluginInstance)
Inner Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: System.InvalidOperationException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #4869193DDetail:
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
<ErrorCode>-2147220970</ErrorCode>
<ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
<Message>System.InvalidOperationException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #4869193D</Message>
<Timestamp>2012-12-31T19:47:22.5768472Z</Timestamp>
<InnerFault i:nil="true" />
<TraceText i:nil="true" />
</OrganizationServiceFault>
at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
at Microsoft.Crm.Extensibility.InprocessServiceProxy.ExecuteCore(OrganizationRequest request)
at Microsoft.Xrm.Sdk.Client.OrganizationServiceContext.Execute(OrganizationRequest request)
at Microsoft.Xrm.Sdk.Client.OrganizationServiceContext.SaveChange(OrganizationRequest request, IList`1 results)