Creating a Weekly Workflow for Task Status Updates

[Sosa25]

Hi i need help creating a workflow that will run once a week. based on the poject plan created, there are 52 total tasks in the plan and 9 people are tagged to various tasks. essentially the workflow will read the excel table(Project plan), and send out an email to each of the 9 individuals the status of their task. the status of their task is in column Q of the table and is the "Overall Status" field in the table.

For example if John Doe is assigned 15 out of the 52 tasks the workflow would need to be able to determine which 15 of the tasks are john doe's. the owner of each task would be in the "Owner" field of the table and the name of the task would be in the "Task" field. it would then in an email create a summary of the 15 tasks john doe is tagged to and the overall status of each task. I was thinking the summary would be a HTML Table where the first column would be "Owner" and have the owner name and then a second column for "Task" and then a third column for "Overall Status".

i was able to get this to work but the complicated part is having the html table be unique to who the owner is as each owner has different amounts of tasks tagged to them. the reason why the html should be unique to each owner is so everybody is getting a huge table with every single task's status. so addititonally the email would have to be sent to each distinct email assoicated with the owner only. i.e. John doe would only get an email for his tasks and the html table in his email would not include the task of others.

[Sosa25]

For clearer context:

In our ongoing efforts to optimize project management processes, I'm seeking your valuable input on creating a workflow for weekly task status updates. The task at hand involves automating the dissemination of task statuses to team members based on our project plan. Allow me to provide some context and outline our objectives.

primary objectives for this workflow are:

1. Automatically extracting task information from our project plan Excel sheet.
2. Generating personalized email summaries for each team member, reflecting the status of their assigned tasks.
3. Ensuring that each email contains only the tasks assigned to the respective team member.

o give you a clear picture, here's a breakdown of the proposed workflow:

1. Data Extraction: We'll develop a workflow to read the project plan Excel sheet, extracting task details such as owner, task name, and overall status.
2. Task Assignment: The workflow will then categorize tasks based on their owners, enabling us to identify which tasks belong to each team member.
3. Email Generation: Using the extracted task information, personalized email summaries will be generated for each team member, showcasing their assigned tasks and respective statuses.
4. Email Distribution: Finally, the emails will be sent out to the distinct email addresses associated with each team member, ensuring that they only receive updates relevant to their tasks.

main challenge lies in dynamically generating unique HTML tables for each team member's tasks within the email summaries. While we've made progress in implementing the workflow, achieving this level of customization has proven to be complex.

i can get the HTML table to run but it includes the entire 52 task rather than just the 15 john doe is tagged to

[Rhiassuring]

Hi there, 

Before we get into details, I want to determine ... 

You say Project Plan. Is this Project Online, Project for the Web? Or maybe you're actually using Planner?

Or, is it for some reason integral to go from the Excel spreadsheet?

For the individual HTML tables, though, the easiest way is going to be to do a "Filter Array", filter to the particular user, Create the HTML table from that output, and email it - just go through a "For Each" on each unique Person.

[Sosa25]

Hi appreciate your response. by project plan i'm referring to an excel file that is used as a planner of sorts. it is essentially a table with the tasks to be completed, the owners of the task, the task reviewer, the status of the task, and due dates for the task.

[Rhiassuring]

Is their email inside the spreadsheet, or just their name? 

And, if it's just their name, is it spelled / written the same as what's in AD (ie their display name for their email)

[Sosa25]

Yes their Email is in the spreadsheet

[Rhiassuring]

Okay, cool, I'll do up a demo. Brb 🙂 

[Sosa25]

Appreciate you! for added context these are the Fields in the table that would be extracted for the html table/email

Task Number, Deliverable description, Current owner, Overall Status, Preparer Due date, Email

[Rhiassuring · Accepted solution]

Haha well I've done up an example with just Status & Task  name, so you'll see that part and be able to add the rest as needed.

I'm not sure how new you are to PA, so let me know if any of this doesn't make sense as you work through it.

 The HTML Table is being created from the Filter Array output (in the apply to each) which is filtered based on a single user.

The compose that has a "union" at the very top is finding the unique names in the list, so that we don't send it multiple times.

[Sosa25]

Question on the HTML table. if its being created from the filter array the only field is "body" and doesnt let me select the field from the excel table to make the table

[Rhiassuring]

That's where you need to use the Parse JSON on the output from the Filter Array. It gives you that little bit of magic that lets you call out individual field names.