{"id":1184,"date":"2024-11-11T11:00:00","date_gmt":"2024-11-11T16:00:00","guid":{"rendered":"https:\/\/automatethemundane.com\/index.php\/2024\/11\/11\/updating-dataverse-records-and-business-process-flows-bpf-with-copilot-studio\/"},"modified":"2024-11-12T08:08:40","modified_gmt":"2024-11-12T13:08:40","slug":"updating-dataverse-records-and-business-process-flows-bpf-with-copilot-studio","status":"publish","type":"post","link":"https:\/\/automatethemundane.com\/index.php\/2024\/11\/11\/updating-dataverse-records-and-business-process-flows-bpf-with-copilot-studio\/","title":{"rendered":"Updating Dataverse Records and Business Process Flows (BPF) with Copilot Studio"},"content":{"rendered":"\n

In a recent project, I was challenged to try and build a Copilot in Copilot Studio that would update a Dataverse table AND update a Business Process Flow (BPF). On its face, its not too crazy. These are classic components of the Power Platform. However, after a few days, it turned into quite the endeavor. One thing that kept sticking for me was I wanted any flows\/topics\/actions connected with the Co Pilot to be dynamic in nature so it could be used over and over. This will be a LONG post as it requires quite a few setup and configuration items, but it is well worth running through it.<\/p>\n\n\n\n

 <\/p>\n\n\n\n

The example will be that of a somebody who manages a 3D Printing business (I love 3D printing) via Dataverse and Model Driven apps. The co pilot will allow the user to get the status of a print job from the database and update its associated BPF. <\/p>\n\n\n\n

 <\/p>\n\n\n\n

Initial Configuration<\/h1>\n\n\n\n

Dataverse Table<\/h2>\n\n\n\n
\n---\ntitle: Project ERD\n---\nerDiagram\n Prints }| -- || Contacts :contains\n Prints }| -- |{ PrintJobsBPF:contains\n PrintJobsBPF{\n int EntraID PK\n }\n Contacts{\n int Person PK\n }\n Prints {\n string PrintJob PK\n lookup Requestor FK\n lookup BPF FK\n string PrintItem\n int Quanity\n boolean PrintCompleted\n date DateRequested\n date DateStarted\n date DateCompleted\n }\n<\/div>\n\n\n\n

 <\/p>\n\n\n\n

BPF<\/h2>\n\n\n\n
\nflowchart LR\n A(Request) --> B(Printing)\n B --> C(Completed)\n<\/div>\n\n\n\n

 <\/p>\n\n\n\n

 <\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Keys<\/h2>\n\n\n\n

Create a key for the print job column to ensure no names are unique<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Views<\/h2>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

Form<\/h2>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

App<\/h2>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

Classic Workflows<\/h2>\n\n\n\n

I use classic workflows when I manage BPF automatically moving between stages. It can be done with Power Automate, but I find this much easier. <\/p>\n\n\n\n

 <\/p>\n\n\n\n

Move BPF from Request to Printing<\/h3>\n\n\n\n
    \n
  1. Create a new Classic workflow that is attached to the BPF table\n
    \"\"\/<\/figure>\n<\/li>\n\n\n\n
  2. Set the conditions to be the based on the related table (Prints), then set the date requested and created on to contains data. Also, set the Active stage to equals Request\n
    \"\"\/<\/figure>\n<\/li>\n\n\n\n
  3. Add an Update step for the BPF table, then select \u201cSet Properties\u201d\n
    \"\"\/<\/figure>\n<\/li>\n\n\n\n
  4. Set the Active Stage to Printing\n
    \"\"\/<\/figure>\n<\/li>\n\n\n\n
  5. Then convert the workflow to a real time workflow\n
    \"\"\/<\/figure>\n<\/li>\n\n\n\n
  6. Save and Activate the Workflow<\/li>\n<\/ol>\n\n\n\n

    With the workflow in place, the BPF will move forward shortly after the record is created. <\/p>\n\n\n\n

    Move BPF from Printing to Completed<\/h3>\n\n\n\n
      \n
    1. As with the step before, create a new workflow background workflow\n
      \"\"\/<\/figure>\n<\/li>\n\n\n\n
    2. Set the condition to be the same as the BPF stage itself and the active stage to Printing\n
      \"\"\/<\/figure>\n<\/li>\n\n\n\n
    3. App an update step to put the BPF in the completed Stage status to Inactive\n
      \"\"\/<\/figure>\n<\/li>\n\n\n\n
    4. Then, add another update stage that updates the Prints table Status column to Inactive\n
      \"\"\/<\/figure>\n<\/li>\n\n\n\n
    5. Update the Starts when to Process changes\n
      \"\"\/<\/figure>\n<\/li>\n\n\n\n
    6. Using the Prints table, set the Update if Date Completed\n
      \"\"\/<\/figure>\n<\/li>\n\n\n\n
    7. Save and Active the workflow<\/li>\n<\/ol>\n\n\n\n

      Copilot Studio <\/h2>\n\n\n\n

      Setup<\/h2>\n\n\n\n

      When building out this Copilot, I tried using straight generative AI with knowledge, Topics connected to flows, and actions connected with flows. I wanted the ability for a user to query a job, get the BPF stage and its steps, then update it. I landed on using topics with power automate flows and generative AI enabled. <\/p>\n\n\n\n

      Knowledge<\/h2>\n\n\n\n

      I built out two knowledge sources, one for just the prints table and one for the BPF table and its associated tables. <\/p>\n\n\n\n

      \"\"\/<\/figure>\n\n\n\n
      \"\"\/<\/figure>\n\n\n\n
      \"\"\/<\/figure>\n\n\n\n

      Topic 1 - Get BPF info<\/h2>\n\n\n\n

      The first topic will reach out to the Prints table, gets associated BPF record, and give the information back to the user. <\/p>\n\n\n\n

        \n
      1. The trigger will be from copilot\n
        \"\"\/<\/figure>\n<\/li>\n\n\n\n
      2. The user is then asked for the name of the print job. The output is saved in variable \u201cvarResponsePrintjobname\u201d\n
        \"\"\/<\/figure>\n<\/li>\n\n\n\n
      3. A message is sent to the user letting them know the action is running\n
        \"\"\/<\/figure>\n<\/li>\n\n\n\n
      4. A Power Automate flow is then executed. I will show how to build this next.\n
        \"\"\/<\/figure>\n<\/li>\n\n\n\n
      5. Three global variables are set from the output of the flows. I chose to do this instead of saving the topic variable as global because I find responses in the chat are \u201cbetter\u201d if they are Topic only. <\/li>\n<\/ol>\n\n\n\n

         <\/p>\n\n\n\n

        \"\"\/<\/figure>\n\n\n\n

        Power Automate flow - Get BPF Information for Copilot<\/h3>\n\n\n\n
          \n
        1. Create a Power Automate workflow called Get BPF Information for Copilot with a Copilot trigger. <\/li>\n\n\n\n
        2. Add a text input called Print Job Name\n
          \"\"\/<\/figure>\n<\/li>\n\n\n\n
        3. Add four variables, two string variables, and two array variables\n\n
          \"\"\/<\/figure>\n\n\n\n
            \n
          1. varEntityLogicalName - String<\/li>\n\n\n\n
          2. varCurrentStageName - String<\/li>\n\n\n\n
          3. varStagesArray - Array<\/li>\n\n\n\n
          4. varStepArray - Array<\/li>\n<\/ol>\n<\/li>\n\n\n\n
          5. Using a List Rows filter, the table using FetchXML ( I find it easier)\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            <fetch>\n  <entity name=\"andy_prints\">\n    <filter>\n      <condition attribute=\"andy_printjob\" operator=\"eq\" value=\"\" \/>\n    <\/filter>\n  <\/entity>\n<\/fetch><\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          6. Using another list rows, do a FetchXML to find the BPF row based on the associated Print row\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            outputs('List_rows_-_Prints')?['body\/value']?[0]?['andy_printsid']<\/code><\/pre>\n\n\n\n
             <\/p>\n\n\n\n
            \ud83d\udca1 The next part should be built out as a child flow for production purposes so it can be reused. But for this demo i put it all into one flow<\/p>\n<\/li>\n\n\n\n
          7. Create vars<\/li>\n\n\n\n
          8. Create another Scope called \u201cScope - Process Stage\u201d<\/li>\n\n\n\n
          9. Within the Scope there will be two actions<\/li>\n\n\n\n
          10. The First Action \u201cGet a row by ID - Process Stages\u201d, will use the outputs of the List rows - BPF to get the process stages of the BPF\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            outputs('List_rows_-_BPF')?['body']?['value'][0]?['_activestageid_value']<\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          11. The second will compose the active stage display name\n\n
            \"\"\/<\/figure>\n\n\n\n
            \ud83d\udca1 This is where the complications of BPF stages and steps come into play. To get the steps of a BPF we need to parse the workflows table and get the clientdata of the row. This is a JSON stored as a string. It is comprised of StageSteps (BPF Stages) and StepSteps (the BPF data steps). We need this information so that we can send it back to copilot and inform the user what stage its at and the steps that need to be taken to update it. <\/p>\n<\/li>\n\n\n\n
          12. Next, add parallel Dataverse action to get the the workflow information\n
            \"\"\/<\/figure>\n<\/li>\n\n\n\n
          13. Add a Dataverse, get row by ID, and use the process ID gathered above\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            outputs('List_rows_-_BPF')?['body']?['value'][0]?['_processid_value']<\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          14. Nex,t will be a ParseJSON of the outputs clientdata\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            {\n    \"type\": \"object\",\n    \"properties\": {\n        \"__class\": {\n            \"type\": \"string\"\n        },\n        \"id\": {\n            \"type\": \"string\"\n        },\n        \"description\": {\n            \"type\": \"string\"\n        },\n        \"name\": {\n            \"type\": \"string\"\n        },\n        \"stepLabels\": {\n            \"type\": \"object\",\n            \"properties\": {\n                \"list\": {\n                    \"type\": \"array\"\n                }\n            }\n        },\n        \"steps\": {\n            \"type\": \"object\",\n            \"properties\": {\n                \"list\": {\n                    \"type\": \"array\",\n                    \"items\": {\n                        \"type\": \"object\",\n                        \"properties\": {\n                            \"__class\": {\n                                \"type\": \"string\"\n                            },\n                            \"id\": {\n                                \"type\": \"string\"\n                            },\n                            \"description\": {\n                                \"type\": \"string\"\n                            },\n                            \"name\": {\n                                \"type\": \"string\"\n                            },\n                            \"stepLabels\": {\n                                \"type\": \"object\",\n                                \"properties\": {\n                                    \"list\": {\n                                        \"type\": \"array\"\n                                    }\n                                }\n                            },\n                            \"steps\": {\n                                \"type\": \"object\",\n                                \"properties\": {\n                                    \"list\": {\n                                        \"type\": \"array\"\n                                    }\n                                }\n                            },\n                            \"relationshipName\": {},\n                            \"attributeName\": {},\n                            \"isClosedLoop\": {\n                                \"type\": \"boolean\"\n                            }\n                        },\n                        \"required\": [\n                            \"__class\",\n                            \"id\",\n                            \"description\",\n                            \"name\",\n                            \"stepLabels\",\n                            \"steps\"\n                        ]\n                    }\n                }\n            }\n        },\n        \"primaryEntityName\": {\n            \"type\": \"string\"\n        },\n        \"nextStepIndex\": {\n            \"type\": \"string\"\n        },\n        \"isCrmUIWorkflow\": {\n            \"type\": \"boolean\"\n        },\n        \"category\": {\n            \"type\": \"string\"\n        },\n        \"businessProcessType\": {\n            \"type\": \"string\"\n        },\n        \"mode\": {\n            \"type\": \"string\"\n        },\n        \"title\": {\n            \"type\": \"string\"\n        },\n        \"workflowEntityId\": {\n            \"type\": \"string\"\n        },\n        \"formId\": {},\n        \"argumentsArray\": {\n            \"type\": \"array\"\n        },\n        \"variables\": {\n            \"type\": \"array\"\n        },\n        \"inputs\": {\n            \"type\": \"array\"\n        }\n    }\n}<\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          15. A filter array is used to filter the clientdata so only the steps are given and no other parts of the data. To do this, we need to convert the list data into an array then filter it\n\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            array(body('Parse_JSON_-_Workflow_Client_data')?['steps']?['list'])<\/code><\/pre>\n\n\n\n
             <\/p>\n\n\n\n
            equals(item()?['__class'], 'EntityStep:#Microsoft.Crm.Workflow.ObjectModel')<\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          16. Then, we can set the Entity logical name\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            outputs('Filter_array_-_For_Steps')['body']?[0]?['description']<\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          17. To get the steps within the array, we now add an apply to each and use the body of the filter array - for steps<\/li>\n\n\n\n
          18. Configure the Apply to each to run after the setting of the variable and the compose of the stage name. The Apply to each will loop over each item from the Filter array - For Steps\n
            \"\"\/<\/figure>\n<\/li>\n\n\n\n
          19. Add an append to array variable that will add the stage and the steps to the variable  - varStagearray\n\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            item()?['steps']?['list']?[0]?['description']<\/code><\/pre>\n\n\n\n
             <\/p>\n\n\n\n
            item()?['steps']?['list']?[0]?['steps']?['list']<\/code><\/pre>\n\n\n\n
             <\/p>\n<\/li>\n\n\n\n
          20. With an array of the stages and steps, we now clean the steps so we can send a \u201cclean\u201d list to Copilot Studio to show the user the exact stage and steps. To do this, we will do another For Each loop within the true leg of the condition.<\/li>\n\n\n\n
          21. Before we can do the loop though, add in a parse JSON to access the array a bit easier, then add the loop and another append to array variable. \n
            \"\"\/<\/figure>\n<\/li>\n\n\n\n
          22. Set the variable varCurrentStageName with the current stage name\n
            \"\"\/<\/figure>\n<\/li>\n\n\n\n
          23. The last step of the flow is to respond to the Copilot with the current Stage\/Step\/Current table\/RecordGUID\n
            \"\"\/<\/figure>\n<\/li>\n<\/ol>\n\n\n\n
            \ud83d\udca1 It is important to give the responses to Copilot good names along with good descriptions <\/p>\n\n\n\n
            {\n  \"type\": \"Response\",\n  \"kind\": \"Skills\",\n  \"inputs\": {\n    \"schema\": {\n      \"type\": \"object\",\n      \"properties\": {\n        \"current_status\": {\n          \"title\": \"Current Status\",\n          \"description\": \"This is the current stage being used in the business process flow\",\n          \"type\": \"string\",\n          \"x-ms-content-hint\": \"TEXT\",\n          \"x-ms-dynamically-added\": true\n        },\n        \"list_of steps to complete\": {\n          \"title\": \"List of Steps to Complete\",\n          \"description\": \"These are the current steps within the current stage of the business process flow\",\n          \"type\": \"string\",\n          \"x-ms-content-hint\": \"TEXT\",\n          \"x-ms-dynamically-added\": true\n        },\n        \"prints_table logical name\": {\n          \"title\": \"Prints Table Logical Name\",\n          \"description\": \"\",\n          \"type\": \"string\",\n          \"x-ms-content-hint\": \"TEXT\",\n          \"x-ms-dynamically-added\": true\n        },\n        \"print_record guid\": {\n          \"title\": \"Print Record GUID\",\n          \"description\": \"\",\n          \"type\": \"string\",\n          \"x-ms-content-hint\": \"TEXT\",\n          \"x-ms-dynamically-added\": true\n        }\n      },\n      \"additionalProperties\": {}\n    },\n    \"statusCode\": 200,\n    \"body\": {\n      \"current_status\": \"@{variables('varCurrentStageName')}\",\n      \"list_of steps to complete\": \"@{variables('varStepArray')}\",\n      \"prints_table logical name\": \"@{variables('varEntityLogicalName')}\",\n      \"print_record guid\": \"@{outputs('List_rows_-_Prints')?['body\/value']?[0]?['andy_printsid']}\"\n    }\n  },\n  \"runAfter\": {\n    \"Scope_-_Process_Stage_and_Workflows\": [\n      \"Succeeded\"\n    ]\n  }\n}<\/code><\/pre>\n\n\n\n
             <\/p>\n\n\n\n
            Testing<\/h3>\n\n\n\n
            To test the co pilot, I have added 30 records<\/p>\n\n\n\n
            Print Job<\/strong><\/td>Requestor<\/strong><\/td>Print Item<\/strong><\/td>Quanity<\/strong><\/td><\/tr>
            Print Job 1<\/td>Jim Henson<\/td>Kermit the Frog<\/td>3<\/td><\/tr>
            Print Job 10<\/td>Jim Henson<\/td>Dr. Bunsen Honeydew<\/td>25<\/td><\/tr>
            Print Job 11<\/td>Jim Henson<\/td>Beaker<\/td>60<\/td><\/tr>
            Print Job 12<\/td>Jim Henson<\/td>Scooter<\/td>13<\/td><\/tr>
            Print Job 13<\/td>Jim Henson<\/td>Rizzo the Rat<\/td>70<\/td><\/tr>
            Print Job 14<\/td>Jim Henson<\/td>Sam Eagle<\/td>2<\/td><\/tr>
            Print Job 15<\/td>Jim Henson<\/td>Sweetums<\/td>45<\/td><\/tr>
            Print Job 16<\/td>Jim Henson<\/td>Dr. Teeth<\/td>27<\/td><\/tr>
            Print Job 17<\/td>Jim Henson<\/td>Floyd Pepper<\/td>39<\/td><\/tr>
            Print Job 18<\/td>Jim Henson<\/td>Janice<\/td>9<\/td><\/tr>
            Print Job 19<\/td>Jim Henson<\/td>Zoot<\/td>50<\/td><\/tr>
            Print Job 2<\/td>Jim Henson<\/td>Miss Piggy<\/td>15<\/td><\/tr>
            Print Job 20<\/td>Jim Henson<\/td>Camilla the Chicken<\/td>6<\/td><\/tr>
            Print Job 21<\/td>Jim Henson<\/td>Robin the Frog<\/td>73<\/td><\/tr>
            Print Job 22<\/td>Jim Henson<\/td>Crazy Harry<\/td>30<\/td><\/tr>
            Print Job 23<\/td>Jim Henson<\/td>Lew Zealand<\/td>1<\/td><\/tr>
            Print Job 24<\/td>Jim Henson<\/td>Mahna Mahna<\/td>12<\/td><\/tr>
            Print Job 25<\/td>Jim Henson<\/td>Pepe the King Prawn<\/td>42<\/td><\/tr>
            Print Job 26<\/td>Jim Henson<\/td>Beauregard<\/td>18<\/td><\/tr>
            Print Job 27<\/td>Jim Henson<\/td>Clifford<\/td>58<\/td><\/tr>
            Print Job 28<\/td>Jim Henson<\/td>Link Hogthrob<\/td>14<\/td><\/tr>
            Print Job 29<\/td>Jim Henson<\/td>Marvin Suggs<\/td>67<\/td><\/tr>
            Print Job 3<\/td>Jim Henson<\/td>Fozzie Bear<\/td>22<\/td><\/tr>
            Print Job 30<\/td>Jim Henson<\/td>Uncle Deadly<\/td>31<\/td><\/tr>
            Print Job 4<\/td>Jim Henson<\/td>Gonzo the Great<\/td>5<\/td><\/tr>
            Print Job 5<\/td>Jim Henson<\/td>Animal<\/td>48<\/td><\/tr>
            Print Job 6<\/td>Jim Henson<\/td>Rowlf the Dog<\/td>7<\/td><\/tr>
            Print Job 7<\/td>Jim Henson<\/td>Statler<\/td>34<\/td><\/tr>
            Print Job 8<\/td>Jim Henson<\/td>Waldorf<\/td>69<\/td><\/tr>
            Print Job 9<\/td>Jim Henson<\/td>The Swedish Chef<\/td>11<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
            With just this data and the current topic on here is my test, ask how many jobs there are, get some basic info on a job, then get the BPF status<\/p>\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
             <\/p>\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            We now have a good extraction of the stage and steps. The next step will be to allow the user to update the BPF via copilot. <\/p>\n\n\n\n
            Topic 2 - Get Column Info<\/h2>\n\n\n\n
            Topic flow - Part 1<\/h3>\n\n\n\n
            With the columns returned, we now need to get the types when then user asks to update the record. This could have been done during the last flow, but for the sake of speed, I decided to run it after a user asks for it. <\/p>\n\n\n\n
              \n
            1. Create a trigger and a message for the user. The trigger will be called if the user asks to update the record. The message will let the user know its working. \n
              \"\"\/<\/figure>\n<\/li>\n\n\n\n
            2. Run a Power Automate flow that will give us the column types out put (the guide for the flow is next)\n
              \"\"\/<\/figure>\n<\/li>\n<\/ol>\n\n\n\n
               <\/p>\n\n\n\n
              Power Automate flow - Get Column info from Copilot<\/h3>\n\n\n\n
              This next flow will get the column information that was gathered from the previous flow. This is important because we don't just want to get the information into the bot, we want to write updates back to the BPF.  <\/p>\n\n\n\n
                \n
              1. The trigger will be a run from copilot with two text inputs, Table and steps\n
                \"\"\/<\/figure>\n<\/li>\n\n\n\n
              2. The next step is to do a Parse JSON of the steps then to initialize a new variable array called vatTypes\n\n\n
                \"\"\/<\/figure>\n\n\n\n
                {\n    \"type\": \"array\",\n    \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n            \"StepName\": {\n                \"type\": \"string\"\n            },\n            \"StepLogicalName\": {\n                \"type\": \"string\"\n            }\n        },\n        \"required\": [\n            \"StepName\",\n            \"StepLogicalName\"\n        ]\n    }\n}<\/code><\/pre>\n\n\n\n
                 <\/p>\n<\/li>\n\n\n\n
              3. To get the column type we need to run an HTTP request to Dataverse that will sit in a for each loop of the steps, I like to use the HTTP with Microsoft Entra ID (preauthorized)\n\n\n\n\n\n\n
                \"\"\/<\/figure>\n\n\n\n
                \"\"\/<\/figure>\n\n\n\n
                body('Parse_JSON_-_Steps')?[0]?['stepList']<\/code><\/pre>\n\n\n\n
                 <\/p>\n\n\n\n
                \"\"\/<\/figure>\n\n\n\n
                \/api\/data\/v9.2\/EntityDefinitions(LogicalName='@{triggerBody()?['text']}')\/Attributes(LogicalName='@{items('Apply_to_each_-_each_step')['StepLogicalName']}')<\/code><\/pre>\n\n\n\n
                 <\/p>\n<\/li>\n\n\n\n
              4. Add an append to array action that add the following\n\n\n
                \"\"\/<\/figure>\n\n\n\n
                {\n  \"columnname\": @{items('Apply_to_each_-_each_step')?['StepLogicalName']},\n  \"value\": @{body('Invoke_an_HTTP_request')?['AttributeTypeName']?['Value']},\n  \"output\": \"\",\n  \"DisplayName\": @{items('Apply_to_each_-_each_step')?['StepName']}\n}<\/code><\/pre>\n\n\n\n
                 <\/p>\n<\/li>\n\n\n\n
              5. Within the respond to Copilot add the varTypes to the columntypes output\n
                \"\"\/<\/figure>\n<\/li>\n<\/ol>\n\n\n\n
                Topic flow - Part 2<\/h3>\n\n\n\n
                  \n
                1. Parse the variable to save it as a table\n\n\n
                  \"\"\/<\/figure>\n\n\n\n
                  kind: Table\nproperties:\n  columnname: String\n  DisplayName: String\n  output: String\n  value: String<\/code><\/pre>\n\n\n\n
                   <\/p>\n<\/li>\n\n\n\n
                2. Then post a question to the user on the type of column to be updated\n
                  \"\"\/<\/figure>\n<\/li>\n<\/ol>\n\n\n\n
                  Testing<\/h3>\n\n\n\n
                  With that we can now get a list of options to update in the chat<\/p>\n\n\n\n
                  \"\"\/<\/figure>\n\n\n\n
                  Topic flow - Part 3 Update record<\/h3>\n\n\n\n
                  With all this information, we can now run a flow to update the record. However, since we do not know the table, or the column name, nor the column type until the user request it from the chat, we need to make a dynamic update process. <\/p>\n\n\n\n
                  There are a total of 28 data verse column types Column data types in Microsoft Dataverse - Power Apps | Microsoft Learn<\/a><\/p>\n\n\n\n
                   <\/p>\n\n\n\n
                  Name<\/th>Type<\/th>Data<\/th><\/tr><\/thead>
                  Big<\/strong><\/td>Time Stamp<\/strong><\/td>BigIntType<\/code><\/td><\/tr>
                  Choice<\/strong><\/td>Option Set<\/strong><\/td>PicklistType<\/code><\/td><\/tr>
                  Choices<\/strong><\/td>MultiSelect Field<\/strong><\/td>MultiSelectPicklistType<\/code><\/td><\/tr>
                  Currency<\/strong><\/td>Currency<\/strong><\/td>MoneyType<\/code><\/td><\/tr>
                  Customer<\/strong><\/td>Customer<\/strong><\/td>CustomerType<\/code><\/td><\/tr>
                  Date and Time<\/strong><\/td>Date and Time <\/strong>Date and Time<\/em> Format<\/td>DateTimeType<\/code><\/td><\/tr>
                  Date Only<\/strong><\/td>Date and Time <\/strong>Date Only<\/em> Format<\/td>DateTimeType<\/code><\/td><\/tr>
                  Decimal Number<\/strong><\/td>Decimal Number<\/strong><\/td>DecimalType<\/code><\/td><\/tr>
                  Duration<\/strong><\/td>Whole Number <\/strong>Duration<\/em> Format<\/td>IntegerType<\/code><\/td><\/tr>
                  Email<\/strong><\/td>Single Line of Text <\/strong>Email<\/em> Format<\/td>StringType<\/code><\/td><\/tr>
                  File<\/strong><\/td>File<\/strong><\/td>FileType<\/code><\/td><\/tr>
                  Floating Point Number<\/strong><\/td>Floating Point Number<\/strong><\/td>DoubleType<\/code><\/td><\/tr>
                  Image<\/strong><\/td>Image<\/strong><\/td>ImageType<\/code><\/td><\/tr>
                  Language<\/strong><\/td>Whole Number <\/strong>Language<\/em> Format<\/td>IntegerType<\/code><\/td><\/tr>
                  Lookup<\/strong><\/td>Lookup<\/strong><\/td>LookupType<\/code><\/td><\/tr>
                  Multiline Text<\/strong><\/td>Multiple Lines of Text<\/strong><\/td>MemoType<\/code><\/td><\/tr>
                  Owner<\/strong><\/td>Owner<\/strong><\/td>OwnerType<\/code><\/td><\/tr>
                  Phone<\/strong><\/td>Single Line of Text <\/strong>Phone<\/em> Format<\/td>StringType<\/code><\/td><\/tr>
                  Status<\/strong><\/td>Status<\/strong><\/td>StateType<\/code><\/td><\/tr>
                  Status Reason<\/strong><\/td>Status Reason<\/strong><\/td>StatusType<\/code><\/td><\/tr>
                  Text<\/strong><\/td>Single Line of Text <\/strong>Text<\/em> Format<\/td>StringType<\/code><\/td><\/tr>
                  Text Area<\/strong><\/td>Single Line of Text <\/strong>Text Area<\/em> Format<\/td>StringType<\/code><\/td><\/tr>
                  Ticker Symbol<\/strong><\/td>Single Line of Text <\/strong>Ticker Symbol Format<\/td>StringType<\/code><\/td><\/tr>
                  Timezone<\/strong><\/td>Whole Number <\/strong>Time Zone<\/em> Format<\/td>IntegerType<\/code><\/td><\/tr>
                  Unique Identifier<\/strong><\/td>Unique Identifier<\/strong> or Primary Key<\/strong><\/td>UniqueidentifierType<\/code><\/td><\/tr>
                  URL<\/strong><\/td>Single Line of Text <\/strong>URL<\/em> Format<\/td>StringType<\/code><\/td><\/tr>
                  Whole Number<\/strong><\/td>Whole Number <\/strong>None<\/em> Format<\/td>IntegerType<\/code><\/td><\/tr>
                  Yes\/No<\/strong><\/td>Two Options<\/strong><\/td>BooleanType<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                  With this information, we can build a set of flows that will update the appropriate data type. For the example I will build out three types, Date\/String\/Boolean.<\/p>\n\n\n\n
                    \n
                  1. Add three conditions to handle the data types\n
                    \"\"\/<\/figure>\n<\/li>\n\n\n\n
                  2. Under the Boolean condition add a ask with adaptive card action\n\n
                    {\n    \"type\": \"AdaptiveCard\",\n    \"$schema\": \"http:\/\/adaptivecards.io\/schemas\/adaptive-card.json\",\n    \"version\": \"1.5\",\n    \"body\": [\n        {\n            \"type\": \"Container\",\n            \"items\": [\n                {\n                    \"type\": \"Input.Toggle\",\n                    \"title\": \"TITLE\",\n                    \"id\": \"varRequestupdateboolean\"\n                }\n            ]\n        },\n        {\n            \"type\": \"ActionSet\",\n            \"actions\": [\n                {\n                    \"type\": \"Action.Submit\",\n                    \"title\": \"Submit\",\n                    \"style\": \"positive\",\n                    \"id\": \"SubmitOutput\"\n                }\n            ]\n        }\n    ]\n}<\/code><\/pre>\n\n\n\n
                     <\/p>\n<\/li>\n\n\n\n
                  3. Since this is Copilot studio, we can update the card to be dynamic with Power FX. So that the title is the display name of the column\n\n
                    {\n  type: \"AdaptiveCard\",\n  '$schema': \"http:\/\/adaptivecards.io\/schemas\/adaptive-card.json\",\n  version: \"1.5\",\n  body: [\n    {\n      type: \"Container\",\n      items: [\n        {\n          type: \"Input.Toggle\",\n          title: Topic.varRequestedUpdate.DisplayName,\n          id: \"varRequestupdateboolean\"\n        }\n      ]\n    },\n    {\n      type: \"ActionSet\",\n      actions: [\n        {\n          type: \"Action.Submit\",\n          title: \"Submit\",\n          style: \"positive\",\n          id: \"SubmitOutput\"\n        }\n      ]\n    }\n  ]\n}<\/code><\/pre>\n\n\n\n
                     <\/p>\n<\/li>\n\n\n\n
                  4. For the Date add this adaptive card\n\n
                    {\n    \"type\": \"AdaptiveCard\",\n    \"$schema\": \"http:\/\/adaptivecards.io\/schemas\/adaptive-card.json\",\n    \"version\": \"1.5\",\n    \"body\": [\n        {\n            \"type\": \"Container\",\n            \"items\": [\n                {\n                    \"type\": \"Input.Date\",\n                    \"id\": \"varRequestupdatedate\",\n                    \"label\": \"Title\"\n                }\n            ]\n        },\n        {\n            \"type\": \"ActionSet\",\n            \"actions\": [\n                {\n                    \"type\": \"Action.Submit\",\n                    \"title\": \"Submit\",\n                    \"style\": \"positive\",\n                    \"id\": \"SubmitOutput\"\n                }\n            ]\n        }\n    ]\n}<\/code><\/pre>\n\n\n\n
                     <\/p>\n<\/li>\n\n\n\n
                  5. Then update the card to use the following Power FX\n\n
                    {\n  type: \"AdaptiveCard\",\n  '$schema': \"http:\/\/adaptivecards.io\/schemas\/adaptive-card.json\",\n  version: \"1.5\",\n  body: [\n    {\n      type: \"Container\",\n      items: [\n        {\n          type: \"Input.Date\",\n          id: \"varRequestupdatedate\",\n          label: Topic.varRequestedUpdate.DisplayName\n        }\n      ]\n    },\n    {\n      type: \"ActionSet\",\n      actions: [\n        {\n          type: \"Action.Submit\",\n          title: \"Submit\",\n          style: \"positive\",\n          id: \"SubmitOutput\"\n        }\n      ]\n    }\n  ]\n}<\/code><\/pre>\n\n\n\n
                     <\/p>\n<\/li>\n\n\n\n
                  6. For the string type use this card\n\n
                    {\n    \"type\": \"AdaptiveCard\",\n    \"$schema\": \"http:\/\/adaptivecards.io\/schemas\/adaptive-card.json\",\n    \"version\": \"1.5\",\n    \"body\": [\n        {\n            \"type\": \"Container\",\n            \"items\": [\n                {\n                    \"type\": \"Input.Text\",\n                    \"placeholder\": \"Placeholder text\",\n                    \"id\": \"varRequestupdatestring\",\n                    \"label\": \"TITLE\"\n                }\n            ]\n        },\n        {\n            \"type\": \"ActionSet\",\n            \"actions\": [\n                {\n                    \"type\": \"Action.Submit\",\n                    \"title\": \"Submit\",\n                    \"style\": \"positive\",\n                    \"id\": \"SubmitOutput\"\n                }\n            ]\n        }\n    ]\n}<\/code><\/pre>\n\n\n\n
                     <\/p>\n<\/li>\n\n\n\n
                  7. Then update it with the following PowerFX\n\n
                    {\n  type: \"AdaptiveCard\",\n  '$schema': \"http:\/\/adaptivecards.io\/schemas\/adaptive-card.json\",\n  version: \"1.5\",\n  body: [\n    {\n      type: \"Container\",\n      items: [\n        {\n          type: \"Input.Text\",\n          placeholder: \"\",\n          id: \"varRequestupdatestring\",\n          label: Topic.varRequestedUpdate.DisplayName\n        }\n      ]\n    },\n    {\n      type: \"ActionSet\",\n      actions: [\n        {\n          type: \"Action.Submit\",\n          title: \"Submit\",\n          style: \"positive\",\n          id: \"SubmitOutput\"\n        }\n      ]\n    }\n  ]\n}<\/code><\/pre>\n\n\n\n
                     <\/p>\n<\/li>\n\n\n\n
                  8. With the cards created we can now run the flows. There will be a flow per each condition. For the example given here, I will show how to build the date update flow. I am going to do another post on how to handle each column type later.<\/li>\n<\/ol>\n\n\n\n
                    Power Automate - Update DateTime Flow<\/h3>\n\n\n\n
                      \n
                    1. Create a flow with four text inputs and one date input\n
                      \"\"\/<\/figure>\n<\/li>\n\n\n\n
                    2. The two HTTP requests will be added. The first will be used to get the entity set name and the second will be used to preform the update\n\n\n\n\n\n\n\n
                      \"\"\/<\/figure>\n\n\n\n
                      \/api\/data\/v9.0\/EntityDefinitions?$filter=LogicalName eq '@{triggerBody()?['text_2']}'&$select=EntitySetName<\/code><\/pre>\n\n\n\n
                       <\/p>\n\n\n\n
                      \"\"\/<\/figure>\n\n\n\n
                      \/api\/data\/v9.2\/@{body('Invoke_an_HTTP_request_-_Get_entityset_name')?['value']?[0]?['EntitySetName']}(@{triggerBody()?['text_3']})<\/code><\/pre>\n\n\n\n
                       <\/p>\n\n\n\n
                      {\n  @{triggerBody()?['text']}:\"@{triggerBody()?['date']}\"\n}d<\/code><\/pre>\n\n\n\n
                       <\/p>\n<\/li>\n\n\n\n
                    3. This will be closed out by an empty respond to co pilot action<\/li>\n<\/ol>\n\n\n\n
                      Within the studio, add the call flow action. For the date ensure to wrap it in the date value PowerFX formula <\/p>\n\n\n\n
                      Text(DateValue(Topic.varRequestupdatedate), \"yyyy-mm-dd\")<\/code><\/pre>\n\n\n\n
                       <\/p>\n\n\n\n
                       <\/p>\n\n\n\n
                      \"\"\/<\/figure>\n\n\n\n
                      Power Automate - Update Boolean<\/h3>\n\n\n\n
                      The flow will be very similar to the one above with the biggest difference being that the trigger will use a Boolean vs a date<\/p>\n\n\n\n
                        \n
                      1. Add the trigger with the following inputs\n
                        \"\"\/<\/figure>\n<\/li>\n\n\n\n
                      2. As before, add two http actions, on to get the entity set name and one to preform the patch\n\n\n\n\n
                        \"\"\/<\/figure>\n\n\n\n
                        \/api\/data\/v9.2\/@{body('Invoke_an_HTTP_request_-_Get_entityset_name')?['value']?[0]?['EntitySetName']}(@{triggerBody()?['text_3']})<\/code><\/pre>\n\n\n\n
                         <\/p>\n\n\n\n
                        {\n  @{triggerBody()?['text']}: \"@{triggerBody()?['boolean']}\"\n}<\/code><\/pre>\n\n\n\n
                         <\/p>\n<\/li>\n\n\n\n
                      3. This will be closed out by an empty respond to co pilot action<\/li>\n<\/ol>\n\n\n\n
                        Within the studio add the call flow action. For the date ensure to wrap it in the datevalue PowerFX formula<\/p>\n\n\n\n
                        If(Topic.varRequestupdateboolean= \"True\", true, false)<\/code><\/pre>\n\n\n\n
                         <\/p>\n\n\n\n
                         <\/p>\n\n\n\n
                        \"\"\/<\/figure>\n\n\n\n
                         <\/p>\n\n\n\n
                        Power Automate - Update String<\/h3>\n\n\n\n
                          \n
                        1. Add the trigger with the following inputs\n
                          \"\"\/<\/figure>\n<\/li>\n\n\n\n
                        2. As add two http actions, on to get the entity set name and one to preform the patch\n\n\n\n\n
                          \"\"\/<\/figure>\n\n\n\n
                          \/api\/data\/v9.2\/@{body('Invoke_an_HTTP_request_-_Get_entityset_name')?['value']?[0]?['EntitySetName']}(@{triggerBody()?['text_3']})<\/code><\/pre>\n\n\n\n
                           <\/p>\n\n\n\n
                          {\n  @{triggerBody()?['text']}: \"@{triggerBody()?['text_4']}\"\n}<\/code><\/pre>\n\n\n\n
                           <\/p>\n<\/li>\n\n\n\n
                        3. This will be closed out by an empty respond to co pilot action<\/li>\n<\/ol>\n\n\n\n
                          Topic flow - Part 4 Close it out and loopback<\/h3>\n\n\n\n
                          The last step will be to ask the user if there are any other fields to update<\/p>\n\n\n\n
                            \n
                          1. After all the conditions, add a question that asks the user if there are any other fields to update\n
                            \"\"\/<\/figure>\n<\/li>\n\n\n\n
                          2. If the output is true loop back to the Question \u201cWhat would you like to update\u201d, else end the conversation.\n
                            \"\"\/<\/figure>\n<\/li>\n<\/ol>\n\n\n\n
                             <\/p>\n\n\n\n
                             <\/p>\n\n\n\n
                            \"\"\/<\/figure>\n\n\n\n
                             <\/p>\n\n\n\n
                            \"\"\/<\/figure>\n\n\n\n
                            \"\"\/<\/figure>\n\n\n\n
                             <\/p>\n\n\n\n
                            \"\"\/<\/figure>\n\n\n\n
                             <\/p>\n\n\n\n
                            \"\"\/<\/figure>\n\n\n\n
                             <\/p>\n\n\n\n
                            I think there are quite a few things that can be done to improve this. I really want to get the generative flows along with less reliance on Power Automate. <\/p>\n","protected":false},"excerpt":{"rendered":"
                            In a recent project, I was challenged to try and build a Copilot in Copilot Studio that would update a Dataverse table AND update a Business Process Flow (BPF). On its face, its not too crazy. These are classic components of the Power Platform. However, after a few days, it turned into quite the endeavor. […]<\/p>\n","protected":false},"author":1,"featured_media":1180,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34,49,50,22,30,35],"tags":[],"class_list":["post-1184","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-process-flow","category-copilot","category-copilot-studio","category-dataverse","category-power-automate","category-tables","entry","has-media"],"jetpack_featured_media_url":"https:\/\/automatethemundane.com\/wp-content\/uploads\/2024\/11\/photo-1549969341-853262338e07-scaled.jpg","_links":{"self":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/comments?post=1184"}],"version-history":[{"count":3,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1184\/revisions"}],"predecessor-version":[{"id":1189,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1184\/revisions\/1189"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media\/1180"}],"wp:attachment":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media?parent=1184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/categories?post=1184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/tags?post=1184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}