Surpassing the 500 limit with SQL + Collection

[Gjakova]

Hi there, so I've been fuming about this for the last 8 hours and I just can't get it to work...

My PowerApp is connected to a Microsoft SQL database and I'm using a collection in PowerApp so that I can join columns from the other SQL db tables inside the column.

I read the following blogs: https://blog.coeo.com/davidmorrison/powerapps-500-record-limit-delegation-and-how-to-work-around-it and tried the solution of Mr Dang https://powerusers.microsoft.com/t5/Building-Power-Apps/500-item-limit-in-CDM-entity-search-filter-n... but I just can't get it to work.

I have two scripts, one script works fine, but the LookUp values won't work, so in my table I only see the Foreign Keys.

The other script repeats itself, so it doesn't loop correctly. It shows me 500 rows with the LookUp values and 500 rows without.

Here is script #1, where the LookUps won't work:

ClearCollect(CountriesMemory, '[dbo].[Countries]');
ClearCollect(StatusMemory, '[dbo].[Statuses]');
ClearCollect(
    ContractMemory,
    AddColumns(
        '[dbo].[Contracts]',
        "CountryName",
        LookUp(CountriesMemory, Fk_Country =CountriesMemory[@Pk_Country], Country_Name),
        "StatusName",
        LookUp(StatusMemory, Fk_Status =StatusMemory[@Pk_Status], Status_Name)
    )
);

//

UpdateContext({firstrecord: First('[dbo].[Contracts]')});
UpdateContext({lastrecord: First(Sort('[dbo].[Contracts]',Pk_Contract,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.Pk_Contract-firstrecord.Pk_Contract)/500,0)});

ClearCollect(iter,
	AddColumns(AddColumns(Filter(NumbersTable,Numbers<=maxiter),"min",(Numbers-1)*500),"max",Numbers*500)
);

Clear(ContractMemory);
ForAll(iter,
	Collect(ContractMemory,
		Filter('[dbo].[Contracts]',Pk_Contract>=firstrecord.Pk_Contract+min && Pk_Contract<firstrecord.Pk_Contract+max)
	)
)

and here is script #2 (where it gets repeated both versions (foreign keys and lookups):

ClearCollect(CountriesMemory, '[dbo].[Countries]');
ClearCollect(StatusMemory, '[dbo].[Statuses]');

//

UpdateContext({firstrecord: First('[dbo].[Contracts]')});
UpdateContext({lastrecord: First(Sort('[dbo].[Contracts]',Pk_Contract,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.Pk_Contract-firstrecord.Pk_Contract)/500,0)});

ClearCollect(iter,
	AddColumns(AddColumns(Filter(NumbersTable,Numbers<=maxiter),"min",(Numbers-1)*500),"max",Numbers*500)
);

Clear(ContractMemory);
ForAll(iter,
	Collect(ContractMemory,
        AddColumns(
            '[dbo].[Contracts]',
                    "CountryName",
                    LookUp(CountriesMemory, Fk_Country =CountriesMemory[@Pk_Country], Country_Name),
                    "StatusName",
                    LookUp(StatusMemory, Fk_Status =StatusMemory[@Pk_Status], Status_Name)
                )
            );
		Filter('[dbo].[Contracts]',Pk_Contract>=firstrecord.Pk_Contract+min && Pk_Contract<firstrecord.Pk_Contract+max)
	)

I tried multiple variations but it just won't work. Does someone can help me out? I am presenting this data into a data table. Maybe I need to do look up values in my Columns from the data table, if so, how would that then work for my ComboBox filters since they make use of ContractMemory.

Thanks in advance!!!

[v-bofeng-msft]

Hi @Gjakova :

   The point is that "Collect" is not a delegable function, it means that "ClearCollect(CountriesMemory, '[dbo].[Countries]')" can only load up to 500 records into CountriesMemory by defalut.In addition, due to delegation issues, there are only up to 500 records in StatusMemory.

  I have two solutions to solve this problem:

Solution1:If the number of records in your data source is less than 2000, you can increase the delegation limit to get the correct result.

Solution2: Using delegateable formulas to load data into collections in batches.

I assume there is a number type column in '[dbo].[Countries]' named 'thenumber', the formula should be:

ClearCollect(CountriesMemory, Filter('[dbo].[Countries]',thenumber<2000);
Collect(CountriesMemory, Filter('[dbo].[Countries]',thenumber>=2000 && thenumber<4000);
Collect(CountriesMemory, Filter('[dbo].[Countries]',thenumber>=4000 && thenumber<6000);
……

Best Regards,

Bof

[Gjakova]

Hey @v-bofeng-msft thank you for your response, but I don’t think that’s the problem.

Both tables which I use a LookUp value for have 50 and 200 records, so that is all fine. I think that the main problem is the way that I have written my two script. The first one works, but I think that when I use Clear(ContractMemory) for the 2nd time, that the LookUps are removed, but when I applied the Lookups in the 2nd script later on, it didn’t loop and it showed me the rows with the Foreign Keys + LookUps

[v-bofeng-msft]

Hi @Gjakova :

Sorry,I'can not understand the purpose of script #2.Why create a loop to add the same table to a collection multiple times? And what is the role of the latter Filter? What function do you want to achieve?

Best Regards,

Bof

[Gjakova]

Hi @v-bofeng-msft , sorry my Power Apps knowledge is not that good, so perhaps I'm doing things which I'm not supposed to. But I am using a SQL database and I have multiple tables in it. One of the tables is called Contracts. The contract table also contains some foreign keys to other tables such as Country and Memory.

For the screen that I am creating in PowerApps, I made a collection which also translates the foreign keys to the names of the tables. Which is:

ClearCollect(CountriesMemory, '[dbo].[Countries]');
ClearCollect(StatusMemory, '[dbo].[Statuses]');
ClearCollect(
    ContractenMemory,
    AddColumns(
        '[dbo].[Contracts]',
        "CountryName",
        LookUp(CountriesMemory, Fk_Country =CountriesMemory[@Pk_Country], Country_Name),
        "StatusName",
        LookUp(StatusMemory, Fk_Status =StatusMemory[@Pk_Status], Status_Name)
    )
);

 This works good, but now I have the problem that my Contracts table in SQL contains almost 10.000 rows (and it keeps growing every day). So at the end of 2021 it could be 15.000.

Since I can only retrieve max. 500 contracts I tried to create a loop, so that PowerApp will retrieve contract #1 - #500 and then retrieve #501-1000, then #1001 - 1500 etc. until it retrieves all the rows from my contract table.

I didn't know how to do that so I read the following blogs:

- https://blog.coeo.com/davidmorrison/powerapps-500-record-limit-delegation-and-how-to-work-around-it
- https://powerusers.microsoft.com/t5/Building-Power-Apps/500-item-limit-in-CDM-entity-search-filter-n...
- https://poszytek.eu/en/microsoft-en/office-365-en/powerapps-en/overcome-2000-items-limit-using-power...

I applied the formula of the first blog with the help of Mr. Dang's comment, but it seems that I'm doing it wrong. I don't understand too much of PowerApps yet to troubleshoot/find the errors myself, therefore I asked here to see if someone can help me with solving this issue I'm facing.

Script #1 and #2 is what I have tested, but I am not sure if I did it correctly. Hope this explains my issue better @v-bofeng-msft 

[timl · Accepted solution]

Hi @Gjakova 

If your aim is to join several tables, the answer is to do this through a SQL Server view, instead of using collections from Power Apps.

Joining the data from within Power Apps is highly inefficient, particularly when you're retrieving several thousand rows, incurring the bandwidth of that data transfer, just in order to call LookUp on a client device which could be limited in memory and speed (eg, a slow mobile phone).

If you create the view in SQL like so, you'll be able to query this view in a delegable way using the Filter and Search functions from within Power Apps, and you won't have any of these problems with collections/ClearCollect limitations.

CREATE VIEW ContractenMemory
AS
SELECT ctr.*, cont.Country_Name, stat.Status_Name
FROM
[dbo].[Contracts] ctr
JOIN [dbo].[Countries]' cont
ON ctr.Fk_Country = cont.PK_Country
JOIN [dbo].[Statuses] stat
ON ctr.Fk_Status = stat.PK_Status

[v-bofeng-msft · Accepted solution]

Hi @Gjakova :

  I agree with @timl , because your number of records is too much, and the amount of calculation is also very large. So no matter what formula is used, performance problems will inevitably be encountered.

  I suggest you try @timl 's suggestion and create a view as the data source.

Best Regards,

Bof

[Gjakova]

Hi @v-bofeng-msft and @timl, thank you both for your help, the VIEW solution sounds promising. Since I don't work that much with SQL I have a question though...

As @timl suggest, I should create a view which is (almost) the same as is provided above. Once that view is inserted into my SQL database, I could call it automatically within Power Apps (no need for Power Automate or such?).

The view basically provides the same function as the script with I had written in my third comment, I assume?

And since Power Apps can load more than 2000+ rows in SQL with ease, I can still filter the data table with my ComboBoxes etc?

Am I correct or am I missing something. Sorry for the rookie questions, but just from your posts I already learned quite a lot!

[timl]

Hi @Gjakova 

Yes, once you create a view in SQL Server, you would add it in the same way as you would add a table. There's no need for Power Automate.

The view that I posted above retrieves the contents of the contracts table, and includes the corresponding descriptions from the countries and statuses tables, so I think that's what you're looking for in your third script.

Let's suppose you set the items of a gallery control to a view that could return 15,000 rows. By default, the gallery control will display a limited number of records, and will automatically retrieve additional batches of records when the user reaches the bottom of the gallery. This performance optimisation means that Power Apps doesn't retrieve all 15,000 records upfront, which results in a much faster experience.

In the case of a combo box, we would typically enable the built-in search capabilities of the combo box control. When the Items property of a combobox is set to a SQL Server view, Power Apps delegates the search criteria that the user enters to SQL Server, which means that Power Apps retrieves only the required records, and no more. Again, this feature enables users to access all available records in the quickest way possible.

[Gjakova]

Hi @timl, I accepted your solution since I can retrieve more than 500 rows now + I think I'm going to use Views much more often. My early test results show that it also work quite fast. I do have (I hope) one last question though if you don't mind...

On my screen I have a Data Table and when I just do Items = sqlView then it shows me all my contracts (2500+).

But I want to filter my Data Table of course with multiple filters (one dropdown and the rest are ComboBoxes).

I then type in Items the following:

Items =

Filter(
    sqlView,
    Dropdown_Filter_10.SelectedText.Value = Type,

    ComboBox_Filter_39.Selected.Result = Country_Name || IsBlank(ComboBox_Filter_39.SelectedItems) || IsEmpty(ComboBox_Filter_39.SelectedItems),
  
    ComboBox_Filter_40.Selected.Result = Status_Name || IsBlank(ComboBox_Filter_40.SelectedItems) || IsEmpty(ComboBox_Filter_40.SelectedItems),

    ComboBox_Filter_41.Selected.Result = ContractNumber || IsBlank(ComboBox_Filter_41.SelectedItems) || IsEmpty(ComboBox_Filter_41.SelectedItems),

    ComboBox_Filter_42.Selected.Result = Year(PurchaseDate) || IsBlank(ComboBox_Filter_42.SelectedItems) || IsEmpty(ComboBox_Filter_42.SelectedItems)
    
)

 Once I apply this filter around my sqlView (which is how my View is called in SQL), then it can't retrieve more than 500 rows anymore.

---

@timl wrote:

If you create the view in SQL like so, you'll be able to query this view in a delegable way using the Filter and Search functions from within Power Apps, and you won't have any of these problems with collections/ClearCollect limitations.

---

But based on what you said earlier I thought that this would work without any problem. Or do I understand it wrong? (If so sorry, English is not my main language). Do I need to apply another solution to filter my data table or am I doing something wrong.

Again, sorry for the spam and thanks so much for your help so far! 🙂