Microsoft Dynamics 365 | Integration, Dataverse...

How to setup a Flow to only care about the max value (date/time) on child records underneath an Opportunity (1:N)?

(0) Share

Report

Posted on by CRM Mike

I'm trying to build a reoccurring flow which would send a daily email notification to an Opportunity Owner for each Opportunity which matches the trigger condition. This condition queries both the Opportunity and child record entity data. I've done this successfully (screenshot below) where it pulls the list of child rows by using a FetchXML query to define the condition. More specifically there is a date field on each child record which is used to determine the timing on when the criteria is met to start sending the email notifications (if date field > 2 days old). After this, it takes the Opportunity IDs and has it look for the user's email address to help identify the To: address and drop a hyperlink for the Opportunity requiring attention.

But now I'm needing to build a second scenario which would further narrow the Listed Rows of child records and focus specifically on the one child record per opportunity with the most recent date. So for example: if there are 5 child records which all have the same parent Opportunity value lookup - I want to be able and say the 2 day condition should be based on the max date value across those 5 child records. Otherwise it would meet the condition pre-maturely and start sending out the email notifications when it's not actionable (because another child record has a further out date which has to take place before the Opportunity is ready to be acted upon).

I haven't tested this, but I'm pretty sure I could add a rollup on the Opportunity which would pull the max date/time from those child records and then I could just query directly on the Opportunity entity, but I'm trying to avoid using a rollup on this as it would only be serving this one purpose and would tie up resources running that system job every day.

I appreciate any help I might receive.

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

RE: How to setup a Flow to only care about the max value (date/time) on child records underneath an Opportunity (1:N)?

Hi Mike,

Maybe you can use flow to retrieve child records for each opportunities to get oldest date.

On the List records action you can use the Order(Asc) by and column name of the date and then get Top 1 record.

Get a list of Child records and order by the Created On Date in ascending order, and my retrieve query is only returning the top one record which would be the oldest date:

Retrieving most recent or oldest records in Power Automate ~ Benitez Here.

Was this reply helpful? Yes No
Suggested answer

Bipin D365 28,983 Moderator on at

Like (0)

Report

RE: How to setup a Flow to only care about the max value (date/time) on child records underneath an Opportunity (1:N)?

Hi,

You can use Fetchxml aggregate function in your List Row action in power automate-

docs.microsoft.com/.../use-fetchxml-aggregation

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
CRM Mike 5 on at

Like (0)

Report

RE: How to setup a Flow to only care about the max value (date/time) on child records underneath an Opportunity (1:N)?

Bipin,

Thanks for this information about aggregate functions in FetchXML. I'm still learning my way around FetchXML so I usually have to use the FetchXML Builder paired with SQL 4 CDS, but I can't get it to transform this query from SQL into FetchXML.

Can the following query be transformed to FetchXML? If so, would you be able to help me construct it properly? In SQL 4 CDS it seems to break it into 3 separate FetchXML queries when you look at the execution plan and I don't know the proper way to combine them into one FetchXML query.

The successful SQL Query I'm trying to transform:

SELECT opp.opportunityid

,opp.name

,opp.customeventtypename

,opp.ownerid

,opp.owneridname

,opp.statecodename

,opp.totalattendance

,A.MaxArrivalDateTime

FROM opportunity opp

INNER JOIN

(

SELECT c.opportunity

,max(c.arrivaldatetime) MaxArrivalDateTime

FROM oppchildrecords c

GROUP BY c.opportunity

)A ON (A.opportunity = opp.opportunityid)

WHERE opp.customeventtypename = 'EventX'

AND opp.statecodename = 'Open'

AND opp.totalattendance <> '0'

AND opp.totalattendance is not null

AND A.MaxArrivalDateTime <= DATEADD(day, -2, GETDATE())

What SQL 4 CDS does when I try to see the display the FetchXML on execution:

The three individual FetchXML Queries it produces in that execution plan above are:

Merge Join:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
<entity name='opportunity'>
<attribute name='opportunityid' />
<attribute name='name' />
<attribute name='customeventtype' />
<attribute name='ownerid' />
<attribute name='statecode' />
<attribute name='totalattendance' />
<link-entity name='customeventtype' to='customeventtype' from='customeventtypeid' alias='opportunity_customeventtype' link-type='outer' />
<filter>
<filter>
<filter>
<condition attribute='customeventtype' entityname='opportunity_customeventtype' operator='eq' value='EventX' />
<condition attribute='statecode' operator='eq' value='0' />
</filter>
<condition attribute='totalattendance' operator='ne' value='0' />
</filter>
<condition attribute='totalattendance' operator='not-null' />
</filter>
<order attribute='opportunityid' />
</entity>
</fetch>

T-SQL Try Catch:

<fetch xmlns:generator='MarkMpn.SQL4CDS' aggregate='true'>
<entity name='oppchildrecords'>
<attribute name='opportunity' alias='opportunity' groupby='true' />
<attribute name='arrivaldatetime' alias='MaxArrivalDateTime' aggregate='max' />
<order alias='opportunity' />
</entity>
</fetch>

Hash Match Aggregate:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
<entity name='oppchildrecords'>
<attribute name='opportunity' />
<attribute name='arrivaldatetime' />
</entity>
</fetch>

Thank you!

Was this reply helpful? Yes No