cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

How to use Aggregation transformation in Flow - List records?

I'm trying to groupby and do 2 types of aggregations: sum for 25 fields and count for the same 25 fields. Ideally I want something like 

 

groupby(
	(cr3df_site2),
	aggregate(
		cr3df_score01 with sum as cr3df_score01sum,
		cr3df_score02 with sum as cr3df_score02sum,
		cr3df_score01 with countcolumn as cr3df_score01count,
		cr3df_score02 with countcolumn as cr3df_score02count
	)
)

 

Clipboarder.2020.07.16.png

However count and countcolumn isn't recognised. Does anyone know if there's a way to achieve this? 

 

I need do to a count because I need to calculate mean that ignores null-fields. I believe using an average will simply divide the sum by the number of records which is not the data I need. Hence I want to do both a sum and a countcolumn (which counts non-null values of that column), the results of which I plan to write to a CDS entity, and then in my CDS entity I have calculation fields that do Sum/Count to get the mean that I need.

 

Second question: is there a way to group by 2 fields, cr3df_site2 and c43df_participanttype2? If I can group by multiple fields that would save me having to set up multiple List records to cover all my participant types.

 

My end goal is actually a little more complex than above. I do have a fetchxml query that achieves my desired goal perfectly but unfortunately it does not work in Flow. So I'm hoping there's still alternatives available.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @JohnAageAnderse,
Thank you for responding! 🙂
Yes I have tried that. The link in my last paragraph goes to my prior post where I admittedly present quite bitter about the letdown of Flow in this area. Very disappointing. The MS support engineer I was assigned has confirmed that aggregation is currently not supported in Flow's FetchXML at the moment. Alas!
I have gone on to create a HUGE flow with variables and loops, it went all the way up to the limit of 500 templates. It does the job, but boy did Flow give me a virtual ulcer for 2 weeks! 😅

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Hello @Lexicron 

I looked at the documentation for CDS and there are two sets - one just named Common Data Service and one named Common Data Service (current environment). From your screenshot you are using the first one.

The second one has the same action "List records" and it contains a field "Fetch Xml Query" - and as you mentioned you had such, could that be an option for you? Unless you already tried 🙂

Kind regards, John

Highlighted

Hi @JohnAageAnderse,
Thank you for responding! 🙂
Yes I have tried that. The link in my last paragraph goes to my prior post where I admittedly present quite bitter about the letdown of Flow in this area. Very disappointing. The MS support engineer I was assigned has confirmed that aggregation is currently not supported in Flow's FetchXML at the moment. Alas!
I have gone on to create a HUGE flow with variables and loops, it went all the way up to the limit of 500 templates. It does the job, but boy did Flow give me a virtual ulcer for 2 weeks! 😅

View solution in original post

Highlighted
Frequent Visitor

Hi @Lexicron i'm facing the same issue here, FetchXML, do all what i need, which is the following...

<fetch version="1.0" output-format="xml-platform" aggregate="true" mapping="logical" distinct="false" >
  <entity name="entityName" >
    <attribute name="AttributeId" alias="Quantity" aggregate="countcolumn" />
    <attribute name="AttributeBrandId" alias="Brand" groupby="true" />
    <attribute name="MarketingSourceId" alias="Marketing" groupby="true" />
    <order alias="Marketing" descending="true" />
    <order alias="Brand" descending="false" />
    <filter type="and" >
      <condition attribute="createdon" operator="last-x-hours" value="24" />
      <condition attribute="MarketingSourceId" operator="not-null" />
    </filter>
  </entity>
</fetch>

 

Works like a charm on FetchBuilder, but when i add it to CDS List records... powerAutomate explodes!!! InternalServerError, and is not more than 3 records xD 😕

Can you guide me on how you achieved it?

 

Thanks in advance!!!

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (13,030)