Friday, September 17, 2021

Powerapps - Creating Autofill dropdown fields from SharePoint List

Scenario:

You have SharePoint Online list with four columns as given below and build a Canvas PowerApp to show the unique values of Country column in Country dropdown, and apply filter on State dropdown to only show those states from State column which are selected in Country dropdown. likewise do same thing with City dropdown. At last, when Country, State and City dropdowns are selected in respective dropdowns. Click on Show button to show/render HTML code from Description column.

Create SharePoint online list with name "Countries" using below columns and values.

Column Name    |    Type 

Country    |    Single line of text

State    |    Single line of text

City    |    Single line of text

Description    |    Multiple line of text + "Enhanced rich text with pictures, tables, and hyperlinks" enabled

and you entered the list items as below

Country     |    State    |    City    |  HTML

USA    |    Illinois    |    Chicago    |    "Hello Chicago - Click here"  

USA    |    Michigan    |    Kalamazoo    |    "Hello Kalamazoo - Click here"  

CANADA    |    Ontario    |     Oakville    |     "Hello Oakville - Click here"


Now follow below steps and create Canvas app.

1. Login to https://make.powerapps.com

2. Click on "Apps" option from left hand side and then click "Canvas"

3. On design screen, click on "Data" from left navigation and "Add data". You can search "SharePoint" data source to make data connect with above SharePoint list called "Countries"

4. On screen, add first dropdown box name ddCountry and use below function:

        fx=Distinct('Countries', Country)

        This will display all unique values from Country column in ddCountry dropdown

5. Add second dropdown box name ddState and use below function:

         fx=Distinct(Filter('Countries', CountryddCountry.Selected.Result), State)

        This will display all unique values from State column in ddState dropdown

6. Add third dropdown box name ddCity and use below function:

        fx=Distinct(Filter('Countries', Country = ddCountry.Selected.Result, 

          State = ddState.Selected.Result), City)

        This will display all unique values from City column in ddCity dropdown

7. Add HTML text box on screen and use below function:

        fx=Substitute(varURL, "/sites/", "https://<yourdomain>.sharepoint.com/sites/")

        User this method in case you want to replace the links from Description column

8. Add button name btnShow and use below function:

        fx=Set(varURL,First(Filter('Countries', Country = ddCountry.Selected.Result, 

        State = ddState.Selected.Result, City = ddCity.Selected.Result).Description);

9. Done