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

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.

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)
      1. 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:
      1. Department (We have renamed Title column to Department)
      2. Rejection Reason (Single line of text)

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

2. 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)

3. 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:

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.

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

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

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.

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.

• 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

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.