Using a list values to find a planner task

[Lparadis23]

Hello! I am trying to create a flow where when a new list item is created it will find a planner task based on the 3 conditions. 1. The title of the task is equal to a list value 2. The person assigned to the task is equal to a list value of an email that is submitted 3. The task is in the bucket "to do". Then once it finds the task I want it to update the task to completed keeping the same assigned person. 

When I have tried to add multiple conditions, I lose the dynamic content of assigned. When I chose only one condition (which wouldn't work because there will be multiple tasks with the same title but different assignees) then I have the issue that it will find the task but it will not update it. Thank you for any assistance! 

[ManishSolanki]

Hi @Lparadis23 

Here is the sample flow which helps you in completing the flow:

In this example, I have used a custom SharePoint list "Task" and Title field contains the task title of the planner task.

The flow will search the planner task by title, bucket & 'assigned to' user to mark it complete in planner. Here, the user who creates the item will be search as 'assigned to' user for searching the tasks. The flow actions are as follows:

1. Add a trigger "When an item is created", since in planner task user id is stored so we need to add "Search for users" action to get the user id based on its email address:

2. Next, we will add "List buckets" action to get all the buckets for a given group & plan. We will use filter array action to filter the bucket based on its name (To do). This is to get the bucket id which is required while searching for a planner task:

Collect the bucket id in the compose action using the expression:

Expression needs to be added in expression box as highlighted above. Expression used for getting the bucket id:

first(outputs('List_buckets')?['body/value'])?['id']

3. Now, add List tasks action to get all the tasks of a group & plan. We will use filter array action to filter tasks based on title & bucket id:

To enter the filter query, click 'Edit in advanced mode' link button and enter the below query:

@and(equals(item()?['bucketId'], outputs('Compose')),equals(item()?['title'], triggerOutputs()?['body/Title']))

4. To filter based on assigned to user, we will first filter using xpath function in expression and then select the task id to be updated:

Expression used for 'From' parameter:

xpath(xml(json(concat('{"Root":{"Item":',body('Filter_array_2'),'}}'))), concat('//_assignments/userId[contains(text(),','',first(outputs('Search_for_users_(V2)')?['body/value'])?['id'],'',')]/../..'))

Expression used for 'Map' parameter:

json(xml(base64ToString(item()?['$content'])))

Note: To get the full textbox for Map parameter, click on 'Switch Map to text mode' button present on the right side of Map.

5. Finally, add "Apply to each" action, pass the output of select action and update the task:

Expression used for 'Task Id' parameter:

item()?['Item']?['Id']

If you need the .zip package of the flow, then I am happy to share it. You can import the flow in your environment using the .zip package.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Lparadis23]

Wow this is incredible! Would have never been able to come up with this on my own. I would love the zip as I am not sure my abilities could recreate your flow. This is my first post so not sure the best way for you to send it. REALLY appreciate your support! 

[Lparadis23]

@ManishSolanki  I took a stab at recreating your flow and got this error Any recommendations to fix? 

[ManishSolanki]

Hi @Lparadis23 

Could you pls share the output of Filter array 2 action? Pls replace the email addresses or any other sensitive information with sample values.

Thanks

[Lparadis23]

@ManishSolanki Thank you for your continued assistance! 

The Input is: 

[

  {

    "@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\"",

    "planId": "F_2l4R5XVUW0lC9FGO4NsWQABjML",

    "bucketId": "dSSkBwVTdUuxcfZQvP-a-WQAKCwR",

    "title": "ROBS Overview",

    "orderHint": "8585055944086272848Pm",

    "assigneePriority": "8585055943485960308",

    "percentComplete": 0,

    "createdDateTime": "2023-09-29T18:35:36.8815499Z",

    "hasDescription": false,

    "previewType": "automatic",

    "referenceCount": 0,

    "checklistItemCount": 0,

    "activeChecklistItemCount": 0,

    "priority": 5,

    "id": "RdsaPRJMYkq6nsfDOiulfmQAKyjv",

    "createdBy": {

      "user": {

        "id": "40471da7-205e-4016-a178-767e04e89e59"

      },

      "application": {

        "displayName": null,

        "id": "00000003-0000-0ff1-ce00-000000000000"

      }

    },

    "appliedCategories": {},

    "assignments": {

      "40471da7-205e-4016-a178-767e04e89e59": {

        "@odata.type": "#microsoft.graph.plannerAssignment",

        "assignedDateTime": "2023-09-29T18:35:36.8815499Z",

        "orderHint": "8585055944086272848Pz",

        "assignedBy": {

          "user": {

            "displayName": null,

            "id": "40471da7-205e-4016-a178-767e04e89e59"

          },

          "application": {

            "displayName": null,

            "id": "00000003-0000-0ff1-ce00-000000000000"

          }

        }

      }

    },

    "_assignments": [

      {

        "userId": "40471da7-205e-4016-a178-767e04e89e59",

        "value": {

          "@odata.type": "#microsoft.graph.plannerAssignment",

          "assignedDateTime": "2023-09-29T18:35:36.8815499Z",

          "orderHint": "8585055944086272848Pz",

          "assignedBy": {

            "user": {

              "id": "40471da7-205e-4016-a178-767e04e89e59"

            },

            "application": {

              "displayName": null,

              "id": "00000003-0000-0ff1-ce00-000000000000"

            }

          }

        }

      }

    ]

  }

The output is: 

    "body": []

[ManishSolanki]

Hi @Lparadis23 

The 'Filter array 2' compares the planner tasks title with Title in SharePoint & also compares the bucket id and accordingly filters the array. It seems that either SharePoint list item does not have title as 'ROBS Overview' or bucket id is different, so it does not filter any records and returns empty array. I assumed that the Title of SharePoint list will be the same of the task title in planner which always falls under 'To do' bucket, needs to be closed in planner when a user creates a new item.

Thanks

[Lparadis23]

@ManishSolanki Ah! So the title of the sharepoint list item is not the title of the planner task ID. Is it possible to change the title of the planner task to equal a value in the sharepoint list?

I was also curious since you have been so incredibly helpful and I will need to add this. The sharepoint list item will also need to be "completed" to trigger this action. 

 

 

[ManishSolanki]

Hi @Lparadis23 

Thanks for sharing the details. Pls specify the column that holds the "Assigned to" user in SharePoint list. Is it "Email ID" column?

Thanks

[Lparadis23]

@ManishSolanki Good Morning! Yes! Email ID = Assigned to. I made this picture to hopefully help as well. You are my hero right now, thank you! 

 

[ManishSolanki]

Hi @Lparadis23 

Thanks for sharing the columns names. I am assuming 'Course Status' is a choice field with single section, pls confirm.

Thanks

[Lparadis23]

Hello @ManishSolanki. Apologize for the delay, yes that is correct! 

[ManishSolanki · Accepted solution]

Hi @Lparadis23 

Here is the complete flow:

1. Create a "Automated cloud flow" and choose the trigger as "When an item is added or modified". As the flow will trigger only when status is set as completed so we will add trigger condition in this action. To do this, click on ellipses menu(...) and then select "Settings" from sub menu. Scroll down to the bottom and add the below trigger condition:

@equals(triggerOutputs()?['body/CourseStatus/Value'], 'Completed')

2. Next, to get the user id of the assigned to user, add "Search for users" action & pass the value of email id column from SharePoint list:

3. Add "List buckets" actions to get all buckets for a group & plan:

4. Add Filter array action to get the bucket id of "To do" bucket:

Add compose action to collect the id of the bucket:

You need to write an expression to get the bucket id as highlighted in the above screenshot:

first(outputs('List_buckets')?['body/value'])?['id']

5. Add "List tasks" action to get all the tasks for a group & plan:

Add another filter array action to get the tasks based on course title & bucket id. Pass the value object from the list tasks action:

Click "Edit in advanced mode" button on the bottom of action and enter the below filter query:

@and(equals(item()?['bucketId'], outputs('Compose')),equals(item()?['title'], triggerOutputs()?['body/Course']))

Add "Select" action to filter the output of filter array based on the assigned to user id:

You need to write expression for both "From" & "Map" property.

Expression for "From" parameter:

xpath(xml(json(concat('{"Root":{"Item":',body('Filter_array_2'),'}}'))), concat('//_assignments//userId[contains(text(),','''',first(outputs('Search_for_users_(V2)')?['body/value'])?['id'],'''',')]/../..'))

 Before entering expression for "Map" parameter, click "Switch Map to text mode" button exists on the right side of "Map" label and then put the below expression in the textbox:

json(xml(base64ToString(item()?['$content']))) 

6. Finally, we will iterate the filtered tasks by passing the output of Select action. In apply to each action, add "Update task" action. In Task Id, enter the expression and set the Progress as Completed:

items('Apply_to_each')?['Item']?['Id']

Its quite lengthy but I hope it will help you in designing the required flow.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Lparadis23]

@ManishSolanki You did it! Can't tell you how much I appreciate your continued assistance through all the troubleshooting! Thank you thank you!!