Hey everyone,

recently we had the problem of integrating (mass) data into a PowerApp. The goal was to to display, and more importantly, search for specific entries in a Dataverse table containing ~5.000 entries. Due to the limitation of only receiving very limited (default 100, up to 2.000) entries when using the default Dataverse Connector, the search does not work properly. By using the JSON Response from Power Automate via filtering within this Power Automate flow in combination with simple front-end logic, a reliable and performant display and searching for entries can be implemented.

Although there are several approaches present for receiving more than the default 2.000 entries (kudos to Matthew Devaney with Create Power Apps Collections Over 2000 Rows With These 4 Tricks (matthewdevaney.com)), it seems that the performance is limited in terms of speed and reliability.

In order to come up with a solution, the approach of using a JSON Response from Power Automate was used. Instead of searching for the search term in the Front-End, a small collection of entries is displayed as a placeholder. When searching for specific data entries, a Power Automate Flow is used, which is accessing the Dataverse and using ODATA filtering in order to perform the search “server”-sided, responding with only the valid entries. Via this approach, the performance increased — from 15secs to 1sec with the same search term used in the ODATA instead of the front-end.

Power Automate Flow

As described by Matthew, by using a Power Automate Flow, one can perform data search server-sided.

First, we start by creating a new CloudFlow named “Power_Automate_Flow”, with a starting trigger of PowerApps (V2). This is used in order to be able to provide the flow with a search term. Here, a new “String”-entry has to be added.

Second, we need to initilaize this variable. This is done by using the “Initialize Variable” step, naming it “Search_Term” by string/char type and using the value of PowerApps (V2).

Third, we connect to the respective Dataverse table and add the “List Rows” step with the respective table as described here: Create Power Apps Collections Over 2000 Rows With These 4 Tricks (matthewdevaney.com).

Last, by using the filtering within the “List Rows”, server-sided search queries can be performed. In the following article, an extensive collection of filtering can be found: Every Power Automate (MS Flow) Filter Query You Ever Wanted To Know As A Functional Consultant — DIY D365

In this case, by using the contains command, the desired operation can be performed:

contains(crfba_namekontrahent,'@{variables('Search_Term')}')

whereas “crfba_namekontrahent” is the logical name of the table column (which can be found in the Dataverse → Table → Edit Column → Advanced Options → Logical Name).

Integration of Power Automate Flow into Power App

Now that we have got the logic in place, we need to integrate this into the Front-End. For this, a simple gallery and a text-input field for the search-field can be used.

First, the population of the Gallery. For this, we are using following logic as the Gallery → Items:

If(
    Len(Text_Input.Text) > 0;
    colFlow_Comb;
    'DV Mass Data Tables'
)

Next, we navigate to the Text-Input → OnChange:

ClearCollect(
    colFlow_Comb;
    If(
        Len(Text_Input.Text) > 0;
        'Power_Automate_Flow'.Run({text: Text_Input.Text});
        'DV Mass Data Tables'
    )
)

With this logic, after entering the search term into text-input AND pressing the enter-key, the search query is used within the Flow, returning the respective data entries in the gallery.

Summary

With this simple technique, one is able to implement a performant server-sided search via a Power Automate Flow, integrated into Power Apps, in order to come up with a fast and reliable entries.

This blog is part of Power Platform Week.

About the Author

Just a computer science student doing JavaScript stuff and trying out new things.

Wepper, M. R., 2022, Power Platform— Performant Integration of Mass Data (> 4.000 entries) into PowerApps via PowerAutomate, Available at: Power Platform— Performant Integration of Mass Data (> 4.000 entries) into PowerApps via PowerAutomate | by Marcel-René Wepper | Dec, 2022 | Medium [Accessed on 19 January 2023]