cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamBates
Frequent Visitor

How to load and filter data from an SQL database to a collection

Hello,

 

I have an application that displays data in a table based on user parameters (text search filter, column sort filter, etc). 

 

Currently the application also stores the data in a gallery behind the scenes with the same parameters. It then uses this gallery to export the data into an excel sheet if it is desired by the user. What we have found is that due to how galleries load, the export only gives up to 100 rows of data.

 

I was advised to update the code so that it stores the data in a collection instead of a gallery, but am struggling with how I am able to populate and filter the data in a collection. 

 

Right now the gallery's data comes from code like this:

 

If(
    chkbox1.Value && chkbox2.Value, //Two boolean parameters
    Switch(
        varSortProperty,
        "Case1",
        SortByColumns(
            Search(
                VIEW_REQUESTS, //SQL table (view) that the data is pulling from
                txtBox1.Text, //Text search parameter
                "Column1",
                "Column2",
                "Column3",
                "Column4"
            ),
            "SORT", //Sort parameter
            If(
                sort,
                Ascending,
                Descending
            )
        ),

 

 

And then the export logic is like this. It calls a flow that parses the JSON and sends a GET request to populate an excel sheet, which then responds to the PowerApp and downloads to the users device.

 

UpdateContext({loader:true});

ClearCollect(
    exportCollection,
    RenameColumns(
        ShowColumns(
            Gallery.AllItems,
            "Column1",
            "Column2",
            "Column3",
            "Column4",
        ),
        "Column1",
        "Column 1",
        "Column2",
        "Column 2",
        "Column3",
        "Column 3",
        "Column4",
        "Column 4",
    )
);

Set(var_Export, exportCollection);
Set(varJson_Export, JSON(var_Export, JSONFormat.IndentFour));

UpdateContext({
    downloadFile:ExportDataToCSV.Run("Request",loggedInUserPin,varJson_Export).download
});
Launch(downloadFile);
UpdateContext({loader:false});
UpdateContext({successPopUP:true});

 

 

 

Any advise on how to populate and filter a collection to use that to send to the Flow so that I can get around the 100 row limit I am facing?

 

Let me know if I can provide more details.

 

Thank you,

Sam

0 REPLIES 0

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,891)