Answered

How to get the distinct values of sharepoint coloumn in power automate and perform ApplytoEach Action On the Output

(0) Share

Report

Posted on by Govind_Prajapat

I have a task list. It has four columns Task, Deadline, ActionOwner1, ActionOwner2. The list is having multiple tasks for single Action Owner. I am not maintaining the list of Action Owners Seperately. We have to Retrieve the list of ActionOwners from task list itself.

Now I want to Send the Mail to Action Owners 5 days prior to the deadline with the list of all of their subtasks. Keeping in mind that each ActionOwner should get a single mail with his/her subtasks list no matter he/she is ActionOwner1 or ActionOwner2 for a task.

Note: I have tried the Select Action to retrieve the list of ActionOwners but I am not able to Perform ApplyToEach Action on the output of Select Action.

Kindly help with this. How Can I achieve this

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Unknown geen idee 1,757 on at

Like (0)

Report
Copy link

Link copied!

Dear G,

Finding unique/distinct values is a common problem and usually solved with the union() expression.
Once you know this, you should be able to find many examples of using this expression in the way you like. Check YouTube/ChatGPT/this forum, etc.

Happy flowing,
Koen

Was this reply helpful? Yes No
Govind_Prajapat 14 on at

Like (0)

Report
Copy link

Link copied!

that is not working for me I am not able to retrieve the value of action owner from the output of action owners.

Anybody can please help with this with example

Was this reply helpful? Yes No
Matthy79 4,188 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Please provide sample data and desired result.

Was this reply helpful? Yes No

Govind_Prajapat

14 on at

Like (0)

Report

Copy link

Link copied!

Assume the List of tasks:

Task	Deadline	ActionOwner1(PersonType)	ActionOwner2(PersonType)
Task1	20/11/2023	person1	person2
Task2	15/11/2023	person2	person1
task3	20/11/2023	person3	person2
task4	20/11/2023	person2	person1
task5	15/11/2023	person3	person1
task6	15/11/2023	person1	person2
task7	20/11/2023	person2	person3
task8	15/11/2023	person3	person2
task9	15/11/2023	person1	person3

Now the list for notification configuration settings:

DaysBeforeDeadline	EmailBody
5	5 days to the deadline of following tasks
10	10 days to the deadline of following tasks

Now suppose the Flow is running on 10/11/2023 the mails for the task actionowners should be like below:

1. For Person1:

Hi Person1,

5 Days to the Deadline of following tasks. Please Complete Before the Deadline.

Task	Deadline
task2	15/11/2023
task5	15/11/2023
task6	15/11/2023
task9	15/11/2023

2. For Person2:

Hi Person2,

5 Days to the Deadline of following tasks. Please Complete Before the Deadline.

Task	Deadline
task2	15/11/2023
task6	15/11/2023
task8	15/11/2023

3. for Person3:

Hi Person3,

5 Days to the Deadline of following tasks. Please Complete Before the Deadline.

Task	Deadline
task5	15/11/2023
task8	15/11/2023
task9	15/11/2023

Now Person1, Person2, and Person3 should also get another mail with the list of the tasks the have the deadline after 10 days.

Person1: list - task1 , task4

Person2: list - task1, task3, task7

person3: list - task3, task7

For More Clearance :

Flow Run Date: 15/11/2023

all action owners should get the list of their tasks which are going to end on 20/11/2023 with the email body 5 days to the deadline

Flow run date: 05/11/2023.

all action owners should get the list of their tasks which are going to end on 15/11/2023 with the email body 10 days to the deadline.

@Matthy79 I believe above Justification has given the clear-cut idea of my requirement. I have already tried suggested methods by @Koen5 but these are not working in my case.

So, Please Provide the concise solution for the above requirement. Kindly give a step by step solution if possible. I am tired of trying different methods.

Thanks in advance.

Was this reply helpful? Yes No

Verified answer

Matthy79 4,188 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
This is how it could work.

Depending on the real data it would be better to create it a little different.

{"id":"f36864c1-ea54-48db-b325-0d30656495cf","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Notify","operationDefinition":{"type":"Scope","actions":{"Deadline_Data":{"type":"Compose","inputs":[{"Task":"Task1","Deadline":"2023-09-19","ActionOwner1":"person1","ActionOwner2":"person2"},{"Task":"Task2","Deadline":"2023-09-14","ActionOwner1":"person2","ActionOwner2":"person1"},{"Task":"Task3","Deadline":"2023-09-19","ActionOwner1":"person3","ActionOwner2":"person2"},{"Task":"Task4","Deadline":"2023-09-19","ActionOwner1":"person2","ActionOwner2":"person1"},{"Task":"Task5","Deadline":"2023-09-14","ActionOwner1":"person3","ActionOwner2":"person1"},{"Task":"Task6","Deadline":"2023-09-14","ActionOwner1":"person1","ActionOwner2":"person2,"},{"Task":"Task7","Deadline":"2023-09-19","ActionOwner1":"person2","ActionOwner2":"person3"},{"Task":"Task8","Deadline":"2023-09-14","ActionOwner1":"person3","ActionOwner2":"person2"},{"Task":"Task9","Deadline":"2023-09-14","ActionOwner1":"person1","ActionOwner2":"person3"}],"runAfter":{}},"DaysBeforeDeadlineWarning_Array":{"type":"Compose","inputs":[5,10],"runAfter":{"Deadline_Data":["Succeeded"]}},"Apply_to_each_DayWarning":{"type":"Foreach","foreach":"@outputs('DaysBeforeDeadlineWarning_Array')","actions":{"Filter_Tasks":{"type":"Query","inputs":{"from":"@outputs('Deadline_Data')","where":"@equals(item()['Deadline'], addDays(utcNow(), items('Apply_to_each_DayWarning'), 'yyyy-MM-dd'))"},"runAfter":{}},"Select_ActionOwner1":{"type":"Select","inputs":{"from":"@body('Filter_Tasks')","select":"@item()['ActionOwner1']"},"runAfter":{"Filter_Tasks":["Succeeded"]}},"Select_ActionOwner2":{"type":"Select","inputs":{"from":"@body('Filter_Tasks')","select":"@item()['ActionOwner2']"},"runAfter":{"Select_ActionOwner1":["Succeeded"]}},"Apply_to_each_ActionOwner":{"type":"Foreach","foreach":"@union(body('Select_ActionOwner1'),body('Select_ActionOwner2'))","actions":{"Filter_Tasks_for_ActionOwner":{"type":"Query","inputs":{"from":"@body('Filter_Tasks')","where":"@equals(or(equals(item()['ActionOwner1'], items('Apply_to_each_ActionOwner')), equals(item()['ActionOwner2'], items('Apply_to_each_ActionOwner'))), true)"},"runAfter":{}},"Create_HTML_table_for_ActionOwner":{"type":"Table","inputs":{"from":"@body('Filter_Tasks_for_ActionOwner')","format":"HTML","columns":[{"header":"Task","value":"@item()['Task']"},{"header":"Deadline","value":"@item()['Deadline']"}]},"runAfter":{"Filter_Tasks_for_ActionOwner":["Succeeded"]}},"Compose_Dummymail":{"type":"Compose","inputs":"Send Mail with this information to @{items('Apply_to_each_ActionOwner')}:\n\n@{items('Apply_to_each_DayWarning')} Days to the Deadline of following tasks. Please Complete Before the Deadline.\n\n@{body('Create_HTML_table_for_ActionOwner')}","runAfter":{"Create_HTML_table_for_ActionOwner":["Succeeded"]}}},"runAfter":{"Select_ActionOwner2":["Succeeded"]}}},"runAfter":{"DaysBeforeDeadlineWarning_Array":["Succeeded"]}}},"runAfter":{}}}

Just copy everything and put it into a dummy flow.

Was this reply helpful? Yes No