cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manjotsingh057
Level: Powered On

Filter sql table data based on combo box

Hi Team,

 

I am trying to filter data based on multiple selections in a  combo box.

User can select multiple vaues in combo box and then sql table should return rows containing those values.

 

What should be the expression for filter ? Also can it be used if sql table contains 10,000 records.

 

Thanks in advance!!!

 

5 REPLIES 5
Super User
Super User

Re: Filter sql table data based on combo box

Hi.

 

You can use 'in' but this will unfortunately only teturn 2000 records as this is not a delegatable function.

 

Are you using SQL on premises or on Azure? If Azure, you can use Flow to issue a SQL query to the SQL server and return all the data that you want.

 

Here is a great video from @Mr-Dang-MSFT

https://www.youtube.com/watch?v=K_H5r0nzwy4&t=383s

manjotsingh057
Level: Powered On

Re: Filter sql table data based on combo box

Thanks @DavesTechTips,

Unfortunately we are using on premise sql enviroment.

Super User
Super User

Re: Filter sql table data based on combo box

I am sorry to give you the bad news, but unfortunately you can't issue SQL queries to on prem SQL servers yet. I am sure that functionality will come soon as this would be a massive improvement on how big data sets are worked with in PowerApps.

 

How many columns in the database? Consider using a view in SQL with very basic information that could easily 'cached' to a collection in PowerApps. Then use the filter 'in' formula on the PowerApps collection that works great. If you need some additional information about the filtered records, these can be retrieved from the actual table on a per ID basis.

 

Please let me know if you don't come right.

 

manjotsingh057
Level: Powered On

Re: Filter sql table data based on combo box

@DavesTechTips

 

I tried loading items into collection but it returns only 500 records to collection which i can increase it to maximum of 2000.

 

But i have 9000 records in SQL table at the moment. Is there a way to get that into collection ?

Super User
Super User

Re: Filter sql table data based on combo box

Hi there.

 

Yes, you can loop through the data 2000 at a time. Please see below for an example of how you could cache the data in batches of 2000. The below example will do up to 10 iterations of batches of 2000.

 

Remember to update the datasource names and ID columns.

 

Refresh('[dbo].[TableName]');
               
        Concurrent(
            UpdateContext(
                {
                    MinRecordID: First(
                        SortByColumns(
                            '[dbo].[TableName]',
                            "ID",
                            Ascending
                        )
                    ).ID
                }
            ),
            UpdateContext(
                {
                    MaxRecordID: First(
                        SortByColumns(
                            '[dbo].[TableName]',
                            "ID",
                            Descending
                        )
                    ).ID
                }
            )
        );
        UpdateContext(
            {
                NumberOfPasses: RoundUp(
                    (MaxRecordID-MinRecordID)/2000,
                    0
                )
            }
        );
        ClearCollect(
            colNumbersTable,
            {Number: 10},
            {Number: 9},
            {Number: 8},
            {Number: 7},
            {Number: 6},
            {Number: 5},
            {Number: 4},
            {Number: 3},
            {Number: 2},
            {Number: 1}
        );
        Clear(localDataTable);
        ForAll(
            Filter(
                colNumbersTable,
                NumberOfPasses >= Number
            ),
            Collect(
                localDataTable,
                Filter(
                    '[dbo].[TableName]',
                    (ID) >= MinRecordID + ((Number-1) * 2000) && ID <= MinRecordID + (Number * 2000)
                )
            )
        )

This works well, but keep the following in mind:

a) Passing SQL queries form FLOW is a LOT faster. See if you can get Azure in SQL.

b) We would typically user the above code from a timer, and call the timer to trigger if we need to refresh the data.

c) Passing SQL queries form FLOW is a LOT faster. See if you can get Azure in SQL. (I have to say this again)

d) Functions like AddColumns are not delegatable, so you can't change the datasource much at the time of running the Collect on it to cache the data.

e) If you are working with many columns, create a view in SQL that only has the columns that you want to cache.

f) Depending on the information you want to display in a gallery, you might want to consider paginating the gallery to allow users to select how many records they want to display, and which page they want to view of the records. This is a little more complex and I will have to do some prep before sending you instructions on this. See below example of this.

2019-01-22_17-00-41.jpgPaginating PowerApps Gallery

 

 

Let me know if you don't come right.

 

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,093)