Microsoft Dynamics 365 | Integration, Dataverse...

Fetchxml: Get sum of all distinct field values with different records

(0) Share

Report

Posted on by Jerome Vicente

Hi,

I have this fetchxml:

I have this scenario:

Customer Journey Custom Channel 1 (3 records with Contacts):

Contact 1
Contact 2
Contact 3

Customer Journey Custom Channel 2 (2 records with Contacts):

Contact 2
Contact 4

When I run the fetchxml, it will display 4 since Contact 2 is on both Customer Journey Custom Channel. I want to display 5 instead.

Thank you in advance.

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report
Hi Jerome Vicente,

I think this could not be achieved by a single fetchxml. In your case, you can only get the count of distinct contact in each Customer Journey record by this fetchxml:

It's result would be like this:

If you want to achieve your requirement, you could create a custom field in Customer Journey entity to populate it with the count of contacts by flow/plugin.(Rollup field can't work in this situation.)

Was this reply helpful? Yes No
Jerome Vicente 40 on at

Like (0)

Report

Hi Steve,

Thank you for the answer. I actually use the fetchxml above and I'm planning to use this on SSRS but I cannot be able to filter by campaign and then sum the column.

Thanks,

Jerome

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Jerome Vicente,

What does "I cannot be able to filter by campaign and then sum the column" mean? Do you want to set the campaignid as dynamic value. For example: Do you want to get the count of contacts of current campaign record?

Was this reply helpful? Yes No
Jerome Vicente 40 on at

Like (0)

Report

Hi Steve,

I want to get all of the contacts of Campaign but there is a case that a contact can be on different Customer Journey just like on my question above. The goal is to filter by CAMP then get the sum of CCA_CON using SSRS.

Thanks,

Jerome

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi Jerome Vicente,

As I said above, you could not use fetchXML to achieve this. As a workaround, you could create a custom field in Customer Journey entity to populate it with the count of contacts by flow/plugin.(Rollup field can't work in this situation.)

Was this reply helpful? Yes No