Unanswered

max values in group

(0) Share

Report

Posted on by 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

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (0)

Sort by

v-litu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
leopold1 12 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
v-litu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
leopold1 12 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
abm abm 32,985 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
leopold1 12 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
abm abm 32,985 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @leopold1

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

Was this reply helpful? Yes No
leopold1 12 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
abm abm 32,985 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @leopold1

Here it is.

Was this reply helpful? Yes No
leopold1 12 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No