max values in group

[leopold1]

Hi, I would like to create the flow and send an email notification, getting data from SharePoint. table looks like this.

Id Country          code   values  dates                reporter

1  Portugal          1          1        30/02/2020      mouse

2  Portugal          1          2        30/03/2020      mouse

3  Sweden           2          1        30/05/2020      ninia

so if Country and code are the same, I want to group and find max number in values column and check if dates are greater than 90 days from current day., and I would like to send email to reporter. so in this case, id 2, and id 3 need to be sent to reporter.

does anyone can put me on the right track?

Any feedbacks are welcome.

Thank you

[v-litu-msft]

Hi @leopold1,

According to your description, I create a Flow as below, you could following these steps to approach that:

1. Get items without any filter query, and initialize an array variable;

2. Create a loop, append the county and code key-values into the variable;

{
"Country": @{items('Apply_to_each')?['Country']},
"code": @{items('Apply_to_each')?['code']}
}

3. Use union() function to remove repeat item;

4. Put its output of Steps 4 into a loop, then create second Get items action with filter query, and Order by set to "desc" and top count value to "1":

Country eq '@{item()?['Country']}' and code eq '@{item()?['code']}'

5. Get the first member of return, and compare its dates with the utc time - 90 days:

outputs('Get_items_2')?['body/value'][0]['dates']

addDays(utcNow(),-90)

If its dates less than 90 days ago, send email to reporter.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leopold1]

Hi  @v-litu-msft  thank you very much for your support. I fixed some errors but in my email I am receiving duplicated records several times. Can i do anything else regarding to it?

[v-litu-msft]

Hi @leopold1，

I guess you may be lost step 3 to remove duplicated records.

Could you please confirm this point?

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leopold1]

Hi I used the Union() as it was described but still get dublicate emails.

Also my dates in orginal file is YYYY-MM-DD can be this reason?

[abm]

Hi @leopold1 

There is another way to determine this but this requires some schema changes. Add a new column called highervalue or select as Yes/No. Then whenever a record gets inserted in a list run a flow to find whether the ID and country already exists. If no record exists set the new field as Yes. If already exists then check the value Vs the new value then update the relevant record column to Yes. Next when you run the report all you need to do is filter the record by new field value as 'Yes'.

Thanks

[leopold1]

Hello @abm thank you vey much. I got stuck in this step:

"If already exists then check the value Vs the new value then update the relevant record column to Yes." Could you please help me here?

Also, what will happened to old "yes" column when new item will be created? For example:

Id Country          code   values  dates                reporter highervalue

2  Portugal          1          2        30/03/2020      mouse    Yes

next time when user will add Id 3: Portugal and code will be checked and in highervalue  it will be assigned yes.

3  Portugal          1          3        30/05/2020      mouse    Yes                                                                                               

But id 2 will be kept with highervalue yes .

Thank you in advance.

[abm]

Hi @leopold1 

The old value you need to update as 'No'.

[leopold1]

Hi @abm in theory i know, but I could not build a flow. any more detailed tips? I am a fresh user and all feedback will be valuable. thank you.

[abm]

Hi @leopold1 

Here it is.

[leopold1]

hi @abm , thank you for the flow.  I tested,  flow runs successfully but it is not updated anything in highervalue column.

[abm]

Hi @leopold1 

Make sure you are using the right ID for updating the record. Here too many id's we have. Most likely that's the only reason its not updating. 

[llllllu]

hi, here you use the GET ITEMS, but how to do with that with excel?

[takolota]

This template gives the largest / most recent or the smallest/ oldest records for unique values in given columns: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Distinct-Records/td-p/2191533