Create a Unique ID in Planner for use in update flow

[rixmcx59]

Hello all, I am having trouble trying to compose a unique ID using Checklist Item ID and Task ID. In the Plan The task id is not unique and the checklist item id is not unique but if you combine the two it becomes a unique id. My scenario is,

1. I have moved the planner data to SharePoint via workflow and combined Task and Checklist ID

2. I want to be able to check for changes in the Bucket Name or Checklist is Checked column before adding the record to SPO list

3. I need the UID to filter current Planner data to the Planner data with a condition

4.  I am able to get Bucket Name using a filter array because Bucket ID exists in the tasks list but can't figure out how to get Checklist ID. below is my flow, it succeeded but the Input/Output showed "Click to download" then went to a blnk page. 

[ChadVKealey]

I can't tell for sure, but strongly suspect the issue is in your "Filter array 2" action. Can you show the code in each "side" of the condition (maybe use Peek code) so we can see what actual values you're plugging in there? The blank screen means that the action isn't returning any data (consistent with a data mismatch of some kind).

[rixmcx59]

I think I got, I changed the compose expression from 

first(body('Filter_array_2'))?['name'] to  first(body('Filter_array_2'))?['id'], the filter array inputs/outputs look good, retrieving the 9 checklist item ID's, Title's etc..I didn't  really know what I was doing in the expression, thinking'name' returned the single value you were after.
Now hopefully I can use the ID, bucket name and checklistIsChecked  in my condition to check if any thing has changed.
Or maybe you know a better way to achieve this, there is no Planner action for "Planner item change"

Thanks for the reply

[ChadVKealey]

No, there are only three triggers in the Planner connector: when a task is created, when a task is completed and when a task is assigned to me. So, no way to act "automatically" on the checking of a checklist item. 

I'm trying to wrap my head around your overall goal. It sounds like you're creating items in a SharePoint list corresponding not just to each task in a plan, but each checklist item in each task in a plan. I imagine the reason for this is tracking/auditing or a sort of backup of the data? Then, when checklist items are checked, you want to record that back to the SP list? Since there's no trigger for that, you're only real option would be to iterate through your SP list items (ALL of them) at some interval, check the corresponding task in the plan, check the status of the checklist items and act accordingly. Unless you have a small number of tasks and checklist items, it's going to take a long time for that flow to run. 

I really like Planner...I think that - out of the box - it provides a lot of functionality, but it is not always the best choice. You might consider just using a SharePoint list and building flows to replicate the built-in functionality in Planner. The tricky part would be the checklist items since there's no simple way to achieve that in a flat SP list. You'd need two related lists (tasks and checklist items) with a relationship, and to present that to end users, a Power Apps canvas app (or some other custom interface).

[rixmcx59]

My goal is to show change over time, when a task changes buckets, or when a checklist item completes.

I am already doing this with 2 SharePoint lists and a Power BI report, like you mentioned. I am combining ChecklistItemID and TaskID this creates my UID for each row in SharePoint, otherwise there is no unique ID that I know of, in this plan task id and Checklist ID is not unique.

What I am currently trying to do is check for change before creating the new item in SharePoint, maybe this is not possible, but I feel like I am close. What I need is the variable ChecklistID+TaskID then in a condition I would use the following, Current PlannerDataUID=SpoPlannerDataUID and PlannerBucketName <> SpoBucketName or PlannerChkListItemIsChecked <> SpoChkListItemIsChecked or something similar the works. 

[rixmcx59]

here is the flow so far, I can get the Bucket name and Checklist Item ID using filter array, I don't know what to do next? I need to get the TaskID and combine with ChecklistID and then what? create a variable? to use in my condition. I'm stuck but will keep trying.

[ChadVKealey]

So, you need to add a "Get items" step pointing at your SP list with an odata filter of <column-with-TaskID-and-ChecklistID> eq <value/variable-in-your-flow-with-TaskID-and-ChecklistID>. If that returns any items, then you know it exists; if it returns nothing, then it doesn't.

[rixmcx59]

I will try that Monday morning, sounds very promising. Thanks for the direction and I will let you know how it goes. I guess I can remove the first get items since it's not being used.

[ChadVKealey]

Yes...it's a common misconception that you need to get all the items from a list to evaluate them. If you have a small list, that can work, but it's inefficient. The better option is to use the odata filter to get just those items that you actually need.  In fact, when you use Get items without specifying an odata filter, the flow checker will (should) warn you about that. Also, writing those odata filters can be a challenge, but for text fields it's pretty easy. Just be sure to use the internal column name and put the value you're trying to match in single quotes (ie: 'value-you-want-to-match').

[rixmcx59]

I am missing something, the input is a single UID and the output body is empty, seems like I need another apply to each, or some way to increment through all the UID's

[ChadVKealey]

I think you're missing a space after "eq" in your odata filter. Also, are you certain that is the internal name of that column and that there is an item in that list with that value in that column?

[rixmcx59]

Ok, removed the space, the internal name is correct. I only have 9 records in the SharePoint list but the odata filter should find the 9 that match correct?

[janybuny9]

Hey, just try to create an unique ID by using timestamp... it works
first is: "when creating new item"

scnd is: "variable initialize"

thrd is: "update Tasks"

you have to put a variable initialize and tell'm how to show the timestamp,for example: 
You have to know how the timestamp looks like and put it to the test value field. In the output field you can show'em how to get showed the unique number...

and then it works pretty fine

BR

Jana

[takolota]

@rixmcx59 @janybuny9 @ChadVKealey 

If anyone is trying to sync a lot of Planner over to SP lists, I highly recommend just using SharePoint’s built-in board view instead to get the kanbans/cards planner-like view without all this extra complexity.

Like this template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Project-Tracker-SharePoint-and-Teams/td-...

[janybuny9]

thank you very much!!

[ChadVKealey]

I have rolled out a few projects using the board style list view in place of or addition to Planner. In one scenario, the group had been using a "requests" list for years, but NEVER actually updated the status of an item because they said they couldn't figure out how. With the board view, it's as easy as dragging. On the flipside, it's easy for people to accidentally update the status. With another group, they have an instant flow on their requests list that is used to create planner tasks for as needed (sort of components of the request). It's more of a cross-check mechanism, but allows the team the visibility they wanted to see and track tasks.