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?

[WarrenBelz · Accepted solution]

@Anonymous ,

You have already marked one of your own posts as the solution on this and also matched two of my posts on completely different issues together (the code is nothing to do with the Shadow ID). I am happy to help, but  it is getting difficult to understand where you want to head.

The subject of your post was around not returning all data and this is caused by Delegation issues. The concept of the shadow ID is simply a numeric field set to the ID number on every record, so you need a mechanism to do this on new records.

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]

Please forgive how my mind works.

I understand what you've just written. I believe my confusion comes with the where to place things, in some cases.

There are times when I am unsure what formula goes in what Field, Gallery, Form, Label, Property.

Some of the clarification presumes IMO a level of understanding and comprehension that my not read as clearly from this side.

I believe I clicked 'Solution' prematurely. I got numbers that I hadn't previously gotten, however I didn't get all the numbers. 

Which is where I stand now. I can get some not all. 

I am attempting to work through the 'large list' guidance. My last question was regarding the 'Form' referenced in the guidance. It plays are role in the formula. However, I am not using a 'Form' to display my data, rather random Labels on a Screen. That said, how would the formula change for my use.

Thank you for your patience.

[WarrenBelz · Accepted solution]

@Anonymous ,

I have recently done a blog on Delegation and in particular large lists. Please have a read of it - happy to answer specific questions after you have done this.

[Anonymous]

I'm back from the abyss.

I finally got everything right-side up, created and placed things as you instructed; unsurprisingly, it worked like a charm!

Thank you!

To continue...

I have ClearCollect(colAMDrvr,Filter(List, Title = "Driver")); in the AppStart

I have CountRows(colAMDrvr) in the Text property of a Label field.

The above returns the total number of Drivers on the list (699) to Label "Drvr".

I also have label "Active" and label "Compliance".

What combination of clear collect in the AppStart and 'count...' command in the "ActDrv" label Text property would return total number of "Active" Drivers - based on adding the content of the list "Status" column to the above?

Likewise, what combination would return total number of "Active" & "Compliant" drivers to the 'Compliance' label - based on the content of the list "Compliance" column to the above? 

[Anonymous]

I got your 'big list' instructions to work on a stand-alone screen.

I attempted to move the fields and formulas (with a cut a paste) to a screen in another App where the data is desired.

When I post the fields on the screen and the formulas where they belong, it all work, however it only brings back half the data.

I know believe the 'big list' clear collects are being unintentionally impacted by the exiting clearcollect commands managing the data on the other App screens.

Is there a way to introduce your 'big list' collection commands into an existing App where collection commands already exist and still get all the numbers?

[WarrenBelz]

Hi @Anonymous ,

I must have missed your post on Friday.

In relation to your question on the Active and Compliant item in Status, I have also done this blog dealing with a syntax using With() if these will always return less then 2,000 items to count. See if this works for you.

I am not sure how to comment on your latest question other than collections should not affect each other . Have you done a CountRows() on the new big 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 · Accepted solution]

Okay now young man, buckle-up; because its me again...

In your blog what does 'MyStatus' represent in your formula, and what should it represent in mine?

This was your formula in the blog.

With(
   {
      MyStatus: 
      Filter(
         MyList,
         Status = "In Progress"
      )
   },
   CountRows(MyStatus)
)

I changed it to this following. It returned the number 310 full buckets. There are 495 'Full' buckets on the list.

With(
   {
      colAMBkt: 
      Filter(
         EquipList,
         Status = "Full"
      )
   },
   CountRows(colAMBkt)
)

There are also a total of 701 buckets. The colAMBkt is only returning 500 to the Total Buckets field. Seems any 'item' with more than 500 items the return total displayed is capped at 500 (there are four other fields on the screen at 500).

[WarrenBelz]

Hi @Anonymous ,

Firstly you need to increase your limit to 2,000 to get more than 500 (this was mentioned in the blog).

I did some fairly extensive testing before I posted including an exercise on a 10,000+ item list (work completed by about 50 different people) and actually using a Choice Field in the source got the exact number of jobs (which I had grouped and counted in SharePoint) for everyone (all under 2,000). I have just re-run it with the same result.

I am noting this as I am wondering why yours does not work. The syntax is essentially a collection then a filter and as long as the first bit is delegable and produces a record set under the Delegable limit, it makes perfect sense that it would get the desired result. So your example

With(
   {
      colAMBkt: 
      Filter(
         EquipList,
         Status = "Full"
      )
   },
   CountRows(colAMBkt)
)

should return the number of records (up to the limit you have set) where the Status (I assume a Single Line of Text field) equals Full

Is all of this correct?

[Anonymous]

Not sure what to tell you. I have cut and paste your formula into my text property and changed the language. 

Same issue, the numbers come back nearly double.

The first column is accurate (with colAMSCBA).

The second column should read 461, not 1162.

The number of active compliant is 461.

Its as if the 'With' function is calculating the total compliant with the total active, rather than showing only the portion of the 701 that are compliant.

1st Column formula:

CountRows(colSCBA)

2nd Column formula:

With(
{
            colSCBA:
            Filter(
                    List,
                    ComplianceStatus = "Noncompliant"
)
},
                    CountRows(colSCBA)
)

[WarrenBelz]

@Anonymous ,

There is something else wrong here - the With() is no more than a filtered collection in the statement. If you do this on a button

ClearCollect(
   colSCBA,
   Filter(
      List,
      ComplianceStatus = "Noncompliant"
   )
)

Then this one a label

CountRows(colSCBA)

what result do you get?