Search for a command to run...
Scalable method for turning objects into arrays
I love backpacking and adventuring, but I am particularly obsessed with exploring the Grand Canyon and its surrounding tributaries.
My hobbies include photography, sports, reading and PC gaming. I also have a love/hate relationship with a vintage Suzuki motorcycle.
Father of two remarkable children and husband to an equally remarkable woman.
"I'll only be this young for one more day"
Thanks for this guide, It's the one complete and helpfull. Could you upload the full code of the solution?
And please, if it is possible, attach a complete layout view of the automation.
Andrea Rebeccato I believe I can do both of those. Give me a few days. Will reply back here, either way.
Added flow source and a diagram link - see here:
https://hamsandwich.hashnode.dev/parsing-custom-planner-labels-with-power-automate#heading-flow-source
Jake Mannion Thank you so much for the code. Anyway I can't see the diagram, because the link address me anytime to this page.
I keep getting this error from the Parse Json step. Any thoughts? 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'for_each_task' referenced by 'inputs' in action 'Parse_JSON_task_request_labels' are not defined in the template.'.'.
very glad to hear that 👍 Jacob Anderson
I got to step 8 but when it came to filtering the array, I selected the right outputs, but I don't have key listed under dynamic content and got stuck there. I went through all the steps again to make sure I did things correctly. Any suggestions?
Hi there.
You'll need to use an expression for both keys - they look like dynamic properties in my screenshot, but they only appear that way after saving and reloading the flow.
Locate the image in step 8, right after the sentence with "...might need to zoom in or click..." The two dark rectangles near the bottom contain the expressions I used.
Jake Mannion This was a great guide for me. Could you please consider expounding on step #8 with some more screenshots or text of the Expressions used? I am quite new to Power Automate and am experiencing unresolvable errors with attempting to implement step 8. Any help here is appreciated.
You bet. Saw your note about a week ago - didn't have a chance to reply until now, but the workflow described in this article is still around.
I'll add a few more screenshots/details soon.
Updated. Please let me know if you have any questions on what I added, Kevin. Those labels can be a tough nut to crack and I hope you get it sorted out!
Create a Table of Contents for Site Page libraries in SharePoint. Easily implement site-wide, via web parts. Supports auto updates and additions.
Easy, practical DIY approach using SharePoint Online lists
Step-by-Step Guide to Linking Form Attachments to Planner Tasks with Power Automate and Logic Apps
Paginated Graph Results with @odata.nextlink in Azure Data Factory
A reasonably sound method to extract unstructured data from Excel worksheets
I'm a fan of Microsoft Planner. It's easy to use, flexible, and the UI/UX is rock solid. The tie-in with O365 Groups and Teams is a strong selling point, as well.
Now, Planner isn't the perfect solution for every scenario (you probably wouldn't want to manage sprints with it) but it is very popular within many companies, large and small.
Microsoft naming conventions don't always make things easy, so:
| Term | Meaning |
| Planner | the name for the overall application/service |
| Plan | an individual task board for a team or person |
| Task | the items within a Plan |
| Bucket | used to group collections of similar tasks |
| Label | a way to tag/classify individual tasks |
Planner can be used in many different ways, but at its core it's all about task management, which makes it a juicy target for automation. The Power Platform connector is pretty robust, and you can extend that even further by hitting the Planner API through Microsoft Graph.
However, any integration efforts quickly expose Planner's underlying data structure, which has some quirks.
Planner boards come with 25 labels to help you and your team classify the individual tasks on the board. Each label has a default name - its associated color - and that name can be customized to provide more meaning and context for the plan users. Cool feature!
Customizations, like label names, are contained at the individual plan level. For example, one team might use red for marking "Urgent" work and a different team may use it to classify something as "Top Secret."
When retrieving Planner task data, any label information comes back as an appliedCategories object:
It's not an array.
There are no name/value pairs.
It's just... booleans with no tie-back to the label names. What does category18 mean? Has it been customized? How can I tell?
Fortunately, you can get a bit of a "glossary" if you retrieve the overall plan data:
That's great, but how do I map those values? How do I bounce a name/value object against an object of booleans?
Different platforms/languages will have their own approach to the challenges above. For Automate, it required some considerable thinking!
At the time this came up, I was working on a solution that iterated over many plans, so I needed a method that scaled. If you are working with data from just an individual plan, there are definitely some corners you could cut.
1.) When I got to the point in my automation where I was working with individual plans, I used the Get plan details action.
2.) Next, I used a Compose statement to craft my array. It took a lot of typing, but copy/paste made it less of an ordeal.
3.) Most teams that use labels within Planner also customize the label names - but it's not an absolute certainty. The Get plan details action only returns customized values. I wanted to be certain I covered both scenarios, so I used a coalesce expression within each node of the array.
The steps above get us halfway there, but we still need to handle the other part of the mapping - the data from each individual task.
4.) Inside of a For each task loop, I started with a Parse JSON step, using the appliedCategories value as the content source.
5.) The schema needs to be created manually (unless you magically have a task with all 25 labels applied, to feed into the generator):
{ "type": "object", "properties": { "category1": { "type": "boolean" }, "category2": { "type": "boolean" }, "category3": { "type": "boolean" }, "category4": { "type": "boolean" }, "category5": { "type": "boolean" }, "category6": { "type": "boolean" }, "category7": { "type": "boolean" }, "category8": { "type": "boolean" }, "category9": { "type": "boolean" }, "category10": { "type": "boolean" }, "category11": { "type": "boolean" }, "category12": { "type": "boolean" }, "category13": { "type": "boolean" }, "category14": { "type": "boolean" }, "category15": { "type": "boolean" }, "category16": { "type": "boolean" }, "category17": { "type": "boolean" }, "category18": { "type": "boolean" }, "category19": { "type": "boolean" }, "category20": { "type": "boolean" }, "category21": { "type": "boolean" }, "category22": { "type": "boolean" }, "category23": { "type": "boolean" }, "category24": { "type": "boolean" }, "category25": { "type": "boolean" } } }
The Parse JSON step is important because it saves you having to write 25 expressions with null-handling logic in the next step.
6.) Next, I used a Compose statement (and a lot of copy/pasting) to create the initial array for the task labels. Note the key/value format matches the "glossary" we created previously.
7.) Then we filter the task label array to remove any nodes containing nulls. Booleans are tricky when filtering arrays in Automate, so switch to advanced mode and use an expression:
@equals(item()?['value'], true)
This yields an array like this:
[ { "key": "category14", "value": true }, { "key": "category18", "value": true }, { "key": "category19", "value": true }, { "key": "category23", "value": true } ]
8.) Now, you've got matching arrays - both sides of the equation! Where you take it from here may vary depending on your solution needs.
I needed each applied label as an individual record, so I looped through the newly-created task label array, filtering against the glossary array we created earlier with each iteration.
A couple people reached out to ask for more details on step #8 recently.
Here are the details on the For Each action and the Filter step just inside. You may need to zoom in or click the picture to see smaller details:
This last part may vary, depending on how you want the label data structured in your flow. I packed each value into an object and rolled those into an array variable using an Append action:
Here is the object, with each expression expanded. I include taskID since it's an effective way to relate child entities in Planner (labels, assignments, etc.) back to the parent:
{
"labelTitle": first(body('filter_plan_labels_with_task_labels'))?['value'],
"taskID": items('for_each_task')?['ID'],
"labelID": int(
substring(
first(body('filter_plan_labels_with_task_labels'))?['key'],
8
)
)
}
The final stage for this workflow gathers up the data collected for the various Planner entities and ships it off to a separate flow for processing and storage.
I hope that helps!
There is a pretty fun story to be told about this particular solution, but I will save that for another article - don't want to get off track here tonight.
This was an interesting puzzle to solve, and the only real drawback was that it involved a fair amount of repetition. But now that it's done, I've got a robust solution that can be scaled considerably.
Some of the upper-echelon Automate junkies might be able to squeeze a little more speed out of this method, but overall it is quite performant. Using Compose actions instead of variables any time you can get away with it definitely helps. Automate handles arrays and array filtering very quickly, too.
If you want to see a mermaid visual of the logic flow, please visit this GitHub Gist.
And here is the (sanitized) source JSON for the flow:
{
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
},
"$authentication": {
"defaultValue": {},
"type": "SecureObject"
},
"bool_emailNotifications (jake_emailNotifications)": {
"defaultValue": true,
"type": "Bool",
"metadata": {
"schemaName": "jake_emailNotifications",
"description": "Set to No if you want to turn off 'process complete' emails"
}
},
"text_sqlServer (jake_sqlServer)": {
"defaultValue": "***SCRUBBED***",
"type": "String",
"metadata": {
"schemaName": "jake_sqlServer"
}
},
"text_sqlDatabase (jake_sqlTable)": {
"defaultValue": "***SCRUBBED***",
"type": "String",
"metadata": {
"schemaName": "jake_sqlTable"
}
},
"text_emailRecipients (jake_emailRecipients)": {
"defaultValue": "***SCRUBBED***",
"type": "String",
"metadata": {
"schemaName": "jake_emailRecipients"
}
},
"text_processFlowEndpoint (jake_test_postURL)": {
"defaultValue": "***SCRUBBED***",
"type": "String",
"metadata": {
"schemaName": "jake_test_postURL",
"description": "endpoint for triggering processing sub flow "
}
}
},
"triggers": {
"Recurrence": {
"recurrence": {
"frequency": "Day",
"interval": 1,
"timeZone": "Pacific Standard Time",
"startTime": "2022-05-01T00:00:00Z",
"schedule": {
"hours": [
"1"
]
}
},
"metadata": {
"operationMetadataId": "48f13d96-ccee-42b8-9de4-d9e92c683b79"
},
"type": "Recurrence"
}
},
"actions": {
"init_assignmentArray": {
"runAfter": {
"init_taskArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "ee36ea65-589b-4cbb-88f4-4d427253f226"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "assignmentArray",
"type": "array"
}
]
}
},
"init_taskObject": {
"runAfter": {
"init_errorCount": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "df6b1fbf-f47b-42a4-ac2e-f6af361e2887"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "taskObject",
"type": "object",
"value": {
"taskID": "",
"planID": "",
"bucketID": "",
"taskTitle": "",
"planTitle": "",
"bucketTitle": "",
"taskDescription": "",
"startDate": "",
"createdDate": "",
"dueDate": "",
"completeDate": "",
"createdByID": "",
"orderHint": "",
"pctComplete": ""
}
}
]
}
},
"init_errorString": {
"runAfter": {
"init_outcomeArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "8b4796a3-4846-4fef-9c94-11860d9767c9"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "errorString",
"type": "string"
}
]
}
},
"init_taskArray": {
"runAfter": {
"init_taskObject": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "242f3f77-c144-4d0e-a9f4-5dd030a23526"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "taskArray",
"type": "array"
}
]
}
},
"init_checklistArray": {
"runAfter": {
"init_labelArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "a8e30162-dc67-40a2-aa2c-5c3d9234b896"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "checklistArray",
"type": "array"
}
]
}
},
"init_labelArray": {
"runAfter": {
"init_labelValuesArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "fb3b0418-82c1-4001-8135-04c7bcde2a7b"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "labelArray",
"type": "array"
}
]
}
},
"init_attachmentArray": {
"runAfter": {
"init_assignmentArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "40513abf-01c1-4699-8681-263ee9e882a4"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "attachmentArray",
"type": "array"
}
]
}
},
"init_labelValuesArray": {
"runAfter": {
"init_attachmentArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "9d17111f-b964-422c-b416-9a3ee0b59349"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "labelValuesArray",
"type": "array"
}
]
},
"description": "used for storing the label info for a given plan - dont think it's used"
},
"Plan_and_task_iteration": {
"actions": {
"for_each_plan": {
"foreach": "@outputs('get_active_plans')?['body/value']",
"actions": {
"List_buckets": {
"runAfter": {},
"metadata": {
"operationMetadataId": "0e8aa5dd-aac0-4784-b418-693f426629a2"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_planner",
"operationId": "ListBuckets_V3",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_planner"
},
"parameters": {
"groupId": "@items('for_each_plan')?['GroupID']",
"id": "@items('for_each_plan')?['PlanID']"
},
"authentication": "@parameters('$authentication')"
}
},
"List_tasks": {
"runAfter": {
"plan_label_values_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "d7847aa4-8952-41c1-8f4e-533c880b946b"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_planner",
"operationId": "ListTasks_V3",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_planner"
},
"parameters": {
"groupId": "@items('for_each_plan')?['GroupID']",
"id": "@items('for_each_plan')?['PlanID']"
},
"authentication": "@parameters('$authentication')"
}
},
"for_each_task": {
"foreach": "@outputs('List_tasks')?['body/value']",
"actions": {
"filter_bucket_array": {
"runAfter": {},
"metadata": {
"operationMetadataId": "bab69193-9ca3-4715-8d61-254f7fa86136"
},
"type": "Query",
"inputs": {
"from": "@outputs('List_buckets')?['body/value']",
"where": "@equals(item()?['id'], items('for_each_task')?['bucketId'])"
}
},
"is_more_task_detail_needed": {
"actions": {
"Get_task_details": {
"runAfter": {},
"metadata": {
"operationMetadataId": "40bd4ba4-1fe8-4566-9c90-783a1a028b88"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_planner",
"operationId": "GetTaskDetails_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_planner"
},
"parameters": {
"id": "@items('for_each_task')?['id']"
},
"authentication": "@parameters('$authentication')"
}
},
"for_each_checklist_item": {
"foreach": "@outputs('Get_task_details')?['body/checklist']",
"actions": {
"append_to_checklist_array": {
"runAfter": {},
"metadata": {
"operationMetadataId": "77340521-feb2-4cf3-8a6d-00f31d7277d1"
},
"type": "AppendToArrayVariable",
"inputs": {
"name": "checklistArray",
"value": {
"itemTitle": "@items('for_each_checklist_item')?['value/title']",
"itemComplete": "@items('for_each_checklist_item')?['value/isChecked']",
"taskID": "@items('for_each_task')?['id']",
"orderHint": "@items('for_each_checklist_item')?['value/orderHint']",
"itemID": "@int( items('for_each_checklist_item')?['id'] )",
"modifiedByID": "@items('for_each_checklist_item')?['value/lastModifiedBy/user/id']",
"modifiedDate": "@items('for_each_checklist_item')?['value/lastModifiedDateTime']"
}
}
}
},
"runAfter": {
"append_taskObject_to_taskArray_detailed": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "491f333b-8f0c-4b00-90c9-f19c10948a6c"
},
"type": "Foreach",
"runtimeConfiguration": {
"concurrency": {
"repetitions": 50
}
}
},
"for_each_attachment": {
"foreach": "@outputs('Get_task_details')?['body/references']",
"actions": {
"append_to_attachmentArray": {
"runAfter": {},
"metadata": {
"operationMetadataId": "23902441-6a89-4dd2-b1eb-6b2068053cb7"
},
"type": "AppendToArrayVariable",
"inputs": {
"name": "attachmentArray",
"value": {
"createdByID": "@items('for_each_attachment')?['value/lastModifiedBy/user/id']",
"attachmentTitle": "@items('for_each_attachment')?['value/alias']",
"attachmentLink": "@items('for_each_attachment')?['resourceLink']",
"taskID": "@items('for_each_task')?['id']"
}
}
}
},
"runAfter": {
"for_each_checklist_item": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "1db02863-5401-4ccb-a543-835d83505745"
},
"type": "Foreach",
"runtimeConfiguration": {
"concurrency": {
"repetitions": 50
}
}
},
"append_taskObject_to_taskArray_detailed": {
"runAfter": {
"Get_task_details": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "8a962b72-e609-496a-b789-05e80f48f3ee"
},
"type": "AppendToArrayVariable",
"inputs": {
"name": "taskArray",
"value": {
"taskID": "@items('for_each_task')?['ID']",
"planID": "@items('for_each_task')?['planId']",
"bucketID": "@items('for_each_task')?['bucketId']",
"taskTitle": "@items('for_each_task')?['title']",
"planTitle": "@items('for_each_plan')?['PlanName']",
"bucketTitle": "@first(body('filter_bucket_array'))?['name']",
"taskDescription": "@outputs('Get_task_details')?['body/description']",
"startDate": "@items('for_each_task')?['startDateTime']",
"createdDate": "@items('for_each_task')?['createdDateTime']",
"dueDate": "@items('for_each_task')?['dueDateTime']",
"completeDate": "@items('for_each_task')?['completedDateTime']",
"createdByID": "@items('for_each_task')?['createdBy/user/id']",
"orderHint": "@items('for_each_task')?['orderHint']",
"pctComplete": "@items('for_each_task')?['percentComplete']"
}
}
}
},
"runAfter": {
"filter_bucket_array": [
"Succeeded"
]
},
"else": {
"actions": {
"append_taskObject_to_taskArray": {
"runAfter": {},
"metadata": {
"operationMetadataId": "8a962b72-e609-496a-b789-05e80f48f3ee"
},
"type": "AppendToArrayVariable",
"inputs": {
"name": "taskArray",
"value": {
"taskID": "@items('for_each_task')?['ID']",
"planID": "@items('for_each_task')?['planId']",
"bucketID": "@items('for_each_task')?['bucketId']",
"taskTitle": "@items('for_each_task')?['title']",
"planTitle": "@items('for_each_plan')?['PlanName']",
"bucketTitle": "@first(\n body('filter_bucket_array')\n )?['name']",
"taskDescription": "",
"startDate": "@items('for_each_task')?['startDateTime']",
"createdDate": "@items('for_each_task')?['createdDateTime']",
"dueDate": "@items('for_each_task')?['dueDateTime']",
"completeDate": "@items('for_each_task')?['completedDateTime']",
"createdByID": "@items('for_each_task')?['createdBy/user/id']",
"orderHint": "@items('for_each_task')?['orderHint']",
"pctComplete": "@items('for_each_task')?['percentComplete']"
}
}
}
}
},
"expression": {
"or": [
{
"equals": [
"@items('for_each_task')?['hasDescription']",
true
]
},
{
"greater": [
"@items('for_each_task')?['referenceCount']",
0
]
},
{
"greater": [
"@items('for_each_task')?['checklistItemCount']",
0
]
}
]
},
"metadata": {
"operationMetadataId": "b8b0c2c6-d713-480e-9669-b824959b2c54"
},
"type": "If",
"description": "if a task has no description, checklist, or attachments, we don't need to run 'Get task details'"
},
"for_each_assignment": {
"foreach": "@items('for_each_task')?['_assignments']",
"actions": {
"append_to_assignmentArray": {
"runAfter": {},
"metadata": {
"operationMetadataId": "1d93b63a-b9e4-4c3b-8667-84e4845b2ed3"
},
"type": "AppendToArrayVariable",
"inputs": {
"name": "assignmentArray",
"value": {
"taskID": "@items('for_each_task')?['id']",
"assignedToID": "@items('for_each_assignment')?['userId']"
}
}
}
},
"runAfter": {
"is_more_task_detail_needed": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "0aacd0d8-1c14-46c3-9ac5-80431deb8100"
},
"type": "Foreach",
"runtimeConfiguration": {
"concurrency": {
"repetitions": 50
}
}
},
"task_labels_object": {
"runAfter": {
"for_each_assignment": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "1bd3d278-4616-405e-a76d-f9f1bb457947"
},
"type": "Compose",
"inputs": "@items('for_each_task')?['appliedCategories']"
},
"Parse_JSON_for_task_labels": {
"runAfter": {
"task_labels_object": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "c7ce52b2-6115-4ad8-81f1-107cc7efed71"
},
"type": "ParseJson",
"inputs": {
"content": "@outputs('task_labels_object')",
"schema": {
"type": "object",
"properties": {
"category1": {
"type": "boolean"
},
"category2": {
"type": "boolean"
},
"category3": {
"type": "boolean"
},
"category4": {
"type": "boolean"
},
"category5": {
"type": "boolean"
},
"category6": {
"type": "boolean"
},
"category7": {
"type": "boolean"
},
"category8": {
"type": "boolean"
},
"category9": {
"type": "boolean"
},
"category10": {
"type": "boolean"
},
"category11": {
"type": "boolean"
},
"category12": {
"type": "boolean"
},
"category13": {
"type": "boolean"
},
"category14": {
"type": "boolean"
},
"category15": {
"type": "boolean"
},
"category16": {
"type": "boolean"
},
"category17": {
"type": "boolean"
},
"category18": {
"type": "boolean"
},
"category19": {
"type": "boolean"
},
"category20": {
"type": "boolean"
},
"category21": {
"type": "boolean"
},
"category22": {
"type": "boolean"
},
"category23": {
"type": "boolean"
},
"category24": {
"type": "boolean"
},
"category25": {
"type": "boolean"
}
}
}
}
},
"task_labels_array": {
"runAfter": {
"Parse_JSON_for_task_labels": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "65d680cc-2ba7-4198-a3ef-007d5841dcf0"
},
"type": "Compose",
"inputs": [
{
"key": "category1",
"value": "@body('Parse_JSON_for_task_labels')?['category1']"
},
{
"key": "category2",
"value": "@body('Parse_JSON_for_task_labels')?['category2']"
},
{
"key": "category3",
"value": "@body('Parse_JSON_for_task_labels')?['category3']"
},
{
"key": "category4",
"value": "@body('Parse_JSON_for_task_labels')?['category4']"
},
{
"key": "category5",
"value": "@body('Parse_JSON_for_task_labels')?['category5']"
},
{
"key": "category6",
"value": "@body('Parse_JSON_for_task_labels')?['category6']"
},
{
"key": "category7",
"value": "@body('Parse_JSON_for_task_labels')?['category7']"
},
{
"key": "category8",
"value": "@body('Parse_JSON_for_task_labels')?['category8']"
},
{
"key": "category9",
"value": "@body('Parse_JSON_for_task_labels')?['category9']"
},
{
"key": "category10",
"value": "@body('Parse_JSON_for_task_labels')?['category10']"
},
{
"key": "category11",
"value": "@body('Parse_JSON_for_task_labels')?['category11']"
},
{
"key": "category12",
"value": "@body('Parse_JSON_for_task_labels')?['category12']"
},
{
"key": "category13",
"value": "@body('Parse_JSON_for_task_labels')?['category13']"
},
{
"key": "category14",
"value": "@body('Parse_JSON_for_task_labels')?['category14']"
},
{
"key": "category15",
"value": "@body('Parse_JSON_for_task_labels')?['category15']"
},
{
"key": "category16",
"value": "@body('Parse_JSON_for_task_labels')?['category16']"
},
{
"key": "category17",
"value": "@body('Parse_JSON_for_task_labels')?['category17']"
},
{
"key": "category18",
"value": "@body('Parse_JSON_for_task_labels')?['category18']"
},
{
"key": "category19",
"value": "@body('Parse_JSON_for_task_labels')?['category19']"
},
{
"key": "category20",
"value": "@body('Parse_JSON_for_task_labels')?['category20']"
},
{
"key": "category21",
"value": "@body('Parse_JSON_for_task_labels')?['category21']"
},
{
"key": "category22",
"value": "@body('Parse_JSON_for_task_labels')?['category22']"
},
{
"key": "category23",
"value": "@body('Parse_JSON_for_task_labels')?['category23']"
},
{
"key": "category24",
"value": "@body('Parse_JSON_for_task_labels')?['category24']"
},
{
"key": "category25",
"value": "@body('Parse_JSON_for_task_labels')?['category25']"
}
]
},
"for_each_task_label": {
"foreach": "@body('filter_task_labels_array')",
"actions": {
"filter_plan_labels_with_task_labels": {
"runAfter": {},
"metadata": {
"operationMetadataId": "a9bad329-fa19-41ff-8533-527fdba98367"
},
"type": "Query",
"inputs": {
"from": "@outputs('plan_label_values_array')",
"where": "@equals(item()?['key'], items('for_each_task_label')?['key'])"
}
},
"append_to_labelArray": {
"runAfter": {
"filter_plan_labels_with_task_labels": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "4b7a974e-2343-4abc-b9fe-ca9ce06465fc"
},
"type": "AppendToArrayVariable",
"inputs": {
"name": "labelArray",
"value": {
"labelTitle": "@first(body('filter_plan_labels_with_task_labels'))?['value']",
"taskID": "@items('for_each_task')?['ID']",
"labelID": "@int(\r\n substring(\r\n first(body('filter_plan_labels_with_task_labels'))?['key'],\r\n 8\r\n )\r\n)"
}
}
}
},
"runAfter": {
"filter_task_labels_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "503bf544-b0e9-433c-82f5-12ffaedbc419"
},
"type": "Foreach",
"runtimeConfiguration": {
"concurrency": {
"repetitions": 50
}
}
},
"filter_task_labels_array": {
"runAfter": {
"task_labels_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "ab454f83-d0f0-4956-9311-40840ab8ca9e"
},
"type": "Query",
"inputs": {
"from": "@outputs('task_labels_array')",
"where": "@equals(item()?['value'], true)"
}
},
"if_label_25_support_is_true": {
"actions": {
"if_task_not_complete": {
"actions": {
"link_to_this_task": {
"runAfter": {},
"metadata": {
"operationMetadataId": "559bc95a-b489-45af-8d41-5b6e96875fa7"
},
"type": "Compose",
"inputs": "<a href=\"https://tasks.office.com/***SCRUBBED***/Home/Task/@{outputs('Get_task_details')?['body/id']}\">@{items('for_each_task')?['title']}</a>"
},
"Post_message_in_a_chat_or_channel": {
"runAfter": {
"link_to_this_task": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "e6abb82c-7b65-42e2-99b6-19cc68093c5c"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_teams_1",
"operationId": "PostMessageToConversation",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_teams"
},
"parameters": {
"poster": "Flow bot",
"location": "Channel",
"body/recipient/groupId": "***SCRUBBED***",
"body/recipient/channelId": "***SCRUBBED***",
"body/messageBody": "<p>There has been a request for help from a task on the @{items('for_each_plan')?['PlanName']} (@{items('for_each_plan')?['Team']}) board in Planner.<br>\n<br>\n<span style=\"font-size: 16px\"></span><span style=\"font-size: 16px\">@{outputs('link_to_this_task')}</span><span style=\"font-size: 16px\"></span></p>"
},
"authentication": "@parameters('$authentication')"
}
},
"log_help_request": {
"runAfter": {
"Post_message_in_a_chat_or_channel": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "57d990aa-2533-4875-a43a-032328619dca"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "PostItem_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "***SCRUBBED***",
"database": "***SCRUBBED***",
"table": "***SCRUBBED***",
"item/workflowType": "taskRetrieve",
"item/outcome": "Support Request",
"item/details": "Task: @{items('for_each_task')?['title']}\nPlan: @{items('for_each_plan')?['PlanName']}\nTeam: @{items('for_each_plan')?['Team']}",
"item/startTime": "@body('Start_time_PST')"
},
"authentication": "@parameters('$authentication')"
}
}
},
"runAfter": {},
"expression": {
"not": {
"equals": [
"@items('for_each_task')?['percentComplete']",
100
]
}
},
"metadata": {
"operationMetadataId": "97078d0f-d82e-4382-9501-5f5da3d914d6"
},
"type": "If"
}
},
"runAfter": {
"for_each_task_label": [
"Succeeded"
]
},
"expression": {
"equals": [
"@body('Parse_JSON_for_task_labels')?['category25']",
"@true"
]
},
"metadata": {
"operationMetadataId": "d9694eb5-44b2-4dfc-87aa-445050326d1c"
},
"type": "If"
}
},
"runAfter": {
"List_tasks": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "c62e1965-b11e-4ba9-bd86-d226d0c6aa8b"
},
"type": "Foreach",
"runtimeConfiguration": {
"concurrency": {
"repetitions": 50
}
}
},
"plan_label_values_array": {
"runAfter": {
"Get_plan_details": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "6d9990c3-257a-44a3-b7dc-4a290f5d9bc5"
},
"type": "Compose",
"inputs": [
{
"key": "category1",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category1'],\r\n 'Pink'\r\n)"
},
{
"key": "category2",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category2'],\r\n 'Red'\r\n)"
},
{
"key": "category3",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category3'],\r\n 'Yellow'\r\n)"
},
{
"key": "category4",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category4'],\r\n 'Green'\r\n)"
},
{
"key": "category5",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category5'],\r\n 'Blue'\r\n)"
},
{
"key": "category6",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category6'],\r\n 'Purple'\r\n)"
},
{
"key": "category7",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category7'],\r\n 'Bronze'\r\n)"
},
{
"key": "category8",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category8'],\r\n 'Lime'\r\n)"
},
{
"key": "category9",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category9'],\r\n 'Aqua'\r\n)"
},
{
"key": "category10",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category10'],\r\n 'Gray'\r\n)"
},
{
"key": "category11",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category11'],\r\n 'Silver'\r\n)"
},
{
"key": "category12",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category12'],\r\n 'Brown'\r\n)"
},
{
"key": "category13",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category13'],\r\n 'Cranberry'\r\n)"
},
{
"key": "category14",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category14'],\r\n 'Orange'\r\n)"
},
{
"key": "category15",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category15'],\r\n 'Peach'\r\n)"
},
{
"key": "category16",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category16'],\r\n 'Marigold'\r\n)"
},
{
"key": "category17",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category17'],\r\n 'Light green'\r\n)"
},
{
"key": "category18",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category18'],\r\n 'Dark green'\r\n)"
},
{
"key": "category19",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category19'],\r\n 'Teal'\r\n)"
},
{
"key": "category20",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category20'],\r\n 'Light blue'\r\n)"
},
{
"key": "category21",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category21'],\r\n 'Dark blue'\r\n)"
},
{
"key": "category22",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category22'],\r\n 'Lavender'\r\n)"
},
{
"key": "category23",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category23'],\r\n 'Plum'\r\n)"
},
{
"key": "category24",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category24'],\r\n 'Light gray'\r\n)"
},
{
"key": "category25",
"value": "@coalesce(\r\n outputs('Get_plan_details')?['body/categoryDescriptions/category25'],\r\n 'Dark gray'\r\n)"
}
]
},
"Get_plan_details": {
"runAfter": {
"List_buckets": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "0dc5fdf6-2fa2-48ae-a4e5-020dec34cb88"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_planner",
"operationId": "GetPlanDetails",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_planner"
},
"parameters": {
"id": "@items('for_each_plan')?['PlanID']"
},
"authentication": "@parameters('$authentication')"
}
},
"Append_to_errorString_2": {
"runAfter": {
"List_buckets": [
"Failed"
]
},
"metadata": {
"operationMetadataId": "62a6b5ae-3e2e-490c-9f7e-932225197b4e"
},
"type": "AppendToStringVariable",
"inputs": {
"name": "errorString",
"value": "Failed to get Planner data (buckets)\n@{items('for_each_plan')?['PlanName']} | @{items('for_each_plan')?['PlanID']} | @{items('for_each_plan')?['GroupID']} | @{items('for_each_plan')?['GroupName']} | @{items('for_each_plan')?['InsertedOn']} | @{items('for_each_plan')?['Team']} | @{items('for_each_plan')?['Leader']} | @{items('for_each_plan')?['LastRunDate']}<br>"
},
"description": "if service account does not have plan access, an error will generate"
},
"increment_errorCount_2": {
"runAfter": {
"Append_to_errorString_2": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "dab9642e-300b-4202-9be7-323e2f874e11"
},
"type": "IncrementVariable",
"inputs": {
"name": "errorCount",
"value": 1
}
},
"update_lastRunDate_on_source_row": {
"runAfter": {
"for_each_task": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "9288db35-967c-4ad8-8010-55fe7fef0234"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "PatchItem_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"id": "@items('for_each_plan')?['ID']",
"item/LastRunDate": "@body('Start_time_PST')"
},
"authentication": "@parameters('$authentication')"
}
}
},
"runAfter": {
"get_active_plans": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "4795519f-7313-40f8-9956-4b55cd3d8164"
},
"type": "Foreach",
"runtimeConfiguration": {
"concurrency": {
"repetitions": 50
}
}
},
"get_active_plans": {
"runAfter": {},
"metadata": {
"operationMetadataId": "41fba7a5-dec9-4cf3-b9c9-2ab3af9320f1"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "GetItems_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"$filter": "Active eq 1"
},
"authentication": "@parameters('$authentication')"
},
"description": "get active plans from SQL table (populated and managed by customer via powerApp)"
},
"increment_errorCount": {
"runAfter": {
"get_active_plans": [
"Failed",
"TimedOut"
]
},
"metadata": {
"operationMetadataId": "5fe7081a-63d9-4a0c-86d9-23e0e70d964a"
},
"type": "IncrementVariable",
"inputs": {
"name": "errorCount",
"value": 1
}
},
"append_errorString": {
"runAfter": {
"increment_errorCount": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "4331ac95-7f3e-447a-8a1f-c5510d0c2ca8"
},
"type": "AppendToStringVariable",
"inputs": {
"name": "errorString",
"value": "Failed to get active plans<br>"
}
}
},
"runAfter": {
"init_recordCounts": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "97ae9def-14eb-4ffe-95f7-2f5e5ec0b360"
},
"type": "Scope"
},
"check_for_email_notification_preference": {
"actions": {
"send_completion_email_lite": {
"runAfter": {},
"metadata": {
"operationMetadataId": "b340dff7-7d1b-4081-ab75-dbe9dc7180d1"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_office365_1",
"operationId": "SendEmailV2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365"
},
"parameters": {
"emailMessage/To": "@parameters('text_emailRecipients (jake_emailRecipients)')",
"emailMessage/Subject": "Task Aggegation Complete - @{div(sub(ticks(body('End_Time_PST')), ticks(body('Start_time_PST'))), 600000000)} minutes - @{variables('errorCount')} errors",
"emailMessage/Body": "<p>@{variables('recordCounts')} </p>",
"emailMessage/Importance": "Normal"
},
"authentication": "@parameters('$authentication')"
}
}
},
"runAfter": {
"http_response": [
"TimedOut"
]
},
"expression": {
"equals": [
"@parameters('bool_emailNotifications (jake_emailNotifications)')",
true
]
},
"metadata": {
"operationMetadataId": "05ae3102-3d82-424e-ad05-b2dfc05f743f"
},
"type": "If"
},
"init_errorCount": {
"runAfter": {
"init_errorString": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "31631ada-9b18-4f95-9e4e-660597cb8d1d"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "errorCount",
"type": "integer",
"value": 0
}
]
}
},
"Start_time_PST": {
"runAfter": {},
"metadata": {
"operationMetadataId": "ccf5cf73-8dfa-4d3b-9b80-df852045c97e"
},
"type": "Expression",
"kind": "ConvertTimeZone",
"inputs": {
"baseTime": "@{utcNow()}",
"formatString": "o",
"sourceTimeZone": "UTC",
"destinationTimeZone": "Pacific Standard Time"
}
},
"get_existing_SQL_records": {
"actions": {
"get_existing_tasks": {
"runAfter": {},
"metadata": {
"operationMetadataId": "076b756a-9716-4a44-87ec-2582a1f0b7e8"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "GetItems_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"$filter": "isActive eq 1"
},
"authentication": "@parameters('$authentication')"
},
"runtimeConfiguration": {
"paginationPolicy": {
"minimumItemCount": 25000
}
}
},
"get_existing_assignments": {
"runAfter": {
"get_existing_tasks": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "076b756a-9716-4a44-87ec-2582a1f0b7e8"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "GetItems_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"$filter": "isActive eq 1"
},
"authentication": "@parameters('$authentication')"
},
"runtimeConfiguration": {
"paginationPolicy": {
"minimumItemCount": 25000
}
}
},
"get_existing_attachments": {
"runAfter": {
"get_existing_assignments": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "076b756a-9716-4a44-87ec-2582a1f0b7e8"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "GetItems_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"$filter": "isActive eq 1"
},
"authentication": "@parameters('$authentication')"
},
"runtimeConfiguration": {
"paginationPolicy": {
"minimumItemCount": 25000
}
}
},
"get_existing_checklist_items": {
"runAfter": {
"get_existing_attachments": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "076b756a-9716-4a44-87ec-2582a1f0b7e8"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "GetItems_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"$filter": "isActive eq 1"
},
"authentication": "@parameters('$authentication')"
},
"runtimeConfiguration": {
"paginationPolicy": {
"minimumItemCount": 25000
}
}
},
"get_existing_labels": {
"runAfter": {
"get_existing_checklist_items": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "076b756a-9716-4a44-87ec-2582a1f0b7e8"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "GetItems_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "@parameters('text_sqlServer (jake_sqlServer)')",
"database": "@parameters('text_sqlDatabase (jake_sqlTable)')",
"table": "***SCRUBBED***",
"$filter": "isActive eq 1"
},
"authentication": "@parameters('$authentication')"
},
"runtimeConfiguration": {
"paginationPolicy": {
"minimumItemCount": 25000
}
}
}
},
"runAfter": {
"init_checklistArray": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "0a15e51f-ea62-4662-9ec6-64805e8c3d3e"
},
"type": "Scope"
},
"init_recordCounts": {
"runAfter": {
"get_existing_SQL_records": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "18ee4e3e-b4c7-4071-97b4-0bed010e698b"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "recordCounts",
"type": "string",
"value": "<h3>EXISTING SQL RECORDS</h3>\n<ul>\n<li>Tasks: @{length( outputs('get_existing_tasks')?['body/value'] )}</li>\n<li>Assignments: @{length( outputs('get_existing_assignments')?['body/value'] )}</li>\n<li>Attachments: @{length( outputs('get_existing_attachments')?['body/value'] )}</li>\n<li>Checklist Items: @{length( outputs('get_existing_checklist_items')?['body/value'] )}</li>\n<li>Labels: @{length( outputs('get_existing_labels')?['body/value'] )}</li>\n</ul>"
}
]
}
},
"init_outcomeArray": {
"runAfter": {
"init_inactiveChecklist_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "ac0964c2-d3e3-4baa-a5a7-d611751a2567"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "outcomeArray",
"type": "array"
}
]
}
},
"init_inactiveTasks_array": {
"runAfter": {
"if_weekday": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "81057d90-262d-409e-87fc-5d3ddaf9d56f"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "inactiveTasks",
"type": "array"
}
]
}
},
"init_inactiveLabels_array": {
"runAfter": {
"init_inactiveAssignments_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "2b6ede09-4521-4637-b8cd-559e7d298517"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "inactiveLabels",
"type": "array"
}
]
}
},
"init_inactiveAttachments_array": {
"runAfter": {
"init_inactiveTasks_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "822732d7-f448-4df5-a865-e10ca2898905"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "inactiveAttachments",
"type": "array"
}
]
}
},
"init_inactiveAssignments_array": {
"runAfter": {
"init_inactiveAttachments_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "0e5642fd-169e-4d2f-acc7-36fc12415e49"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "inactiveAssignments",
"type": "array"
}
]
}
},
"init_inactiveChecklist_array": {
"runAfter": {
"init_inactiveLabels_array": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "5bb90e84-300c-4a53-a9c4-a2440bc7452c"
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "inactiveChecklist",
"type": "array"
}
]
}
},
"HTTP": {
"runAfter": {
"array_payloads": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "062aff9d-f992-4b7d-b23a-3bd6cdfc8ee1"
},
"type": "Http",
"inputs": {
"method": "POST",
"uri": "@parameters('text_processFlowEndpoint (jake_test_postURL)')",
"headers": {
"Content-Type": "application/json"
},
"body": "@outputs('array_payloads')"
},
"operationOptions": "DisableAsyncPattern"
},
"http_response": {
"runAfter": {
"HTTP": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "fbe15233-84cb-4618-91cb-c350236a79ec"
},
"type": "Compose",
"inputs": "@{outputs('HTTP')['statusCode']}\n@{body('HTTP')}"
},
"End_Time_PST": {
"runAfter": {
"Plan_and_task_iteration": [
"Succeeded",
"Failed"
]
},
"metadata": {
"operationMetadataId": "844d3531-4728-412e-855f-690ba1bd04f5"
},
"type": "Expression",
"kind": "ConvertTimeZone",
"inputs": {
"baseTime": "@{utcNow()}",
"formatString": "o",
"sourceTimeZone": "UTC",
"destinationTimeZone": "Pacific Standard Time"
}
},
"compose_totals": {
"runAfter": {
"End_Time_PST": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "33b358a6-a5d6-4f25-aef4-4714c2e8e736"
},
"type": "Compose",
"inputs": "<h2>Gather Task Data</h2>\n<h3>@{length(\noutputs('get_active_plans')?['body/value']\n)} Plans</h3>\n<ul><li>Tasks: @{length(\n variables('taskArray')\n)}</li></ul>\n<ul><li>Assignments: @{length(\n variables('assignmentArray')\n)}</li></ul>\n<ul><li>Attachments: @{length(\n variables('attachmentArray')\n)}</li></ul>\n<ul><li>Checklist items: @{length(\n variables('checklistArray')\n)}</li></ul>\n<ul><li>Labels: @{length(variables('labelArray'))}</li></ul>\n\n<h3>ERROR COUNT</h3>\n<p>@{variables('errorCount')}</p>\n\n<h3>DEBUG LOG</h3>\n<p>@{variables('errorString')}</p>"
},
"array_payloads": {
"runAfter": {
"compose_totals": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "b459749d-99fd-45a0-90b5-91fa046a813a"
},
"type": "Compose",
"inputs": {
"parentFlowDuration": "@div(sub(ticks(body('End_Time_PST')), ticks(body('Start_time_PST'))), 600000000)",
"parentFlowMessage": "@{outputs('compose_totals')}",
"payloads": [
{
"contentType": "task",
"values": {
"arraySQL": "@outputs('get_existing_tasks')?['body/value']",
"arrayCurrent": "@variables('taskArray')"
}
},
{
"contentType": "checklist",
"values": {
"arraySQL": "@outputs('get_existing_checklist_items')?['body/value']",
"arrayCurrent": "@variables('checklistArray')"
}
},
{
"contentType": "label",
"values": {
"arraySQL": "@outputs('get_existing_labels')?['body/value']",
"arrayCurrent": "@variables('labelArray')"
}
},
{
"contentType": "attachment",
"values": {
"arraySQL": "@outputs('get_existing_attachments')?['body/value']",
"arrayCurrent": "@variables('attachmentArray')"
}
},
{
"contentType": "assignment",
"values": {
"arraySQL": "@outputs('get_existing_assignments')?['body/value']",
"arrayCurrent": "@variables('assignmentArray')"
}
}
]
}
},
"log_outcome": {
"runAfter": {
"http_response": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "57d990aa-2533-4875-a43a-032328619dca"
},
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_sql",
"operationId": "PostItem_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
},
"parameters": {
"server": "***SCRUBBED***",
"database": "***SCRUBBED***",
"table": "***SCRUBBED***",
"item/workflowType": "taskRetrieve",
"item/outcome": "@if(\r\n greater(\r\n variables('errorCount'),\r\n 0\r\n ),\r\n 'Complete with Error',\r\n 'Success'\r\n)",
"item/itemCount": "@add(\r\n add(\r\n length(variables('assignmentArray')),\r\n length(variables('checklistArray'))\r\n ),\r\n add(\r\n add(\r\n length(variables('labelArray')),\r\n length(outputs('get_active_plans')?['body/value'])\r\n ),\r\n add(\r\n length(variables('taskArray')),\r\n length(variables('attachmentArray'))\r\n )\r\n )\r\n)",
"item/errorCount": "@variables('errorCount')",
"item/details": "Start: @{body('Start_time_PST')} | Runtime: @{div(\r\n sub(\r\n ticks(body('End_Time_PST')),\r\n ticks(body('Start_time_PST'))\r\n ),\r\n 600000000\r\n)\r\n\r\n} minutes\n@{length(outputs('get_active_plans')?['body/value'])} pln | @{length(variables('assignmentArray'))} asn | @{length(variables('attachmentArray'))} atc | @{length(variables('checklistArray'))} chk | @{length(variables('labelArray'))} lbl | @{length(variables('taskArray'))}tsk@{variables('errorString')}",
"item/startTime": "@body('Start_time_PST')"
},
"authentication": "@parameters('$authentication')"
}
},
"if_weekday": {
"actions": {},
"runAfter": {
"Start_time_PST": [
"Succeeded"
]
},
"else": {
"actions": {
"Terminate": {
"runAfter": {},
"metadata": {
"operationMetadataId": "2309e5bb-9878-4721-8a1b-273d5e86b574"
},
"type": "Terminate",
"inputs": {
"runStatus": "Cancelled"
}
}
}
},
"expression": {
"and": [
{
"less": [
"@dayOfWeek(body('Start_time_PST'))",
6
]
},
{
"greater": [
"@dayOfWeek(body('Start_time_PST'))",
0
]
}
]
},
"metadata": {
"operationMetadataId": "9fafc0e2-9c45-40f1-b824-ee5cf3561784"
},
"type": "If"
}
},
"description": "Fetches all active plans and enumerates tasks, assignments, checklists, assignees, and labels. Aggregates those data points and gets them into Azure SQL."
}