Creating interactive adaptive cards in MS Teams with dynamic data from SharePoint Lists   Recently updated !


Goal

Our end goal here is to post an adaptive card to 2 different departments for approval. Each department will be presented with a different set of rejection reasons based on their department dynamically. These rejection reasons are being fetched dynamically from the SharePoint List and are being appended to the adaptive cards. This eliminates the need to modify the adaptive cards each time the data in the SharePoint list is modified.

Pre-Requisites

  1. An account with license for below services:
    1. SharePoint Online (Plan 1/Plan 2)
    2. Microsoft Teams
    3. Microsoft Power Automate Free
  2. A SharePoint Online site with contribute access to the account in use
  3. A SharePoint List that will be used as a data input source. In our case, we are using a list called Equipment Request with the below columns:
    1. Title (this is the default column)
    2. Status (Single line of text)
      • Default value has been set to Pending
    3. A SharePoint List that will be used as a source to fetch dynamic data for reason based on department. In our case, we are using a list called Rejection Reason with the below columns:
      • Department (We have renamed Title column to Department)
      • Rejection Reason (Single line of text)

Steps to perform:

1. Create a SharePoint List Equipment Request. We will be using this list to add content and trigger a flow (when an item is created or modified) that will post an adaptive card. We will use the below columns in the list:

    1. Title (this is the default column)
    2. Status (Single line of text)
      • Default value has been set to Pending
  1. Create another SharePoint List Rejection Reason that will be used to dynamically render rejection reasons in the adaptive cards basis the department the card is being posted to. We will use the below columns in the list:
    1. Department (We have renamed Title column to Department)
    2. Rejection Reason (Single line of text)
  2. Populate data in the Rejection Reason list since we will be using data from this list in our adaptive cards. For our case, below is the sample data table:
Rejection Reason

 

4. We will make use of Power Automate to automatically post an adaptive card to a channel as soon as a new item is created in Equipment Request List

5. Create a Flow and set it to trigger when an item is created or modified

 

 

6. We will now initialize variables HRCounter & ITCounter. We will be using these variables as an item counter at a later stage. We have used these counters for making our troubleshooting a little bit easier. You can do away without creating these counters.

HRCounter ITCounter

 

7. Initialize another set variables HRReasons & ITReasons (Array). We will be using these variables to append data to the adaptive card.

HRReasons ITReasons

 

8. Initialize another set variables HRResponse & ITResponse (String). We will be using these variables to capture the response that is submitted by the approver.

HRResponse ITResponse

 

9. Get List Items (with Filter Query). Here we will filter and fetch rejection reasons only for “Human Resource” department (for demo purpose). Here you can dynamically fetch department for a given user and apply that filter for this query accordingly.

Get Filtered List Items

 

10. In this step, we will use Apply to each to append all the items (that we have retrieved from the list) to an array while incrementing the counter by 1.

 

  • These values that we have appended to the HRReasons Array will be used later when composing the adaptive card.
Append to Array HRReasons

 

  • We have used the below Expression Value for both Title & Value sections:

body(‘Get_Human_Resource_Rejection_Reasons_items_from_Rejection_Reason_List’)[‘value’][variables(‘HRCounter’)][‘RR’]

 

11. We will use Compose Action to compose an adaptive card message

Compose Message
  • Below is the JSON code that we used for composing our adaptive card:

{

  “type”: “AdaptiveCard”,

  “$schema”: “http://adaptivecards.io/schemas/adaptive-card.json”,

  “version”: “1.2”,

  “body”: [

    {

      “type”: “TextBlock”,

      “text”: “A New Request has been submitted by @{triggerOutputs()?[‘body/Author/DisplayName’]}”,

      “wrap”: true,

      “id”: “Request_Head”,

      “size”: “Medium”,

      “weight”: “Bolder”,

      “horizontalAlignment”: “Center”

    },

    {

      “type”: “TextBlock”,

      “text”: “@{triggerOutputs()?[‘body/Title’]}”,

      “wrap”: true,

      “id”: “Request_Body”

    }

  ],

  “actions”: [

    {

      “type”: “Action.Submit”,

      “title”: “Approve”,

      “id”: “Approve”,

      “style”: “positive”

    },

    {

      “type”: “Action.ShowCard”,

      “title”: “Reject”,

      “card”: {

        “type”: “AdaptiveCard”,

        “body”: [

          {

            “type”: “TextBlock”,

            “text”: “Please select a reason for rejecting the request.”,

            “wrap”: true,

            “id”: “Rejection_Heading”

          },

          {

            “type”: “Input.ChoiceSet”,

            “choices”: @{variables(‘HRReasons’)},

            “placeholder”: “Placeholder text”,

            “style”: “expanded”,

            “value”: “@{body(‘Get_Human_Resource_Rejection_Reasons_items_from_Rejection_Reason_List’)[‘value’][0][‘RR’]}”,

            “id”: “Choices”

          }

        ],

        “actions”: [

          {

            “type”: “Action.Submit”,

            “title”: “Reject”,

            “id”: “Rejected”,

            “style”: “destructive”

          }

        ]

      },

      “id”: “Reject”,

      “style”: “destructive”

    }

  ],

  “id”: “Adaptive_Card”

}

  •  Highlighted sections in the JSON above is the Dynamic content or expression that has been used to compose an adaptive card

12. Now we will post an Adaptive Card to the Human Resource Channel & Wait for a response

13. Once a new request is submitted by a requestor, an adaptive card will be posted in the Human Resource Channel in Operations Team letting the department know of a new request.

14. This is how the adaptive card will look like

I need a new laptop request clipped

15. The approver will be presented with 2 options; Approve or Reject

16. When the approver clicks Reject, they will be presented with department specific rejection reasons as filtered in the flow above.

Adaptive Card Reject HR

 

17. We will capture the response of the HR approver in the HRResponse variable

 

@{body(‘Post_an_Adaptive_Card_to_a_Human_Resources_Teams_channel_and_wait_for_a_response’)[‘submitActionId’]}

18. We will then update the status of the request with the response of the first approval.

 

19. If the HR Approver approves the request, it will then be forwarded to the IT Team for fulfilment.

20. For this, we will add a condition to validate the outcome of HR Approval. If the outcome of the HR Approval is Approve, then proceed further.

21. We will follow same steps from #11 to #20 for the IT Approval process.

22. Now that request has been approved by HR Team, it has now been forwarded to IT Team for fulfilment

23. A new adaptive card will be posted in the IT Team Channel as below:

I need a new laptop request clipped

 

24. If the request adheres to the set guidelines, the IT Team will approve the request otherwise the request will be rejected.

25. The IT Team will be presented with IT specific rejection reasons (as below):

Adaptive Card Reject HR

 

26. We will capture the response of the HR approver in the ITResponse variable

 

@{body(‘Post_an_Adaptive_Card_to_IT_Helpdesk_Teams_channel_and_wait_for_a_response’)[‘submitActionId’]}

27. We will then update the status of the request with the response of the next approval

update item with it approval clipped

Conclusion

We just saw how we can dynamically filter values and present them as dynamic options in an adaptive card.

In this demo the First Approval request goes to the Human Resource Channel in the Operations Team. Once the request is approved by the Human Resource Team, it is then forwarded for Second level Approval & Fulfilment to the IT Helpdesk Channel in the Operations Team. If the request is rejected by the HR, it will not be presented to the IT Helpdesk for fulfilment.

This kind of dynamic filtering can be achieved by using content from SharePoint Lists.

Akhil Ohri

Written By
Akhil Ohri
(Microsoft 365 Consultant)

Jasjit

Peer Reviewed By
Jasjit Chopra
(Microsoft 365 Consultant)

Leave a comment

Your email address will not be published. Required fields are marked *