Power Platform and Chat GPT

Ben Prins Office 365

In today’s digital age, businesses are constantly looking for ways to streamline their processes and improve their customer experience. One way to do this is by leveraging the power of chatbots, which can quickly and efficiently answer customer inquiries. In this blog post, we will explore how to create a canvas app that uses Power Automate flow to ask ChatGPT API questions and display the response in the canvas app. By the end of this tutorial, you will have the tools and knowledge to build your own chatbot app that can answer your customers’ questions in real-time, enhancing their overall experience and increasing your operational efficiency. So, let’s get started!

Create a Chat GPT Api secret

  • Open the ChatGPT API site.
  • Login or create an account.
  • Click on Personal, followed by View API keys.
  • Click on Create new secret key and save the key in a password vault.

Creating the Power Automate Flow

  • Create a new Power Automate flow with the name Canvas app – Chat GPT.
  • Add as the trigger a PowerApps V2.
  • Add a text input with the name Question.
  • Add a HTTP action with the name Post to Chat GPT.
  • Set the Method to POST.
  • Set the URI to https://api.openai.com/v1/completions.
  • Set the header to Content-Type with value application/json.
  • Set a second header to Authorization with the value Bearer [API Secret].
  • Set the body to the following json code.
{
  "model": "text-davinci-003",
  "prompt": "triggerBody()['text']",
  "temperature": 0,
  "max_tokens": 4000
}
  • Add a Parse JSON action with the name Chat GPT Response.
  • Set the Content to Body (response of the HTTP call).
  • Set the following schema (update the schema is the response is different).
{
    "type": "object",
    "properties": {
        "id": {
            "type": "string"
        },
        "object": {
            "type": "string"
        },
        "created": {
            "type": "integer"
        },
        "model": {
            "type": "string"
        },
        "choices": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "text": {
                        "type": "string"
                    },
                    "index": {
                        "type": "integer"
                    },
                    "logprobs": {},
                    "finish_reason": {
                        "type": "string"
                    }
                },
                "required": [
                    "text",
                    "index",
                    "logprobs",
                    "finish_reason"
                ]
            }
        },
        "usage": {
            "type": "object",
            "properties": {
                "prompt_tokens": {
                    "type": "integer"
                },
                "completion_tokens": {
                    "type": "integer"
                },
                "total_tokens": {
                    "type": "integer"
                }
            }
        }
    }
}
  • Add the Respond to a PowerApp or Flow action.
  • Add a text output called ChatGPTRepsonse and add the response from Chat GPT with the following code.
first(body('HTTP_-_Post_to_Chat_GPT')?['choices'])?['text']
  • The overall Power Automate flow will look like this.

Creating the Canvas app

  • Open the Power Apps Studio and create a new canvas app.
  • Rename Screen1 to Home.
  • Add the Canvas app – Chat GPT Power Automate flow to the canvas app.
  • Add a Rectange Shape to the top of the canvas app with the name RectTitle.
  • Add a label over the RectTitle with the name lblChatGPT.
  • Set the Text to “Send your question to the all powerful Chat GPT AI bot”.
  • Add a label with the name lblGPTRepsonse.
  • Place the lblGPTRepsonse on the right side of the screen.
  • Add a text input with the name txtQuestion.
  • Place the txtQuestion on the left side of the screen.
  • Set the txtQuestion Default to “What is your question?”.
  • If you like add an Image with the name imgRobot and add an image of a robot in the Image property.
  • Place the imgRobot left and next to the lblGPTRepsonse.
  • Add a button with the name btnSendQuestion.
  • Set the following code on the Onselect of the btnSendQuestion.
    • This will save the response in the variable repsonsegpt.
    • Start the flow with the text provided in the textQuestion text input box.
Set(responsegpt, 'Canvasapp-ChatGPT'.Run(txtQuestion.Text).chatgptrespondse)

Power Automate: Dataverse Contact automatic invitation redemption

Ben Prins Office 365

In my previouse blog post I explained how to automatically create and delete B2C account for Dataverse Contacts. In this post, I will dive deeper into the process by automating the redemption of an invitation code process. By default, new contacts need to redeem an inventation code before they can access the Power Pages. I am using the one-time-password setup for access to the Power Pages and for ease of use I don’t want the contact (external users) to have to redeem an inventation code.

To achieve this, we will automate the process by granting the contact a web role, connecting the B2C account ID, and adding the external identity. This will allow the contact to access the Power Pages without having to redeem an invitation code.

Creating the automated redemption process

  • Add the following actions below the Scope – Create B2C Users.
  • Initialize a variable called Role URL as a string.
  • Open the required web role in the Portal management tool, the ID is in the end of the URL.
https://[environment].crm4.dynamics.com/main.aspx?appid=39b012bd-1234-1234-1234-0022489fd314&pagetype=entityrecord&etn=adx_webrole&id=04ae7aa4-1234-1234-1234-0022489b74da
  • Add the following URL as the value, with your own unique web role id.
https://[environment].crm4.dynamics.com/api/data/v9.1/adx_webroles(04ae7aa4-1234-1234-1234-0022489b74da)
  • Initialize a variable called Invitation as a string.
  • Add as a value two guid()
  • Initialize a variable called External Identity URL as a String.
  • Add the following URL (with your environment name).
https://[environment].crm4.dynamics.com/api/data/v9.1/adx_externalidentities
  • Add a scope called Add Role to new contact and add the following actions.
  • Add the Dataverse Relate rows action called Add Role to Contact.
  • Set the Table name as Contacts.
  • Set the Row ID to Contact (the contact id of the contact where the flow was triggert on).
  • Set the Relationship to adx_webrole_contact.
  • Set the Relate with to the variable Role URL.
  • We are going to update the existing Dataverse Update a row action called Contact – Add User information.
  • Drag this action into the scope Add Role to new contact.
  • Set the Table name to Contacts.
  • Set the Row ID to Contact (the contact id of the contact where the flow was triggert on).
  • Set the Email Confirmed to Yes.
  • Set the Lockout Enabled to Yes.
  • Set the Login Enabled to Yes.
  • Set the Security Stamp to the guid() expression.
  • Set the Time Zone Rule Version Number to 0.
  • Set the User Name to the ID of the B2C account.
  • Add the Dataverse Add a new row action called External Identities.
  • Set the Table name to External Identities.
  • Set the Contact (Contacts) to Contacts(Contact) (the contact id of the contact where the flow was triggert on).
  • Set the Identity Provider to your Identity Provider URL
  • Set the User Name to the ID of the B2C account.
  • Add the Dataverse Relate rows action called Add External Identity to Contact.
  • Set the Table name to Contacts.
  • Set the Row ID to Contact (the contact id of the contact where the flow was triggert on).
  • Set the Relationship to adx_contact_externalidentity.
  • Set the Relate with the following code, the External Identity is the ID of the newly created External Identity.
https://[environment].crm4.dynamics.com/api/data/v9.1/adx_externalidentities()
  • The flow will now create a new B2C account when a new contact is created, link them together and automatically redeem the invitation.

Create and delete B2C accounts for Dataverse Contact

Ben Prins Office 365

Today, we’ll be discussing a crucial aspect of B2C account management – the creation and deletion of B2C accounts in response to changes in the Dataverse Contact. This is an important topic for businesses that deal with external Power Pages user (contacts) that want to ensure the security of their records and Power Pages. In this post I will explain how to create or delete B2C accounts that are connected to a Dataverse Contact. So, let’s dive in!

Create a B2C account when a Dataverse Contact is created

Whenever a new contact is created a new B2C account needs to be created automatically that is linked to the contact. This is done thought the email address of the contact and the Azure B2C account id. These automations will limit the amount of manual admin work.

  • Create a new Power Automate flow with the name Create B2C user for Dataverse Contact.
  • Add the Dataverse trigger When a row is added.
  • Set the Change type to Added.
  • Set the Table name to Contacts.
  • Set the Scope to Organization.
  • Create the following 3 variables.
  • You will need to create an Application Registration in the B2C tenant with the following permissions.
Permission typePermissions (from least to most privileged)
Delegated (work or school account)User.ReadWrite.All, Directory.ReadWrite.All
Delegated (personal Microsoft account)
Not supported
ApplicationUser.ReadWrite.All, Directory.ReadWrite.All
  • Store the Client ID, Tenant ID and Secrect in the corresponding variables.
  • Add a HTTP action called HTTP – Delete User to the flow.
  • Set the Method to: Post.
  • Set the URI to the following code.
https://graph.microsoft.com/v1.0/users
  • Set the body to the following code.
  • In my scenario the user will not be using the password but the one-time password from B2C. That’s why I have added a guid twice as the password.
  • This call will create an account of the type email address which allows for any valid email to be used. The email does not have to be part of the B2C domain.
  • Parse the JSON response of the HTTP – Create User call.
  • Add the Dataverse action Update a row.
  • Set the Table name to Contact.
  • Set the Row id to the contact id of the trigger.
  • Add the id that was returned by the HTTP call that created the B2C account.
  • Save the flow.

Delete B2C account when Dataverse Contact is deleted

In my scenario I am maintaining the Power Pages contacts within a canvas app, and when a contact is deleted the associated B2C account needs to be deleted too.

  • Create a new Power Automate flow with a Power Apps (V2) trigger with the name Delete B2C users for deleted Dataverse Contact.
  • Add an input Text field names ActiveDirectoryID.
    • This is the Object ID of the Azure B2C Active Directory User connected to the Contact.
  • Create the following 3 variables.
  • You will need to create an Application Registration in the B2C tenant with the following permissions.
Permission typePermissions (from least to most privileged)
Delegated (work or school account)User.ReadWrite.All
Delegated (personal Microsoft account)
Not supported
ApplicationUser.ReadWrite.All
  • Store the Client ID, Tenant ID and Secret in the corresponding variables.
  • Add a HTTP action called HTTP – Delete User to the flow.
  • Set the Method to: Delete.
  • Set the URI to the following code.
https://graph.microsoft.com/v1.0/users/
  • Add the PowerApp (V2) parameter ActiveDirectoryID to the end of the URI.
  • Set the Tenant, Client ID and Secret fields with their corresponding variables.
  • Set the Authentication to Active Directory OAuth.
  • Set the Audience to the following code.
https://graph.microsoft.com
  • Save the Power Automate Flow.
  • Open or create a canvas app (Power Apps).
  • Open the Power Automate panel in the canvas app.
  • Add the Delete B2C users for deleted Dataverse Contact Power Automate flow.
  • Add a gallery with the source set to the Dataverse Contact table.
  • Add a recycle bin or other delete Icon to the gallery.
  • Add the following code to the recycle bin icon under OnSelect.
    • This will remove the contact record.
    • Starts the Power Automate Flow and sending the User Name (Users B2C object ID).
    • Notifies the users.
Remove(Contacts, ThisItem);
DeleteB2CuserfordeletedDataverseContact.Run(ThisItem.'User Name');
Notify("Record deleted successfully", NotificationType.Success);
  • Save and publish the canvas app.

Teams / Power Automate adaptive cards

Ben Prins Office 365

When sharing information or sending out a notification on teams I like to use the adaptive card feature. Adaptive Cards are the Teams method of sharing and displaying blocks of information in an easy-to-read manor without the complexity of customizing CSS or HTML to render them. With adaptive cards you can even create polls, show weather information, and create hyperlinks.

Creating a feedback form

  • Create a Power Automate flow with the required trigger.
  • Add the Post adaptive card in a chat or channel Teams action.
  • Select the Group chat (Channel or Group Chat) or provide the chat ID.
  • Add the following JSON code for an example feedback form with a title, single line, and multi-line text input fields and two types of choice list.
{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "type": "AdaptiveCard",
  "version": "1.0",
  "body": [
    {
      "type": "TextBlock",
      "size": "Medium",
      "weight": "Bolder",
      "id": "Title",
      "text": "EXAMPLE FEEDBACK FORM",
      "horizontalAlignment": "Left"
    },
    {
      "type": "Input.Text",
      "placeholder": "{acFullName}",
      "style": "text",
      "isMultiline": false,
      "maxLength": 75,
      "id": "acFullName"
    },
    {
      "type": "Input.Text",
      "placeholder": "{acComments}",
      "style": "text",
      "isMultiline": true,
      "maxLength": 200,
      "id": "acComments"
    },
    {
      "type": "TextBlock",
      "size": "Medium",
      "weight": "Bolder",
      "text": "Do you like Adaptive Cards?",
      "horizontalAlignment": "Left",
      "separator": true
    },
    {
      "type": "Input.ChoiceSet",
      "id": "acDecision",
      "value": "1",
      "choices": [
        {
          "title": "Yes!",
          "value": "Yes"
        },
        {
          "title": "Of course!",
          "value": "Of course"
        }
      ],
      "style": "expanded"
    },
    {
      "type": "TextBlock",
      "text": "Suggest follow-up discussion regarding:",
      "weight": "Bolder"
    },
    {
      "type": "Input.ChoiceSet",
      "id": "acFollowUp",
      "isMultiSelect": true,
      "value": "",
      "choices": [
        {
          "title": "Everything",
          "value": "Everything"
        },
        {
          "title": "Always",
          "value": "Always"
        }
      ]
    }
  ],
  "actions": [
    {
      "type": "Action.Submit",
      "title": "Submit"
    }
  ]
}

Creating a Poll

  • Create a Power Automate flow with the required trigger.
  • Add the Post adaptive card in a chat or channel Teams action.
  • Select the Group chat (Channel or Group Chat) or provide the chat ID.
  • Add the following JSON code for an example Poll with a title, header line, header, text, and a short poll.
{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.0",
    "body": [
        {
            "type": "TextBlock",
            "text": "Example Poll Request",
            "id": "Title",
            "spacing": "Medium",
            "horizontalAlignment": "Center",
            "size": "ExtraLarge",
            "weight": "Bolder",
            "color": "Accent"
        },
        {
            "type": "TextBlock",
            "text": "Example Header Tagline Text",
            "id": "acHeaderTagLine",
            "separator": true
        },
        {
            "type": "TextBlock",
            "text": "Example  Poll Header",
            "weight": "Bolder",
            "size": "ExtraLarge",
            "spacing": "None",
            "id": "acHeader"
        },
        {
            "type": "TextBlock",
            "text": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer vestibulum lorem eget neque sollicitudin, quis malesuada felis ultrices. ",
            "id": "acInstructions",
            "wrap": true
        },
        {
            "type": "TextBlock",
            "text": "Example Poll Question",
            "id": "acPollQuestion"
        },
        {
            "type": "Input.ChoiceSet",
            "placeholder": "Select from these choices",
            "choices": [
                {
                    "title": "Choice 1",
                    "value": "Choice 1"
                },
                {
                    "title": "Choice 2",
                    "value": "Choice 2"
                }
            ],
            "id": "acPollChoices",
            "style": "expanded"
        }
    ],
    "actions": [
        {
            "type": "Action.Submit",
            "title": "Submit",
            "id": "btnSubmit"
        }
    ]
}

Creating a hyperlink

  • Create a Power Automate flow with the required trigger.
  • Add the Post adaptive card in a chat or channel Teams action.
  • Select the Group chat (Channel or Group Chat) or provide the chat ID.
  • You can use dynamics content for the hyperlink text and the hyperlink itself.
{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.0",
    "body": [
       {
            "type": "TextBlock",
            "text": "This is example 1 [Hyperlink Texts](https://powerautomate.microsoft.com/)",
            "id": "acHeader",
            "wrap": true
        },
       {
            "type": "TextBlock",
            "text": "This is example 2 [@{triggerOutputs()['headers']['x-ms-user-name-encoded']}](https://powerautomate.microsoft.com/)",
            "id": "acHeader",
            "wrap": true
        }
    ]
}

Finding the chat ID

  • There are multiple ways to find the chat ID, I like to use the following way.
  • Open teams in the browser and open the chat
  • In the URL you can see the ID of the chat. You need to copy the code after conversations including the @ and that is after the @.
  • In my first example the ID is: 19:de13fe3a9cae407ba31abc84421e9ab4@thread.v2
  • In my second example the ID is: 19:95b0d2cb-aa0c-4e0c-8fcf-2f7b77c5afdb_d523c084-0c04-41d0-81d2-943ad42abe9a@unq.gbl.spaces
  • The action in Power Automate with find the chat and show the name of that chat.
https://teams.microsoft.com/_#/conversations/19:de13fe3a9cae407ba31abc84421e9ab4@thread.v2?ctx=chat

https://teams.microsoft.com/_#/conversations/19:95b0d2cb-aa0c-4e0c-8fcf-2f7b77c5afdb_d523c084-0c04-41d0-81d2-943ad42abe9a@unq.gbl.spaces?ctx=chat

Power Automate: Custom Flow loging

Ben Prins Office 365

When managing multiple Power Automate Cloud Flows it can easily become a big task to figure out what went wrong and where. My client has more than 10 Cloud Flows that run multiple time per day or per hour. The solution is to create a log table (dataverse) or list (SharePoint) where all the runs are stored. The log table contains all the data an administrator needs to maintain the Cloud Flows. In my example I create a Dataverse table.

The Dataverse log table

In the log table we store the following data.

The Cloud Flow

In every Cloud flow a try and catch scope is added. All the main actions are in the try scope, if one of these actions fails than the catch scope will be used.

  • Add a Scope with the name Try.
  • Add the action the Dataverse Add a new row action.
  • Select the log table by Table name.
  • Select the Log status Processing, I use Processing, Failed and Successful.
  • Add the Cloud Flow name to Process name.
  • Add the following code to the Environment field.
workflow()?['tags']?['environmentName']
  • Add the following code to External URL, use the code snippets for the fx code.
    This creates a link to the actual flow run.
workflow()?['tags']?['environmentName']

workflow()?['name']

workflow()?['run']?['name']
  • Set the Status Reason to Active.
  • Add the end of the try scope add the Dataverse Update a row action.
  • Select the log table by Table name.
  • Add the ID of the create log in the Row ID field.
  • Select Successful in the Log status field.
  • Add a Scope with the name catch.
  • Add the Filter Array action.
  • Add the result of the try scope in the From field.
result('Try')
  • Add the following filter Status is equal to Failed
item()?['status']
  • Add the Dataverse Update a row action.
  • Select the log table by Table name.
  • Add the ID of the create log in the Row ID field.
  • Select Failed in the Log status field.
  • Select Inactive in the Status field.
  • Add the following text to the Log details: Flow log action(s):
  • Add the following code to the Log details, to show the error message from the try scope.
body('Filter_results_with_failed')[0]?['outputs']

Power Automate: Advanced Flow building

Ben Prins Office 365

Power Automate is one of my favorite tools from the Power Platform. It is extremely versatile and can be used to automate tasks between online services and automate processes ranging from simple to highly complex. In this post, I will share with you 3 advanced expressions I have used recently on my project. One part of the project is to convert XML data to data we can store in the dataverse.

Convert XML to JSON for easy access

For a project I needed to read multiple XML files with millions of rows and store data from the files into the dataverse. XML is harder to use in a Flow then JSON, so with a simple expression I transformed the XML to JSON.

  • Add a compose action with the name XML to JSON with the following code.
json(xml(variables('XML')))
  • Change the variable(‘XML’) to your XML content or store your XML content in that variable.
  • Add a parse json action set the Content to the output of the XML to JSON compose.
  • Add/create the JSON schema.

Using path in JSON

In most cases when you need to save data from JSON you can use the dynamic content to find it.
But sometimes you are looking for a field name that is not unique. In my case I needed a field called country related to the company. But the country field was used multiple times for various blocks. You can select the correct country by using the path (location) of the field in an expression.

  • Select the JSON through the dynamic content.
  • Copy the code from the dynamic content to the Expression.
  • Add the path add the end of the code.
  • I added .company.country to select the country of the company.
body('Parse_JSON').company.country

Dataverse lookup field

Lookup fields in Dataverse are really useful, but when you select them through the dynamic content the value will be the id not the display value. If the data must be readable for users, you can use the following steps to select the display value.

  • Add a compose to the flow.
  • Select the lookup field in the Inputs through the dynamic content.
  • Copy the code from the dynamic content to the Expression.
  • Your output looks something like this.
outputs('company')?['body/rc_countrycode']}
  • Add the @OData.Community.Display.V1.FormattedValue’ after rc_countrycode (your field name will be different).
  • The end results looks like this.
outputs('company')?['body/rc_countrycode@OData.Community.Display.V1.FormattedValue']