Datatable control issue while showing data more than 500 items

[manjotsingh057]

I have a SQL table with 16000 records.

Now i want to show  that data in data table control in PowerApps

Issue :

If records count crosses 500 then datatable is not able to load the data further. It will just show "loading data at the bottom of table just after showing approx. 200 records".

But if i use gallery control then it works fine.

This issue occurs only if we use filters / search expression with data in Items property of datatable.

However if we directly assign the datasource without any filter condition, it works fine.

Anyone experienced same issue with data table control ?

Is there anything that we can be done to avoid this issue ?

below is the screenshot of the issue.

[Mr-Dang-MSFT]

Hi @manjotsingh057,

It would be very helpful if you can provide your formula for filtering.

Though as a best practice for performance, you'll want to reduce the records you bring into your apps. Since you are on SQL, you can aggregate and join data into SQL Views which work very well.

Here's a blog post on creating Views of your data:

https://powerapps.microsoft.com/en-us/blog/using-sql-server-views-in-powerapps/

[manjotsingh057]

@Mr-Dang-MSFT

Here is the query that i am using.

This works fine with Gallery control but not with Datatable control.

Filter(
Filter(
Filter(
Filter(
Filter(
Filter(
Filter(
Filter(
'[dbo].[Projects]',
IsBlank(DDLField1.Selected.Value) || StartsWith(
ApplicationType,
DDLApplicationType.Selected.Value
)
),
IsBlank(DDLMarket.Selected.Value) || StartsWith(
Market,
DDLMarket.Selected.Value
)
),
IsBlank(DDLIndustry.Selected.Value) || StartsWith(
Industry,
DDLIndustry.Selected.Value
)
),
IsBlank(DDLTerritory.Selected.Value) || StartsWith(
Territory,
DDLTerritory.Selected.Value
)
),
IsBlank(DDLSourceSite.Selected.Value) || StartsWith(
SourceSite,
DDLSourceSite.Selected.Value
)
),
IsBlank(DDLCustomerType.Selected.Value) || StartsWith(
CustomerType,
DDLCustomerType.Selected.Value
)
),
TxtProjectNumber.Text in ProjectNumber
),
TxtCompany.Text in CompanyName
)

[manjotsingh057]

@Mr-Dang-MSFT,

were you able to verify the query ? Since the same query is working for Gallery control but data table control is not responding well for datasets that return more than 500 records. That's strange.

[Mr-Dang-MSFT]

Here's your formula formatted for readability:

Filter(
    Filter(
        Filter(
            Filter(
                Filter(
                    Filter(
                        Filter(
                            Filter(
                                '[dbo].[Projects]',
                                IsBlank(DDLField1.Selected.Value) || StartsWith(
                                    ApplicationType,
                                    DDLApplicationType.Selected.Value
                                )
                            ),
                            IsBlank(DDLMarket.Selected.Value) || StartsWith(
                                Market,
                                DDLMarket.Selected.Value
                            )
                        ),
                        IsBlank(DDLIndustry.Selected.Value) || StartsWith(
                            Industry,
                            DDLIndustry.Selected.Value
                        )
                    ),
                    IsBlank(DDLTerritory.Selected.Value) || StartsWith(
                        Territory,
                        DDLTerritory.Selected.Value
                    )
                ),
                IsBlank(DDLSourceSite.Selected.Value) || StartsWith(
                    SourceSite,
                    DDLSourceSite.Selected.Value
                )
            ),
            IsBlank(DDLCustomerType.Selected.Value) || StartsWith(
                CustomerType,
                DDLCustomerType.Selected.Value
            )
        ),
        TxtProjectNumber.Text in ProjectNumber
    ),
    TxtCompany.Text in CompanyName
)

My favorite thing about the Filter function is that you can specify multiple conditions using more commas. Each condition is required to be true to return a record since the comma is like an "And." Your formula can be rewritten as:

Filter('[dbo].[Projects]',
    IsBlank(DDLField1.Selected.Value) || StartsWith(ApplicationType,DDLApplicationType.Selected.Value),
    IsBlank(DDLMarket.Selected.Value) || StartsWith(Market,DDLMarket.Selected.Value),
    IsBlank(DDLIndustry.Selected.Value) || StartsWith(Industry,DDLIndustry.Selected.Value),
    IsBlank(DDLTerritory.Selected.Value) || StartsWith(Territory,DDLTerritory.Selected.Value),
    IsBlank(DDLSourceSite.Selected.Value) || StartsWith(SourceSite,DDLSourceSite.Selected.Value),
    IsBlank(DDLCustomerType.Selected.Value) || StartsWith(CustomerType,DDLCustomerType.Selected.Value),
    TxtProjectNumber.Text in ProjectNumber,
    TxtCompany.Text in CompanyName
)

There are many improvements to make to your conditions.

• I notice that you use StartsWith. Does the content of the dropdown menu only match a part of what the column starts with? Or is it an exact match? If it's exact, you should use an equal sign instead since StartsWith needs to do more work.
• For filtering the ProjectNumber and CompanyName, are you using a text input box? If so, you can reduce the text input boxes to just one and use the Search function.

You might make changes like this:

Search(
    Filter('[dbo].[Projects]',
        IsBlank(DDLField1.Selected.Value) || ApplicationType=DDLApplicationType.Selected.Value,
        IsBlank(DDLMarket.Selected.Value) || Market=DDLMarket.Selected.Value,
        IsBlank(DDLIndustry.Selected.Value) || Industry=DDLIndustry.Selected.Value,
        IsBlank(DDLTerritory.Selected.Value) || Territory=DDLTerritory.Selected.Value,
        IsBlank(DDLSourceSite.Selected.Value) || SourceSite=DDLSourceSite.Selected.Value,
        IsBlank(DDLCustomerType.Selected.Value) || CustomerType=DDLCustomerType.Selected.Value
    ),
    TextInput1.Text,"ProjectNumber","CompanyName"
)

This means, "Filter the projects according to the dropdown menus. Then search among that subset to show records that match the project number and/or company name to what you type into TextInput1."

[manjotsingh057]

Thanks @Mr-Dang-MSFT that will help me to formulate queries better.

but even with the query you have given, i am facing the same issue.

data table control is not able to load the data after few records.

[Mr-Dang-MSFT]

The data table works that way for performance reasons. As mentioned earlier, as a best practice, you should only bring in the data you need to see at a given moment. The more data you bring into an app, the more you impact the app experience. 

For your scenario, I would just use a gallery to unblock yourself. You can make a gallery work the same way as a data table. In fact, you can make it even better by using text input fields and patch statements so it can update.

https://powerapps.microsoft.com/en-us/blog/editable-tables/

If you are bringing the 16,000 records in so you can use one of the aggregate functions (sum, average, etc), it's not worth the performance hit. You should instead create the common SQL Views you expect.

[manjotsingh057]

@Mr-Dang-MSFT

I agree we should get the data that we want to see but if i have 16000 records in Table i cannot get less that 500 records with few filters available.

If i get more than 500 records after applying all the required filters and data table cannot show it then that is huge roadblock in the implementation.

If i use gallery in place of data table i have a disadvantage here. The number of columns that i can show in gallery control will be limited because of horizontal screen space available since headers will be fixed and we cannot afford to have a scroll in this case.

[Mr-Dang-MSFT]

@manjotsingh057,

You can increase the limit from 500 up to 2000 by going to File > App Settings > Advanced Settings 

That said, if you bind the data table and gallery directly to your filter statement, it will still only reveal the first 100 until you scroll down.

I'd like to understand the purpose of your app a little bit more if you can share.