Help with a rollup query

(0) Share

Report

Posted on by Nosilla17

Hello - I'm hoping to get some assistance with a roll up query please. Obviously, a contact record can have multiple opportunities but I need to get a list of contact records which only have linked lost or open opportunities therefore if there is a linked won opportunity, I don't want to see the contact record. Is there a way I can exclude these type of contact records? Below is the logic I am currently using in the roll up query entity which isn't working as hoped as it is still returning contact records which have both a lost and won opportunity.

Entity: Lead

Opportunity(Contact) Contains Data

Status = Open / Lost

Many thanks,

I have the same question (0)

All responses (4)

Answers (1)

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Nosilla17,

Does the ‘rollup query’ you mentioned above refer to the one shown on the screenshot below?

Also, you mentioned that you want to list contacts records, why you use lead entity in the query?which one entity is your need?

Anyway, now suppose you want to list all contact records that opportunity status is Open or Lost.

Contact and Opportunity entity is 1:N relationship, but it doesn’t means that they just have only one relationship, if so, you need add all related opportunities as filter in the query.

1.Go Settings > Customizations > Customize the system> Contact > 1:N relationships to check relationship between contact and opportunity .

2.Add filter in the rollup query.

From the previous step, there are three 1:N relationships between Contact and Opportunity, so you need add these three all in the query, just like following screenshot:

Not set Status = Open / Lost, because opportunity status just have three value(Open/Lost/Won), set ‘Status does not equal Won’ directly can be more concise.

Now, you can check that it still returning contact records which have a won opportunity or not.

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
Nosilla17 5 on at

Like (0)

Report
Copy link

Link copied!

Hello, Thank you for your reply.

Apologies, I was referring to contact not lead.

For the contact, I have 3 x 1:N relationships - I have added these 3 to my query and set the filter to Status does not equal WON but it's still returning a contact record which has 2 opportunities - 1 lost and 1 won. In this scenario, I'm trying to filter out any contact out which has a won opportunity on, regardless of if it has a lost etc. So I only want to see contacts which don't contain any won opportunities

Thank you

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Nosilla17,

As long as the contact that has a child with won status, it should not be returned, which is what you need, right?

In fact, the logic of the rollup query is contrary to your needs, its built-in logic is to return this parent record as soon as one of the child records matches the condition

For example:

Contact1 : won opportunity1 and lost opportunity2

Contact2 : won opportunity3

Contact3 : lost opportunity4

Then I query contacts with previous criteria, only contact2 can’t be returned, contact1 and contact3 both have one opportunity meet the condition.

So rollup query can’t directly return contacts which don't contain any won opportunities.

Or you can get results you need with Indirect method (For testing purposes, only one relationship is used):

1.Get all contacts that have opportunities.

2.Get all contacts which contain Won opportunities.

3.Compare the two results, contacts that does not appear in the second result are contacts that meets your requirements.

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums

Was this reply helpful? Yes No
Nosilla17 5 on at

Like (0)

Report
Copy link

Link copied!

Hello - thank you very much for your reply - that does answer my question.

Was this reply helpful? Yes No