Use SharePoint column values for Adaptive Card Choice

[anthonys123]

Our main document library has columns for Client, Project, Location, etc. When uploading any new files to any SharePoint site, I want to have the option to pick from a list of existing clients found in the main document library. These two locations may have nothing to do with each other - I just want to utilize an existing, regularly updated value set. 

So, when a file (invoice, packing slip, receipt, etc) is uploaded to an unrelated SharePoint Site library, an Adaptive Card gets sent to a manager via Teams. The manager can set the metadata for this file using values from another SharePoint site. 

Or, if it's easier, upload the file to the Document Library to utilize the existing vales in the dropdown, then move that file to the appropriate location while retaining those values. Either way, how can I grab these column values and put them in a dropdown list in an Adaptive Card?

For reference, this is what the current Adaptive Card looks like. 

[DamoBird365]

Hi @anthonys123 

So, a choices block in an Adaptive Card looks like the following:

            "choices": [

                {

                    "title": "Five Mins",

                    "value": "5"

                },

                {

                    "title": "Ten Mins",

                    "value": "10"

                },

                {

                    "title": "Fifteen Mins",

                    "value": "15"

                }

            ]

Basically an object of choices with titles and values (which could be the same).

You need to get your choices from SharePoint and can do this with an API call to SharePoint.  I couldn't work out how to filter the results from the API (so you might be able to simplify the next bit) so I filtered the array for the GUID of the choices column that I wanted the options of instead.

I've turned this:

Into this:

Here is the solution:

 

I really hope this was what you were looking for 🙂 it was a good challenge putting it together.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

[anthonys123]

This is super interesting and looks fun, but I'm not sure it will work. The values from SharePoint aren't choices, they're text fields. I want to pull all the values from one column and display them in a dropdown list. Sorry for not being clear about that. 

[DamoBird365]

@anthonys123 

**bleep**! I was super chuffed with that one 🙂 but no problems.  Are you saying that you will have many items and you want to summarize the distinct values in an individual column?

I.e. if we have a list

ID  ColumnNew

1    Monday

2    Tuesday

3    Friday

4    Monday

5    Monday

You would want the dropdown list in the adaptive card to be Monday, Tuesday, Friday?  As from the 5 items, these are the distinct values?

Damien

[anthonys123]

Yep. That's about right. Although there will also be a lot of blank values. 

ID  ColumnNew

1    Monday

2    Tuesday

3    Friday

4   

5    Monday

6   

7    Tuesday

8   

Well, I did learn a little something from your previous effort. I think I can use that for something else 🙂  I really appreciate it! 

[DamoBird365]

Hi @anthonys123 

OK, my list has the following:

Output is :

Do you think you can take that to an adaptive card?

Damien

[PrinzPasta]

Hi Damien! 

First of all many thanks for your solution! It addresses exactly my issue. 🙂
I have a question in regard; How did you get the GUID of the choices column in your sharepoint list? 
I see the GUID from my list in the address-bar, but I did not find any trick to get the GUID of my choices column. 

Would appreciate a quick hint on that 🙂

Cheers,
Mirco

UPDATE 03.03.2022

I just found a solution with a total different approach: 
Please see this solution for further information. Worked for me like a charm and took me 2min to implement (don't forget to add spaces between the values in your regular expression ;-)). 

Solved: Re: Read values from multi selected choice column - Power Platform Community (microsoft.com)

[MaDW1]

Thank you for your inputs - they helped a lot 🙂
I used this solution Solved: Get Sharepoint List Choice Column values - Power Platform Community (microsoft.com)

My SharePoint column:

My Power Automate Flow:

My result: