Can I only Load Rows with a specific value?

[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

[Pstork1]

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

[DCCCPE]

It's a choice column

[Pstork1]

Use something like the following filter in the Items property of the gallery

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

[DCCCPE]

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)

[Pstork1]

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.

[DCCCPE]

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! 😵

[Pstork1]

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.

[Drrickryp]

@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-...   Not everyone agrees with all of them but they work for me.

[DCCCPE]

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?