Connecting Datasource Dynamically In PowerApps Using Power Automate - Part One

As developers, we always want to develop something which could be reusable whether it’s done in programming or with Rapid Development Tools like PowerApps. One such requirement I came across was to have the ability to connect with different datasources dynamically from PowerApps. Natively, PowerApps does not support connecting to different datasources at runtime. However, with a combination of Power Automate, we should be able to achieve the said requirement.

 

In my scenario, I am developing a quiz application. There are different types of quizzes, each stored in a separate SharePoint List. User chooses the quiz type they want to take and based on the selection data is pulled from their respective SharePoint List.

 

I will be completing this app in two modules, 

• Module 1: Loading datasources into a Choice/Dropdown Control
• Module 2: Loading content from a selected datasource

Getting Started,

 

I will be using two lists to explain this scenario. Both the lists will be using the same schema. So let’s create our first list called “General Quiz”. All the fields in this list will be marked as “Required”.

 

General Quiz
Column Name	Type
Title	Single Line of Text
Option 1	Single Line of Text
Option 2	Single Line of Text
Option 3	Single Line of Text
Option 4	Single Line of Text
Correct Answer	Single Line of Text

 

Now, we can create our 2^nd list, using this list as a template. So, from “Site Contents -> New -> List”, select “From existing list”. Give new list a name, like “History Quiz”, select “General Quiz” as template and click on “Create”.

 

 

Since we will not be using any attachments on these lists, we can set the “Attachments” setting to “Disabled”. List Settings -> Advanced Settings -> Attachments.

 

 

Now we have our lists setup, before we jump into creating the Quiz App with Dynamic Datasources, we need to collect the listnames that will be displayed as datasources in PowerApp.

 

Collect List Names

 

We will collect the listnames using PowerAutomate.

1. Navigate to here.
2. Click on “Create” and select “Instant flow”

3. Give flow a name, like, “QuizBuzz_GetAllLists”. Select “PowerApps” as trigger and click on “Create”.

4. Add a New Step -> “Get Lists”, and configure it with the Site URL where all quiz lists are created

5. Next, initialize a String variable and name it “Lists”

6. Next, loop through all the items from “Get Lists” action and append the “Display Name” of all lists into “Lists” variable. Make sure to have the list names separated by a special character like semi-colon (;) or hash (#)

7. There will be an extra character at the end of the appended string, so we will use a “Compose” operation to compose the final string that will remove the extra character. Expression used in this operation is as follows,
   
   substring(variables('Lists'),0,sub(length(variables('Lists')),1))
   

 

8. Finally, the output of “Compose” action will be sent to the calling app in PowerApps. Add a step “Respond to a PowerApp or Flow”, this action will allow us to add an output. We will choose “Text”, provide an appropriate name to the response variable and assign output from “Compose” action

 

 

 

Note

Currently, there is no option to send an Array or Collection to PowerApps. Hence, we pass the output as String and create collection in PowerApps after receiving data from Flow.

 

Creating collection in PowerApps

 

So far, we have created a string using PowerAutomate and sent it to PowerApps. Now, we need to convert this string into a collection, that will be referred as a datasource for our Quiz App. This app will be a stand-alone canvas app. So, let’s start with a Blank Canvas App.

1. Navigate to here.
2. Click on “Create” and select “Canvas app from blank”. Provide a name to your app, select “Tablet” format and click on Create button.

3. First, we will need to associate the flow in PowerApps. So, select the Form in the tree view, then click on "Action" -> "Power Automate". Now, select the flow that we had created earlier.

4. Next, in the “App” -> ”On Start”, use the Run command on the flow and collect the result in a variable. Since, the result is in String format, we will convert it into a collection by splitting based on the special character provided

5. Now, we have got all the listnames as our datasource in collection. Next, we will load this collection in a dropdown, so that the users can select their datasource.
6. On the Screen, add a Label and a Dropdown Control. Provide the appropriate text for the Label control. For Dropdown Control, set the "Items"property to the collection created in the “OnStart” event of the app

7. You will notice that dropdown shows all the listnames from Sharepoint Site

Final Outcome

 

So far, we have been able to bring a list of datasources we want to consume in our application. In my next article, we will conclude by loading the contents from these datasources dynamically.