Can I only Load Rows with a specific value?

(0) Share

Report

Posted on by DCCCPE

Hello,

I've come up against the dreaded 2000 entries limit and am unsure how to get around my particular issue.

I have a data set of tasks and I've created a canvas app that shows all existing tasks on a Sharepoint dataset. It allows users to view and edit those, as well as submit new tasks direct from the app.

This was working fine...until I breached the 2000 entry limit, and now obviously it wont load the new entries into the app.

However, I don't actually want to load the entire data set, but only those rows that have a particular choice column, namely whether the task is complete, marked as "No". This would result in only 1040 entries. I'm filtering the data currently so app users don't see completed tasks, but I assume they're still being pulled by the app.

So my questions are, would some how filtering what entries the app pulls solve my 2000 entry woes, and if so...how on earth do I do that?!

Yours faithfully,

A Rank Amateur

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (0)

Sort by

Pstork1 69,649 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

What type of column is the Task complete column? Is it a choice, yes/no, or just a text column?

Was this reply helpful? Yes No
DCCCPE 14 on at

Like
a
(0)

Report
Copy link

Link copied!

It's a choice column

Was this reply helpful? Yes No
Pstork1 69,649 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Use something like the following filter in the Items property of the gallery

Filter(<<DataSourceName>>, <<ColumnName>>.Value = "No")

Was this reply helpful? Yes No
DCCCPE 14 on at

Like
a
(0)

Report
Copy link

Link copied!

I already have the following in the Items property, but I still have the issue with it not pulling entries over 2000.

Search
(Sort
(Sort
(Filter('<<DATASOURCE>>','<<COMPLETECOLUMN>>'=false, <<LOCATION>>=Filter1.SelectedText.Value),
<<LOCATION>>,Ascending),
<<EXACTLOCATION>>,Ascending),
Search1.Text,"<<EXACTLOCATION>>")

I use a drop down to filter the results by the LOCATION (town) and a text box as a Search for the EXACTLOCATION (Street)

Was this reply helpful? Yes No
Pstork1 69,649 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Since the Completed column is a Choice column you can't filter on the column itself. You have to filter on the Value property of the column. When filtering on a complex column the delegation will default to the data type of the leaf property being tested. Value will be a string, not a Boolean, which is what you are testing for now.

In terms of the Search() outside. The key will be to make sure that the inner filter() returns a data set lower than the data row limit for the app. Filtering on the value of a Choice field is delegable so that should work.

Was this reply helpful? Yes No
DCCCPE 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Pstork1,

Thank you for your help so far, just had a few days away, back to the challenge! So I made a mistake in the information I gave you, it isn't ma choice column but instead a toggle. So going back to how I have it currently...

Search
(Sort
(Sort
(Filter('<<DATASOURCE>>','<<COMPLETETOGGLECOLUMN>>'=false, <<LOCATION>>=DropDown1.SelectedText.Value),
<<LOCATION>>,Ascending),
<<EXACTLOCATION>>,Ascending),
TextInput1.Text,"<<EXACTLOCATION>>")

This is working, but isn't delegating the Choice column filter, but is instead pulling the entire list and filtering it on the app. However as you said I tried adding "'<<COMPLETETOGGLECOLUMN>>'.value=false" instead, and the app now returns 0 results.

Any thoughts as my head hurts now! 😵

Was this reply helpful? Yes No
Pstork1 69,649 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

If CompleteToggleColumn is a Yes/No column than what you have should work. The other possible issue is the use of DropDown1.SelectedText.Value inside the Filter. It would be better to save that as a variable and use the variable instead of the reference to the control when delegating the formula. I would also try it first without the Search and Sorts just to make sure the filter is correct and delegable.

Finally, there is a difference in a SharePoint list when using a Yes/No column that isn't required. The column can then be false for two different reasons. Either its been set to True and edited to false or it just defaulted to false. Unfortunately, if it defaults to false it often reports the value as null, not false.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@DCCCPE

My suggestion is to change it in SharePoint from a yes/no column to single line of text and filter it directly on the text value. Here is my suggestion https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-PowerApps/ba-p/848842 Not everyone agrees with all of them but they work for me.

Was this reply helpful? Yes No
DCCCPE 14 on at

Like
a
(0)

Report
Copy link

Link copied!

So I have success..sort of!

As Pstork1 suggested I've stripped the formula back to as below and it is pulling the records using delegable processes! Woo!

(Sort
(Filter
('DataSource','CompletedColumn'=false),
'ExactLocation', Ascending)
)

However, I now have lost my filtering of the town (location) which I was using a drop down for, and the search bar for "ExactLocation" is also broken.

If I have to lose the second filter to keep it delegable then so be it, but I need someway to search the data by at least the "ExactLocation" column or the apps going to be pretty useless! know Search isn't a delegable feature, and this app is only going to work if I can keep it all delegable, any ideas on a work around?

Was this reply helpful? Yes No
DCCCPE 14 on at

Like
a
(0)

Report
Copy link

Link copied!

So I've made progress,

(Sort
(Filter
('DataSource','Completed'=false,StartsWith('ExactLocation', Search1TextBox.Text)),
ExactLocation, Ascending)
)

So It is now working, and allowing search, joyfully delegating its tasks! Happy days.

However, I'd still like to reintroduce being able to filter it by the additional field of "Location" e.g. what town the report is from, as this would certainly help users to navigate it. However every time I attempt it, it comes up with a delegation warning.

It was a record field, which I then had the title of copied to a text column to allow it to be filtered.

Any thoughts good people?

Was this reply helpful? Yes No