Power Apps/ Automate – Add choice on SharePoint column field using Canvas App

As a business entity, we want to minimize error modifying lists in choice fields directly on our database (SharePoint).

As a solution – we opt to add lists on our choice fields dynamically through canvas app with the help of power automate which does not require Power Platform premium license .

Step 1:

For this tutorial, we would be using a SharePoint site named Power Platform Portfolios.

Create a new list by following below steps:

1. On the Power Platform Portfolios site, Click New.
2. Choose List.

Step 2:

Select Blank list as our data for the list

Step 3:

Give your list a name by following below steps:

1. Name the list Grocery Items
2. Check the Show in site navigation to easily create a shortcut to the list
3. Select Create button.

Step 4:

Add a new choice field by following below steps:

1. Select Add columns
2. Select Choice.
3. Click Next button.

Step 5:

1. Set the choice field Name

Name

Items

2. Type the Choices items.

Choices

Grocery Item 1

Grocery Item 2

Grocery Item 3

3. Click the Save button.

Step 6:

Take Note of the Field Title and Field Internal Name by following below steps:

1. Take note of the Field Title Items which can be seen on the default view “All Items View”.
2. Select the settings button, then choose List settings.
3. Click the column field Items
4. Take note of the Field Internal Name Items which can be seen on the field properties by selecting settings and the field name “Items“.

Field Title

Items

Field Internal name

Items

Step 7:

Create a blank canvas app from Power Apps. From the Power Apps studio, add SharePoint data by following below steps:

1. Click Data.
2. Click Add data.
3. Search for SharePoint.
4. Select SharePoint.

Step 8:

Below Recent Sites, select the site created.

Step 9:

Add the new list as data source by following below steps:

1. Choose the list created named “Grocery Items”
2. Click Connect.

Note: The Grocery List items should now be connected.

Step 10:

On the Canvas App, Insert a Label and name it Label2. Change the following properties:

Text

"Items"

Size

20

FontWeight

FontWeight.Bold

Step 11:

Insert a ComboBox and name it ComboBox1. Change the following properties:

Items

Sort(Choices([@'Grocery Items'].Items),Value,SortOrder.Ascending)

DisplayFields

["Value"]

SelectMultiple

false

Reset

locreset

Note: It will prompt an error due to locreset not being declared yet. We will add this code on the button after this step.

Step 12:

Insert a Button and name it Button1. Change the following properties:

Text

"Add Items"

OnSelect

//Add item to SharePoint Field Choice Column

AddChoice.Run(
    ComboBox1.SearchText,
    "[Field Title]","[Field Internal Name]","Grocery Items",
    "https://<yoursite>.sharepoint.com/sites/<sitename>");
    Notify("Choice has been added",NotificationType.Success);

//start timer to refresh
UpdateContext({locstarttimer:true,
            locaddeditem: ComboBox1.SearchText}  
            );

//reset TextInput1
UpdateContext({locreset: true});
UpdateContext({locreset: false});

Note: Go back to Step 6 for the Field Title, Field Internal Name. Replace <yoursite> and <sitename> based on your SharePoint URL. It will prompt an error since we have not created the flow yet. Ignore the error and let us proceed on creating the flow on the next steps.

DisplayMode

If(
    Or(
    IsBlank(ComboBox1.SearchText),
    ComboBox1.SearchText = "",
    !IsBlank(
        LookUp(Choices([@'Grocery Items'].Items),
        Lower(ComboBox1.SearchText) = Lower(Value),Value))
    ),
        DisplayMode.Disabled,
        DisplayMode.Edit
    )

Step 13:

Insert a Timer and name it Timer1. Timer will refresh the database until the new value is reflected on the app to avoid manual refresh. Apply the following properties:

Duration

3000

OnTimerEnd

Refresh('Grocery Items');

If(
    !IsBlank(
        LookUp(Choices([@'Grocery Items'].Items),locaddeditem in Value)
            ),
    
        UpdateContext({locstarttimer:false,
                        locaddeditem: ""}  
                        );
);

Repeat

true

Start

locstarttimer

Visible

false

Note: This property will make the timer hidden

Step 14:

Add Power Automate flow on Canvas App by doing the following:

1. From the left side bar, click the ellipsis
2. Choose Power Automate

Step 15:

Create a flow from blank by doing the following:

1. On the Power Automate section, select Add flow.
2. Choose Create new flow. Create your flow dialog box will appear.
3. Select Create from blank.

Step 16:

Rename the flow to Add Choice

Step 17:

From the Power Apps (V2) Trigger, click Add an input, Choose Text and type the following name of inputs below:

User Inputs

NewChoice

FieldTitle

FieldInternalname

ListName

Site

Step 18:

Click New Step, then search and choose Initialize variable.

Name

var_choices

Type

Array

Step 19:

Click New Step, then search and choose Send an HTTP request to SharePoint. On the Site Address, select the dropdown and select Enter custom value.

Site Address

Method

GET

Uri

_api/web/lists/GetByTitle('@{triggerBody()['text_3']}')/
fields?$filter=EntityPropertyName eq '@{triggerBody()['text_2']}'

Step 20:

Click New Step, then search and choose Compose then paste below input to expression tab:

Inputs

body('Send_an_HTTP_request_to_SharePoint')?['d']?['results'][0]?['Choices']?['results']

Step 21:

Apply the following steps on the New Step.

1. Add an action Apply to each
2. Add dynamic content Outputs from Compose action.
3. Add an action Append to array variable and add apply below contents

Name

var_choices

Value

@{items('Apply_to_each')}

Paste this code to Value input field

Step 22:

Add an action Append to array variable outside the loop and apply below contents:

Name

var_choices

Value

@{triggerBody()['text']}

Paste this code to Value input field

Step 23:

Click New Step, then search and choose Send an HTTP request to SharePoint. On the Site Address, select the dropdown and select Enter custom value.

Site Address

Method

PATCH

Uri

_api/web/lists/GetByTitle('@{triggerBody()['text_3']}')/
fields/GetByTitle('@{triggerBody()['text_1']}')

Headers

accept

application/json;odata=verbose

Content-Type

application/json;odata=verbose

IF-MATCH

*

Body

{
'__metadata':{
  'type': 'SP.FieldChoice'
},
  'Choices':{'results': @{variables('var_choices')} }
}

Step 24:

Do the following steps to complete the flow.

1. Save the flow by clicking the save button.
2. Close the flow. You will be directed to canvas app.

Test the flow by accessing the App and typing new item, then click the Add Items Button.

You can check if the item is already added both on the ComboBox1 and SharePoint field Items