Remove From My Forums

Fetch leads which are not created by SalesPerson Using QueryExpression

Question

Hi All,

Can anyone suggest me the way to filter out Leads which are not created by 'SalesPerson'?

Currently, I'm fetching the records which are Primary Leads and with the StatusCode=100000004 and i need to add one more condition.

Please see the below code done using QueryExpression:

QueryExpression qe = new QueryExpression();
            FilterExpression filter = new FilterExpression();

            qe.ColumnSet = new ColumnSet(true);
            qe.EntityName = "lead";

            filter.FilterOperator = LogicalOperator.And;
            filter.AddCondition("statuscode", ConditionOperator.Equal, 100000004);
            qe.PageInfo.Count = 100;
            qe.PageInfo.PageNumber = 1;

            LinkEntity linkPrimary = new LinkEntity();
            linkPrimary.JoinOperator = JoinOperator.Inner;
            linkPrimary.LinkFromAttributeName = "leadid";
            linkPrimary.LinkFromEntityName = "lead";
            linkPrimary.LinkToAttributeName = "new_primarylead";
            linkPrimary.LinkToEntityName = "lead";
            qe.LinkEntities.Add(linkPrimary);

            qe.Criteria = filter;

            RetrieveMultipleRequest request = new RetrieveMultipleRequest();
            request.Query = qe;
            RetrieveMultipleResponse response = (RetrieveMultipleResponse)orgProxy.Execute(request);

Any suggestion would appreciated!

Tuesday, October 30, 2012 8:48 AM

Answers

Hi,

first you need to retrieve all salespersons. Then you need to add a condition for each salesperson you retrieve. Here is the complete code for this:

QueryExpression salesPersonQuery = new QueryExpression();
            FilterExpression isDisabledFilter = new FilterExpression();

            salesPersonQuery.ColumnSet = new ColumnSet(true);
            salesPersonQuery.EntityName = "systemuser";

            isDisabledFilter.FilterOperator = LogicalOperator.And;
            isDisabledFilter.AddCondition("isdisabled", ConditionOperator.Equal, false);

            LinkEntity linkFilteredUsers = new LinkEntity();
            linkFilteredUsers.JoinOperator = JoinOperator.Inner;

            linkFilteredUsers.LinkFromEntityName = "systemuser";
            linkFilteredUsers.LinkFromAttributeName = "systemuserid";

            linkFilteredUsers.LinkToEntityName = "systemuserroles";
            linkFilteredUsers.LinkToAttributeName = "systemuserid";

            salesPersonQuery.LinkEntities.Insert(0, linkFilteredUsers);

            LinkEntity linkFilteredRoles = new LinkEntity();
            linkFilteredRoles.JoinOperator = JoinOperator.Inner;

            linkFilteredRoles.LinkFromEntityName = "systemuserroles";
            linkFilteredRoles.LinkFromAttributeName = "roleid";

            linkFilteredRoles.LinkToEntityName = "role";
            linkFilteredRoles.LinkToAttributeName = "roleid";
            var filterRole = new FilterExpression(LogicalOperator.And);
            filterRole.Conditions.Add(new ConditionExpression("name", ConditionOperator.Equal, "System Administrator"));
            linkFilteredRoles.LinkCriteria = filterRole;

            linkFilteredUsers.LinkEntities.Insert(0, linkFilteredRoles);


            salesPersonQuery.PageInfo.Count = 100;
            salesPersonQuery.PageInfo.PageNumber = 1;
            salesPersonQuery.Criteria = isDisabledFilter;

            RetrieveMultipleRequest retrieveSalesPersonsReq = new RetrieveMultipleRequest();
            retrieveSalesPersonsReq.Query = salesPersonQuery;
            RetrieveMultipleResponse retrieveSalesPersonsResponse = (RetrieveMultipleResponse)orgProxy.Execute(retrieveSalesPersonsReq);

            QueryExpression qe = new QueryExpression();
            FilterExpression filter = new FilterExpression();

            qe.ColumnSet = new ColumnSet(true);
            qe.EntityName = "lead";

            filter.FilterOperator = LogicalOperator.And;
            filter.AddCondition("statuscode", ConditionOperator.Equal, 100000004);

            if (retrieveSalesPersonsResponse.EntityCollection != null 
                && retrieveSalesPersonsResponse.EntityCollection.Entities != null
                && retrieveSalesPersonsResponse.EntityCollection.Entities.Count > 0)
            {
                foreach (var salesPerson in retrieveSalesPersonsResponse.EntityCollection.Entities)
                {
                    filter.AddCondition(new ConditionExpression("createdby", ConditionOperator.NotEqual, salesPerson.Id.ToString()));
                }
            }

            qe.PageInfo.Count = 100;
            qe.PageInfo.PageNumber = 1;

            LinkEntity linkPrimary = new LinkEntity();
            linkPrimary.JoinOperator = JoinOperator.Inner;
            linkPrimary.LinkFromAttributeName = "leadid";
            linkPrimary.LinkFromEntityName = "lead";
            linkPrimary.LinkToAttributeName = "new_primarylead";
            linkPrimary.LinkToEntityName = "lead";
            qe.LinkEntities.Add(linkPrimary);

            qe.Criteria = filter;

            RetrieveMultipleRequest request = new RetrieveMultipleRequest();
            request.Query = qe;
            RetrieveMultipleResponse response = (RetrieveMultipleResponse)orgProxy.Execute(request);

Greetings,

Pavlos

Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.

Edited by Pavlos Panagiotidis Tuesday, October 30, 2012 9:12 AM
Proposed as answer by Pavlos Panagiotidis Tuesday, October 30, 2012 9:12 AM
Marked as answer by DhavalPanchal247 Tuesday, October 30, 2012 12:22 PM

Tuesday, October 30, 2012 9:11 AM

All replies

1

Sign in to vote
Hi,

You can add one more condition like below

filter.AddCondition("createdby", ConditionOperator.NotEqual, GUID of the salesperson);
- Edited by _Vikram Tuesday, October 30, 2012 9:01 AM
Tuesday, October 30, 2012 9:00 AM

Hi,

first you need to retrieve all salespersons. Then you need to add a condition for each salesperson you retrieve. Here is the complete code for this:

QueryExpression salesPersonQuery = new QueryExpression();
            FilterExpression isDisabledFilter = new FilterExpression();

            salesPersonQuery.ColumnSet = new ColumnSet(true);
            salesPersonQuery.EntityName = "systemuser";

            isDisabledFilter.FilterOperator = LogicalOperator.And;
            isDisabledFilter.AddCondition("isdisabled", ConditionOperator.Equal, false);

            LinkEntity linkFilteredUsers = new LinkEntity();
            linkFilteredUsers.JoinOperator = JoinOperator.Inner;

            linkFilteredUsers.LinkFromEntityName = "systemuser";
            linkFilteredUsers.LinkFromAttributeName = "systemuserid";

            linkFilteredUsers.LinkToEntityName = "systemuserroles";
            linkFilteredUsers.LinkToAttributeName = "systemuserid";

            salesPersonQuery.LinkEntities.Insert(0, linkFilteredUsers);

            LinkEntity linkFilteredRoles = new LinkEntity();
            linkFilteredRoles.JoinOperator = JoinOperator.Inner;

            linkFilteredRoles.LinkFromEntityName = "systemuserroles";
            linkFilteredRoles.LinkFromAttributeName = "roleid";

            linkFilteredRoles.LinkToEntityName = "role";
            linkFilteredRoles.LinkToAttributeName = "roleid";
            var filterRole = new FilterExpression(LogicalOperator.And);
            filterRole.Conditions.Add(new ConditionExpression("name", ConditionOperator.Equal, "System Administrator"));
            linkFilteredRoles.LinkCriteria = filterRole;

            linkFilteredUsers.LinkEntities.Insert(0, linkFilteredRoles);


            salesPersonQuery.PageInfo.Count = 100;
            salesPersonQuery.PageInfo.PageNumber = 1;
            salesPersonQuery.Criteria = isDisabledFilter;

            RetrieveMultipleRequest retrieveSalesPersonsReq = new RetrieveMultipleRequest();
            retrieveSalesPersonsReq.Query = salesPersonQuery;
            RetrieveMultipleResponse retrieveSalesPersonsResponse = (RetrieveMultipleResponse)orgProxy.Execute(retrieveSalesPersonsReq);

            QueryExpression qe = new QueryExpression();
            FilterExpression filter = new FilterExpression();

            qe.ColumnSet = new ColumnSet(true);
            qe.EntityName = "lead";

            filter.FilterOperator = LogicalOperator.And;
            filter.AddCondition("statuscode", ConditionOperator.Equal, 100000004);

            if (retrieveSalesPersonsResponse.EntityCollection != null 
                && retrieveSalesPersonsResponse.EntityCollection.Entities != null
                && retrieveSalesPersonsResponse.EntityCollection.Entities.Count > 0)
            {
                foreach (var salesPerson in retrieveSalesPersonsResponse.EntityCollection.Entities)
                {
                    filter.AddCondition(new ConditionExpression("createdby", ConditionOperator.NotEqual, salesPerson.Id.ToString()));
                }
            }

            qe.PageInfo.Count = 100;
            qe.PageInfo.PageNumber = 1;

            LinkEntity linkPrimary = new LinkEntity();
            linkPrimary.JoinOperator = JoinOperator.Inner;
            linkPrimary.LinkFromAttributeName = "leadid";
            linkPrimary.LinkFromEntityName = "lead";
            linkPrimary.LinkToAttributeName = "new_primarylead";
            linkPrimary.LinkToEntityName = "lead";
            qe.LinkEntities.Add(linkPrimary);

            qe.Criteria = filter;

            RetrieveMultipleRequest request = new RetrieveMultipleRequest();
            request.Query = qe;
            RetrieveMultipleResponse response = (RetrieveMultipleResponse)orgProxy.Execute(request);

Greetings,

Pavlos

Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.

Edited by Pavlos Panagiotidis Tuesday, October 30, 2012 9:12 AM
Proposed as answer by Pavlos Panagiotidis Tuesday, October 30, 2012 9:12 AM
Marked as answer by DhavalPanchal247 Tuesday, October 30, 2012 12:22 PM

Tuesday, October 30, 2012 9:11 AM

Or you can also use this FetchXML to achieve this:

<fetch mapping="logical" count="50" version="1.0">
	<entity name="lead">
		<link-entity name="systemuser" from="systemuserid" to="createdby">
			<link-entity name="systemuserroles" from="systemuserid" to="systemuserid">
				<link-entity name="role" from="roleid" to="roleid">
					<filter>
						<condition attribute="name" operator="ne" value="Salesperson" />
					</filter>
				</link-entity>
			</link-entity>
		</link-entity>
	</entity>
</fetch>

Greetings,
Pavlos

Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.

Tuesday, October 30, 2012 9:17 AM

0

Sign in to vote
Hi Pavlos,

Thank you once again to spending your time!

Here, in this line we need to add 'SalesPerson' as you suggested in this forum ya? Please confirm.

filterRole.Conditions.Add(new ConditionExpression("name", ConditionOperator.Equal, "System Administrator"));

You mentioned 'System Administrator' this time.
Tuesday, October 30, 2012 9:21 AM
0

Sign in to vote

Yes, exactly. I forgot to change it.
Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.

Tuesday, October 30, 2012 9:25 AM
0

Sign in to vote

it is returning duplicate records, we missing any joins?

Tuesday, October 30, 2012 10:50 AM

Hi,

I don't really understand what you are trying to achieve with this code:

LinkEntity linkPrimary = new LinkEntity();
            linkPrimary.JoinOperator = JoinOperator.Inner;
            linkPrimary.LinkFromAttributeName = "leadid";
            linkPrimary.LinkFromEntityName = "lead";
            linkPrimary.LinkToAttributeName = "new_primarylead";
            linkPrimary.LinkToEntityName = "lead";
            qe.LinkEntities.Add(linkPrimary);

Can you please explain this?

Greetings,

Pavlos

Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.

Tuesday, October 30, 2012 11:00 AM

0

Sign in to vote
We are only considering primary leads. both column are available in "LeadBase" table.

1. LeadId

2. new_PrimaryLead

We need to fetch the records which are LeadID = new_PrimaryLead.
- Edited by DhavalPanchal247 Tuesday, October 30, 2012 11:16 AM
Tuesday, October 30, 2012 11:05 AM
0

Sign in to vote
Then maybe you are using the wrong attribute name?

Maybe you should be using this:

linkPrimary.LinkToAttributeName = "new_primaryleadid";

instead of this:

linkPrimary.LinkToAttributeName = "new_primarylead";

Greetings,

Pavlos
Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.
Tuesday, October 30, 2012 11:15 AM

Sorry, for the typo mistake, i have updated the thread!

Posting the entire code:

QueryExpression salesPersonQuery = new QueryExpression();
            FilterExpression isDisabledFilter = new FilterExpression();

            salesPersonQuery.ColumnSet = new ColumnSet(true);
            salesPersonQuery.EntityName = "systemuser";

            isDisabledFilter.FilterOperator = LogicalOperator.And;
            isDisabledFilter.AddCondition("isdisabled", ConditionOperator.Equal, false);

            LinkEntity linkFilteredUsers = new LinkEntity();
            linkFilteredUsers.JoinOperator = JoinOperator.Inner;

            linkFilteredUsers.LinkFromEntityName = "systemuser";
            linkFilteredUsers.LinkFromAttributeName = "systemuserid";

            linkFilteredUsers.LinkToEntityName = "systemuserroles";
            linkFilteredUsers.LinkToAttributeName = "systemuserid";

            salesPersonQuery.LinkEntities.Insert(0, linkFilteredUsers);

            LinkEntity linkFilteredRoles = new LinkEntity();
            linkFilteredRoles.JoinOperator = JoinOperator.Inner;

            linkFilteredRoles.LinkFromEntityName = "systemuserroles";
            linkFilteredRoles.LinkFromAttributeName = "roleid";

            linkFilteredRoles.LinkToEntityName = "role";
            linkFilteredRoles.LinkToAttributeName = "roleid";
            var filterRole = new FilterExpression(LogicalOperator.And);
            filterRole.Conditions.Add(new ConditionExpression("name", ConditionOperator.Equal, "Salesperson"));
            linkFilteredRoles.LinkCriteria = filterRole;

            linkFilteredUsers.LinkEntities.Insert(0, linkFilteredRoles);


            salesPersonQuery.PageInfo.Count = 100;
            salesPersonQuery.PageInfo.PageNumber = 1;
            salesPersonQuery.Criteria = isDisabledFilter;

            RetrieveMultipleRequest retrieveSalesPersonsReq = new RetrieveMultipleRequest();
            retrieveSalesPersonsReq.Query = salesPersonQuery;
            RetrieveMultipleResponse retrieveSalesPersonsResponse = (RetrieveMultipleResponse)orgProxy.Execute(retrieveSalesPersonsReq);

            QueryExpression qe = new QueryExpression();
            FilterExpression filter = new FilterExpression();

            qe.ColumnSet = new ColumnSet(true);
            qe.EntityName = "lead";

            filter.FilterOperator = LogicalOperator.And;
            filter.AddCondition("statuscode", ConditionOperator.Equal, 100000004);
            
            if (retrieveSalesPersonsResponse.EntityCollection != null
                && retrieveSalesPersonsResponse.EntityCollection.Entities != null
                && retrieveSalesPersonsResponse.EntityCollection.Entities.Count > 0)
            {
                foreach (var salesPerson in retrieveSalesPersonsResponse.EntityCollection.Entities)
                {
                    filter.AddCondition(new ConditionExpression("ownerid", ConditionOperator.NotEqual, salesPerson.Id.ToString()));
                }
            }

            qe.PageInfo.Count = 100;
            qe.PageInfo.PageNumber = 1;

            LinkEntity linkPrimary = new LinkEntity();
            linkPrimary.JoinOperator = JoinOperator.Inner;
            
            linkPrimary.LinkFromEntityName = "lead";
            linkPrimary.LinkFromAttributeName = "leadid";

            linkPrimary.LinkToEntityName = "lead";
            linkPrimary.LinkToAttributeName = "new_primarylead";

            qe.LinkEntities.Add(linkPrimary);

            qe.Criteria = filter;

            RetrieveMultipleRequest request = new RetrieveMultipleRequest();
            request.Query = qe;
            RetrieveMultipleResponse response = (RetrieveMultipleResponse)orgProxy.Execute(request);

Please suggest Pavlos!

Edited by DhavalPanchal247 Tuesday, October 30, 2012 11:19 AM

Tuesday, October 30, 2012 11:18 AM

Hi,

try changing it to this:

LinkEntity linkPrimary = new LinkEntity();
            linkPrimary.JoinOperator = JoinOperator.Inner;
            linkPrimary.LinkFromAttributeName = "new_primarylead";
            linkPrimary.LinkFromEntityName = "lead";
            linkPrimary.LinkToAttributeName = "leadid";
            linkPrimary.LinkToEntityName = "lead";
            qe.LinkEntities.Add(linkPrimary);

Greetings,

Pavlos

Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.

Tuesday, October 30, 2012 11:35 AM

0

Sign in to vote
Hi,

ignore my last post. Just add

qe.Distinct = true;

to your code. It should work.

Greetings,

Pavlos
Please mark this reply as an answer and vote it as helpful if it helps you find a resolution to your problem.
View my latest gallery contribution here.
Visit my blog here.
Tuesday, October 30, 2012 11:44 AM
0

Sign in to vote

Its working Pavlos!

Tuesday, October 30, 2012 12:21 PM

Answered by:

Fetch leads which are not created by SalesPerson Using QueryExpression

Question

Answers

All replies