logic app ( power automate) extract checklist items from a task from a planner plan
I have a 'get the checklist Items for each task from the template plan (task details)' action
which is a http get
https://graph.microsoft.com/v1.0/planner/tasks/@{items('For_each_template_task')?['id']}/details
then a 'Parse JSON testing' action
schema:
{
"properties": {
"@@odata.context": {
"type": "string"
},
"@@odata.etag": {
"type": "string"
},
"checklist": {
"additionalProperties": {
"properties": {
"@@odata.type": {
"type": "string"
},
"isChecked": {
"type": "boolean"
},
"lastModifiedBy": {},
"lastModifiedDateTime": {
"type": "string"
},
"orderHint": {
"type": "string"
},
"title": {
"type": "string"
}
},
"type": "object"
},
"type": "object"
},
"description": {
"type": "string"
},
"id": {
"type": "string"
},
"previewType": {
"type": "string"
},
"references": {
"properties": {},
"type": "object"
}
},
"type": "object"
}
content
@{body('get_the_checklist_Items_for_each_task_from_the_templateplan(task_details)')}
Output data
{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#planner/tasks('2B69MwLkekuzBBu38EcaDpcANQ0l')/details/$entity",
"@odata.etag": "W/\"JzEtVGFza0RldGFpbHMgQEBAQEBAQEBAQEBAQEBARCc=\"",
"id": "2B69MwLkekuzBBu38EcaDpcANQ0l",
"description": "",
"previewType": "checklist",
"references": {},
"checklist": {
"14037": {
"@odata.type": "#microsoft.graph.plannerChecklistItem",
"isChecked": false,
"title": "test1",
"orderHint": "8585Fg",
"lastModifiedDateTime": "0001-01-01T00:00:00Z",
"lastModifiedBy": null
},
"36310": {
"@odata.type": "#microsoft.graph.plannerChecklistItem",
"isChecked": false,
"title": "test2",
"orderHint": "8586002405mV",
"lastModifiedDateTime": "0001-01-01T00:00:00Z",
"lastModifiedBy": null
},
"45221": {
"@odata.type": "#microsoft.graph.plannerChecklistItem",
"isChecked": false,
"title": "test3",
"orderHint": "8586002406569253572P.",
"lastModifiedDateTime": "0001-01-01T00:00:00Z",
"lastModifiedBy": null
},
"61052": {
"@odata.type": "#microsoft.graph.plannerChecklistItem",
"isChecked": false,
"title": "test4",
"orderHint": "8585\\Y",
"lastModifiedDateTime": "0001-01-01T00:00:00Z",
"lastModifiedBy": null
}
}
}
the keys like,14037, 36310, 45221, 61052 are dynamic keys ( these are checklist items in a task, in a bucket of a planner plan) these are from the template plan
for each of these checklist items I also want to create checklist in the destination planner,for the specific task
in the for each I'm using @createArray(outputs('Parse_JSON_testing'))
but here the for each loop runs only once and it should run 4 times in this case
inside the for each I have the 'create checklist items on the new tasks' action
a http patch
which should put the checklist items in the tasks, basically what I'm trying to do is to clone the plans, with their buckets, tasks and checklist items. The creation of buckets, tasks works perfectly, only problem is the checklist items (subtasks)
what am I doing wrong? How can I get my for each to iterate properly?
I have tried different approaches, with select and filter actions, but I wasn't able to get it right.
Parse JSON isn't going to work given you've got a dynamic structure.
I have two approaches for you ...
Advanced Data Operations
A nice way of doing this is to use the Advanced Data Operations connector. It has an operation called Json Properties to Name/Value Pair Array which will break down the data and provide you with the ability to understand the names of properties and their associated values.
https://statesolutions.com.au/json-properties-to-name-value-pair-array/
Passing that data into that operation will yield this result ...
[
{
"propertyName": "id",
"propertyType": "String",
"propertyValue": "2B69MwLkekuzBBu38EcaDpcANQ0l"
},
{
"propertyName": "description",
"propertyType": "String",
"propertyValue": ""
},
{
"propertyName": "previewType",
"propertyType": "String",
"propertyValue": "checklist"
},
{
"propertyName": "references",
"propertyType": "Object",
"propertyValue": []
},
{
"propertyName": "checklist",
"propertyType": "Object",
"propertyValue": [
{
"propertyName": "14037",
"propertyType": "Object",
"propertyValue": [
{
"propertyName": "isChecked",
"propertyType": "Boolean",
"propertyValue": false
},
{
"propertyName": "title",
"propertyType": "String",
"propertyValue": "test1"
},
{
"propertyName": "orderHint",
"propertyType": "String",
"propertyValue": "8585Fg"
},
{
"propertyName": "lastModifiedDateTime",
"propertyType": "Date",
"propertyValue": "0001-01-01T00:00:00Z"
},
{
"propertyName": "lastModifiedBy",
"propertyType": "Null",
"propertyValue": null
},
{
"propertyName": "@odata.type",
"propertyType": "String",
"propertyValue": "#microsoft.graph.plannerChecklistItem"
}
]
},
{
"propertyName": "36310",
"propertyType": "Object",
"propertyValue": [
{
"propertyName": "isChecked",
"propertyType": "Boolean",
"propertyValue": false
},
{
"propertyName": "title",
"propertyType": "String",
"propertyValue": "test2"
},
{
"propertyName": "orderHint",
"propertyType": "String",
"propertyValue": "8586002405mV"
},
{
"propertyName": "lastModifiedDateTime",
"propertyType": "Date",
"propertyValue": "0001-01-01T00:00:00Z"
},
{
"propertyName": "lastModifiedBy",
"propertyType": "Null",
"propertyValue": null
},
{
"propertyName": "@odata.type",
"propertyType": "String",
"propertyValue": "#microsoft.graph.plannerChecklistItem"
}
]
},
{
"propertyName": "45221",
"propertyType": "Object",
"propertyValue": [
{
"propertyName": "isChecked",
"propertyType": "Boolean",
"propertyValue": false
},
{
"propertyName": "title",
"propertyType": "String",
"propertyValue": "test3"
},
{
"propertyName": "orderHint",
"propertyType": "String",
"propertyValue": "8586002406569253572P."
},
{
"propertyName": "lastModifiedDateTime",
"propertyType": "Date",
"propertyValue": "0001-01-01T00:00:00Z"
},
{
"propertyName": "lastModifiedBy",
"propertyType": "Null",
"propertyValue": null
},
{
"propertyName": "@odata.type",
"propertyType": "String",
"propertyValue": "#microsoft.graph.plannerChecklistItem"
}
]
},
{
"propertyName": "61052",
"propertyType": "Object",
"propertyValue": [
{
"propertyName": "isChecked",
"propertyType": "Boolean",
"propertyValue": false
},
{
"propertyName": "title",
"propertyType": "String",
"propertyValue": "test4"
},
{
"propertyName": "orderHint",
"propertyType": "String",
"propertyValue": "8585\\Y"
},
{
"propertyName": "lastModifiedDateTime",
"propertyType": "Date",
"propertyValue": "0001-01-01T00:00:00Z"
},
{
"propertyName": "lastModifiedBy",
"propertyType": "Null",
"propertyValue": null
},
{
"propertyName": "@odata.type",
"propertyType": "String",
"propertyValue": "#microsoft.graph.plannerChecklistItem"
}
]
}
]
},
{
"propertyName": "@odata.context",
"propertyType": "String",
"propertyValue": "https://graph.microsoft.com/v1.0/$metadata#planner/tasks('2B69MwLkekuzBBu38EcaDpcANQ0l')/details/$entity"
},
{
"propertyName": "@odata.etag",
"propertyType": "String",
"propertyValue": "W/\"JzEtVGFza0RldGFpbHMgQEBAQEBAQEBAQEBAQEBARCc=\""
}
]
From there, you can pick out and loop through your property names and pull the relevant data from the original structure.
XML
You can convert it to XML and do it that way, but properties that are numeric by name don't always convert how you want them to and you then have to do the work to get the right outcome.
It turns this property name 14037 into this element name _x0031_4037 in XML. The reason is due to rules governing the first character of any element name in an XML structure, it shouldn't start with a number.
I can show you that solution as well if you want but if any dynamic property names aren't numeric, you'll need to do the work to determine the fork in logic to handle that.
That data converted to XML looks like this ...
<root odata.context="https://graph.microsoft.com/v1.0/$metadata#planner/tasks('2B69MwLkekuzBBu38EcaDpcANQ0l')/details/$entity" odata.etag="W/"JzEtVGFza0RldGFpbHMgQEBAQEBAQEBAQEBAQEBARCc="">
<id>2B69MwLkekuzBBu38EcaDpcANQ0l
</id>
<description>
</description>
<previewType>checklist
</previewType>
<references />
<checklist>
<_x0031_4037 odata.type="#microsoft.graph.plannerChecklistItem">
<isChecked>false
</isChecked>
<title>test1
</title>
<orderHint>8585Fg
</orderHint>
<lastModifiedDateTime>0001-01-01T00:00:00Z
</lastModifiedDateTime>
<lastModifiedBy />
</_x0031_4037>
<_x0033_6310 odata.type="#microsoft.graph.plannerChecklistItem">
<isChecked>false
</isChecked>
<title>test2
</title>
<orderHint>8586002405mV
</orderHint>
<lastModifiedDateTime>0001-01-01T00:00:00Z
</lastModifiedDateTime>
<lastModifiedBy />
</_x0033_6310>
<_x0034_5221 odata.type="#microsoft.graph.plannerChecklistItem">
<isChecked>false
</isChecked>
<title>test3
</title>
<orderHint>8586002406569253572P.
</orderHint>
<lastModifiedDateTime>0001-01-01T00:00:00Z
</lastModifiedDateTime>
<lastModifiedBy />
</_x0034_5221>
<_x0036_1052 odata.type="#microsoft.graph.plannerChecklistItem">
<isChecked>false
</isChecked>
<title>test4
</title>
<orderHint>8585\Y
</orderHint>
<lastModifiedDateTime>0001-01-01T00:00:00Z
</lastModifiedDateTime>
<lastModifiedBy />
</_x0036_1052>
</checklist>
</root>
With that, we can use some operations to get the names of the elements, this is a flow that demonstrates the approach ...
Initialize Data (Initialize Variable)
That's your data as JSON.
Initialize Data w. Root Property (Initialize Variable)
In order to get the JSON to convert to XML successfully, we need to add a root property. The type of variable this is is Object.
This is the expression to do that ...
@{json(concat('{ "root": ', variables('Data'), '}'))}
Compose XML As Data (Compose)
This is simple, the expression is ...
@{xml(variables('Data w. Root Property'))}
Compose Checklist Elements (Compose)
We now want to get all of the elements out below the <checklist> node, to do that, we need to run an Xpath query over the top of it, this is the expression ...
@{xpath(outputs('Compose_Data_As_XML'), '//checklist/*')}
That gives us this back ...
[
{
"$content-type": "application/xml;charset=utf-8",
"$content": "PF94MDAzMV80MDM3IG9kYXRhLnR5cGU9IiNtaWNyb3NvZnQuZ3JhcGgucGxhbm5lckNoZWNrbGlzdEl0ZW0iPg0KICA8aXNDaGVja2VkPmZhbHNlPC9pc0NoZWNrZWQ+DQogIDx0aXRsZT50ZXN0MTwvdGl0bGU+DQogIDxvcmRlckhpbnQ+ODU4NUZnPC9vcmRlckhpbnQ+DQogIDxsYXN0TW9kaWZpZWREYXRlVGltZT4wMDAxLTAxLTAxVDAwOjAwOjAwWjwvbGFzdE1vZGlmaWVkRGF0ZVRpbWU+DQogIDxsYXN0TW9kaWZpZWRCeSAvPg0KPC9feDAwMzFfNDAzNz4="
},
{
"$content-type": "application/xml;charset=utf-8",
"$content": "PF94MDAzM182MzEwIG9kYXRhLnR5cGU9IiNtaWNyb3NvZnQuZ3JhcGgucGxhbm5lckNoZWNrbGlzdEl0ZW0iPg0KICA8aXNDaGVja2VkPmZhbHNlPC9pc0NoZWNrZWQ+DQogIDx0aXRsZT50ZXN0MjwvdGl0bGU+DQogIDxvcmRlckhpbnQ+ODU4NjAwMjQwNW1WPC9vcmRlckhpbnQ+DQogIDxsYXN0TW9kaWZpZWREYXRlVGltZT4wMDAxLTAxLTAxVDAwOjAwOjAwWjwvbGFzdE1vZGlmaWVkRGF0ZVRpbWU+DQogIDxsYXN0TW9kaWZpZWRCeSAvPg0KPC9feDAwMzNfNjMxMD4="
},
{
"$content-type": "application/xml;charset=utf-8",
"$content": "PF94MDAzNF81MjIxIG9kYXRhLnR5cGU9IiNtaWNyb3NvZnQuZ3JhcGgucGxhbm5lckNoZWNrbGlzdEl0ZW0iPg0KICA8aXNDaGVja2VkPmZhbHNlPC9pc0NoZWNrZWQ+DQogIDx0aXRsZT50ZXN0MzwvdGl0bGU+DQogIDxvcmRlckhpbnQ+ODU4NjAwMjQwNjU2OTI1MzU3MlAuPC9vcmRlckhpbnQ+DQogIDxsYXN0TW9kaWZpZWREYXRlVGltZT4wMDAxLTAxLTAxVDAwOjAwOjAwWjwvbGFzdE1vZGlmaWVkRGF0ZVRpbWU+DQogIDxsYXN0TW9kaWZpZWRCeSAvPg0KPC9feDAwMzRfNTIyMT4="
},
{
"$content-type": "application/xml;charset=utf-8",
"$content": "PF94MDAzNl8xMDUyIG9kYXRhLnR5cGU9IiNtaWNyb3NvZnQuZ3JhcGgucGxhbm5lckNoZWNrbGlzdEl0ZW0iPg0KICA8aXNDaGVja2VkPmZhbHNlPC9pc0NoZWNrZWQ+DQogIDx0aXRsZT50ZXN0NDwvdGl0bGU+DQogIDxvcmRlckhpbnQ+ODU4NVxZPC9vcmRlckhpbnQ+DQogIDxsYXN0TW9kaWZpZWREYXRlVGltZT4wMDAxLTAxLTAxVDAwOjAwOjAwWjwvbGFzdE1vZGlmaWVkRGF0ZVRpbWU+DQogIDxsYXN0TW9kaWZpZWRCeSAvPg0KPC9feDAwMzZfMTA1Mj4="
}
]
... not overly useful in that form so the next step does the work to get out that which is needed.
Select Property Names (Select)
This is the ugly part but what it does do is allow you to get what you need without having to loop.
This is the definition of the Select operation ...
... and these are the two expressions that need to be pasted in end on end ...
@{replace(split(xpath(xml(base64ToString(item()['$content'])), 'name(/*)'), '_')[1], 'x003', '')}@{last(split(xpath(xml(base64ToString(item()['$content'])), 'name(/*)'), '_'))}
... or alternatively, this as a single expression ...
replace(replace(xpath(xml(base64ToString(item()['$content'])), 'name(/*)'), '_x003', ''), '_', '')
... like I said, it's a bit ugly but does the job.
This is the result ...
[
{
"PropertyName": "14037"
},
{
"PropertyName": "36310"
},
{
"PropertyName": "45221"
},
{
"PropertyName": "61052"
}
]
Using both of those approaches allows you loop over the property names and do some dynamic extraction of their values from the original JSON data.
- Logic App AuthorizationFailure - vnet integration needed
- Use Salesforce Connectors from Hosted Azure Logic App in VSCode?
- Azure logic apps encode left bracket in json query param names to ~2. How can I rewrite with concat and/or replace functions?
- How to handle null value in logic app condition module
- Add properties to an object in a variable in Azure Logic Apps
- Workflow Validation Failed Error: The function cannot be called from a logic app. It must not have a custom route
- In an AZURE Function how to throw an error to be caught in the Azure Logic AP
- Cannot get AAD auth token in Logic Apps
- Parameterising connections.json in Azure Logic Apps(Standard) for deployment via Azure DevOps
- Logic Apps: How to use create a new watchlist with data (raw content) module
- Add extra attribute to object Logic app
- How to hide or change legend in "Run query and visualize results" logic app function?
- Azure Logic App Authorization Policy set via Bicep
- Azure Event Grid Graph API SharePoint Event info useful?
- Azure Logic App Function call for non json body
- Publish Failure Reporting Add or Build Definition File
- Azure Logic App returns metadata instead of SQL query results when invoked via HTTP trigger
- Azure Logic App is only inserting 100 rows into SQL Server table
- Unable to open the workflow in designer using vs code
- How to Pass SAS Token as Header in Azure Logic App HTTP Trigger?
- Azure Logic Apps with Form Recognizer - Get Array definition in optional settings entry
- logic app ( power automate) extract checklist items from a task from a planner plan
- Power Automate Get Filename if Contains Special Character
- Task AzureAppServiceSetting@1: Application Settings object is not a valid JSON while deploying with YAML file from Azure Devops
- unable to delete or move first step from logic app?
- Azure Logic App HTTP Request Authentication Scope
- Enabling minimum apiVersion to 2021-08-01 in Azure API Management causing saving issues or deployment errors for existing logic apps
- Azure Logic Apps: Set condition to False when SQL query returns no rows of data
- Send alert/Logic apps if Azure ML pipeline Completed with Azure ML Pipeline Outputs attached to email
- How to output URL of Azure Logic Apps with Terraform?