Filter of Collection not Returning all Data

[Anonymous]

I have several global ClearCollect formulas that kick off for two screens in an app.

The screens are a series of Text labels formulated to show SP list column totals from a collection based on the content of one or more columns.

The Text property for the label for the Manager total field is CountIf(Collection, Title = "Manager"). That returns 21.

I'm looking at the SP list through SP, filtered by Manager, and the count is 240. 

What is causing the collection to not report back the correct total?

[Anonymous]

Yep; got it to work. Thank you.

My issue, the list is at least 4,000 deep. I need a Total field that says 4,000, and that continues to update every time the app is opened and a new item(s) has been added.

A self-tallying count chart that returns any number of current and current compliant on many different Titles.

[WarrenBelz · Accepted solution]

Thanks @Anonymous ,

Two ways to do this - firstly individually as Text is a Delegable query so

ClearCollect(
   colAM,
   Filter(
      List,
      CQTitle = "AsstManager"
   )
)

then 

CountRows(colAM)

will get you the total up to 2,000 Assistant Managers

The other way is to do a big collection - this post of mine explains it. Happy to elaborate further.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous · Accepted solution]

So, put this in the App start:

ClearCollect(
   colAM,
   Filter(
      List,
      CQTitle = "AsstManager"
   )
)

And, put this in the Text properties of the Text Field: 

CountRows(colAM)

It that the instruction? 

[WarrenBelz · Accepted solution]

@Anonymous ,

It is not an instruction, it is an example of what you would need to do (on each category) to get an item count if you are dealing with 4,000+ records. Also it would be the Default of the Text field or the Text of a Label (or a Variable if you wanted).

A big collection as suggested will also work, but is more effort and complexity.

Also if you could collect the data you need in under 2,000 records with another text or numeric Filter, the all CountRows would work on this collection.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

Bravo! That worked.

Now, I've got a dozen other Titles for which I need the same function. How do I make that happen.

Also, some of those Titles have compliance requirements.

Compliance is indicated in the 'Status' column of the list.

I need a Text field to show the total number of a specific Title - (Task Accomplished)

I need a Text field to show the total number of a specific Title that is 'Compliant' - (Task Pending)

For Example:

Total No. of Asst. Bookkeepers is - 139.

Total No. of Compliant Asst. Bookkeepers - 78.

...and, I need to replicate this process for 7 other Titles.

[WarrenBelz · Accepted solution]

@Anonymous ,'

You accepted your own post as the solution?
Also have you looked at the big collection code on the link I posted

I mentioned collecting under 2,000 with another filter - will Status do this?

[Anonymous]

On you 'large' list post you call one of the fields that make it work "shadow" ID.

Can this "shadow" ID be something hat is already on the list?

I have a column called "UniqueID" that is assigned to every employee when their profile is created.

It is a 'required' column.

Can this (the UniqueID) be used as the "shadow ID" in your formula?

[Anonymous]

Ok, all the work got me to where I am now getting totals that I wasn't getting before.

However, I am still not getting all of the list.

A label set to show how many items are being pulled shows 2000.

Below is an image that shows the PowerApps returns in Blue, and the actual SP list totals as filtered in the list by position title in Black. I am still not getting the desired results.

[WarrenBelz]

@Anonymous ,

I need to reiterate that CountIf and CountRows are not Delegable, so all of this is a workaround. When designing a data model, you should consider accounting for likely information requirements this before you find yourself in the position you are now.

You can cut down the number of collections by combining them is you know the total is under 2,000, so 

ClearCollect(
   colAM,
   Filter(
      List,
      CQTitle = "AsstManager" ||
      CQTitle = "Janitor I" ||
      CQTitle = "Janitor II"
   )
)

could be used for these three.

The only real solution is the large collection link I posted - have you looked at that?

[Anonymous]

Mr. Belz:

Yes, I have looked at the large list link. I am working my way through it now.

In the instructions there is a reference to a 'Form', "...my new record Forms".

I am not using a form in my scenario. I'm using random Label fields on a blank screen.

Shall I put the formula in the 'Visible' property of the Screen I'm working on, as I don't have access on an 'OnSuccess' property?

"The process I do that will probably help you the most is the "shadow" ID number I keep. I have a numeric field in the list and this on the OnSuccess of my new record Forms."

ClearCollect(
   colAM,
   Filter(
      List,
      CQTitle = "AsstManager" ||
      CQTitle = "Janitor I" ||
      CQTitle = "Janitor II"
   )
)

Also, what do I put in each Label and under what property of that field should it be placed, Text, Default?