cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jooorge
Frequent Visitor

Filtered ClearCollect not bringin all rows from SQL Table

Hi All,

So I have a ClearCollect on AppStart to filter a source SQL table with around 15k rows (result should be less than 500 rows). Im noticing Im not having all the rows in the result Collection.

 

What is the ClearCollect fetch limit? It looks like its not seeing the full source SQL Table?

Any thoughts?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Jooorge ,

Try this

With(
   {
      wCurrent:
      Filter(
         '[dbo].[CurrentR]',
         Region="Heartland Area"
      )
   },
   ClearCollect(
      CurrentR,
      ShowColumns(
         wCurrent,
         "Entity_x0020_Adjust_x0020_for_x0020_S_x0020_Branches",
         "Agreement_x0020_Name",
         "Agreement_x0020_Number",
         "LOB",
         "Agreement_x0020_Type",
         "Inter_x0020_Comp_x0020_Indicator",
         "Price_x0020_Escalation",
         "gm_x0020__x0025__x0020__x0028_equal_x0020_to_x0020_estimate_x002c__x0020_change_x0020_as_x0020_req_x0027_d_x0029_",
         "Contract_x0020_Duration",
         "Renewal_x0020_Month",
         "New_x0020_Contract_x0020_Start_x0020_Date",
         "secured_x0020_volume",
         "secured_x0020_gross_x0020_margin",
         "Monthly_x0020_Revenue", 
         "Monthly_x0020_Margin",
         "New_x0020_Monthly_x0020_Revenue_x0020_per_x0020_secured",
         "New_x0020_Monthly_x0020_Margin_x0020_per_x0020_Secured",
         "Catch_x002d_up_x0020_Revenue",
         "Catchup_x0020_GM",
         "Comments",
         "Contract_x0020_Amount",
         "Renewing_x003f_",
         "Index_Id",
         "Orig_x0020_Est_x0020_Margin_x0020__x0024_",
         "Period_x0020_of_x0020_Renewal",
         "Period_x0020_of_x0020_Start_x0020_Date"
      )
   )
)

 

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.

View solution in original post

7 REPLIES 7
WarrenBelz
Super User
Super User

Hi @Jooorge ,

Collections have an "initial" limit of your Delegation limit (500-2,000), however if you have a Delegable Filter collecting it, it should return the 500 rows you are after. What is your ClearCollect code?

 

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.

 

 

@WarrenBelz  thank you! So Im grabing an Owners sharepoint table then looking up who has the App opened, then using his assigned Entities (very few) to apply a filter in the SQL table with 'IN' the entity Column. (multiple values being filtered in same column).

 

ClearCollect(Owners,Filter(Owner_Entities,OwnerBranch = First(Split(User().Email,"@")).Result));
ClearCollect(
CurrentR,
ShowColumns(
Filter('[dbo].[CurrentR]',
'Branches' in Owners.Entity )
,
"Entity_x0020_Adjust_x0020_for_x0020_S_x0020_Branches",
"Agreement_x0020_Name",
"Agreement_x0020_Number",
"LOB",
"Agreement_x0020_Type",
"Inter_x0020_Comp_x0020_Indicator",
"Price_x0020_Escalation",
"gm_x0020__x0025__x0020__x0028_equal_x0020_to_x0020_estimate_x002c__x0020_change_x0020_as_x0020_req_x0027_d_x0029_",
"Contract_x0020_Duration",
"Renewal_x0020_Month",
"New_x0020_Contract_x0020_Start_x0020_Date",
"secured_x0020_volume",
"secured_x0020_gross_x0020_margin",
"Monthly_x0020_Revenue",
"Monthly_x0020_Margin",
"New_x0020_Monthly_x0020_Revenue_x0020_per_x0020_secured",
"New_x0020_Monthly_x0020_Margin_x0020_per_x0020_Secured",
"Catch_x002d_up_x0020_Revenue",
"Catchup_x0020_GM",
"Comments",
"Contract_x0020_Amount",
"Renewing_x003f_",
"Index_Id",
"Orig_x0020_Est_x0020_Margin_x0020__x0024_",
"Period_x0020_of_x0020_Renewal",
"Period_x0020_of_x0020_Start_x0020_Date"
)
);

 

@Jooorge ,

I am not an SQL user, but do you get a Delegation warning on this part?

Filter(
   '[dbo].[CurrentR]',
   'Branches' in Owners.Entity
)

If so, you are limited to your Delegation limit for the sample to collect from.

 

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.

@WarrenBelz  Yes, I see a double blue underscore at 'in'.

What are my options if I need to filter for multiple values in the same column in the clearcollect filter?

 

Thanks again for your help!

Hi @Jooorge ,

Your only real option if you have to use the in filter is to do a (very) big Collection. I have a number of options for this in my blog on Delegation.

 

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.

@WarrenBelz  one more thing. If I filter like this, with a straight hard coded filter (no delegation warinings) Im onnly getting 219 rows out of the 437 in the SQL table. Any ideas on what I could try?

Thanks again for all your help

 

 

ClearCollect(
CurrentR,
ShowColumns(
Filter('[dbo].[CurrentR]',
Region="Heartland Area")
,
"Entity_x0020_Adjust_x0020_for_x0020_S_x0020_Branches",
"Agreement_x0020_Name",
"Agreement_x0020_Number",
"LOB",
"Agreement_x0020_Type",
"Inter_x0020_Comp_x0020_Indicator",
"Price_x0020_Escalation",
"gm_x0020__x0025__x0020__x0028_equal_x0020_to_x0020_estimate_x002c__x0020_change_x0020_as_x0020_req_x0027_d_x0029_",
"Contract_x0020_Duration",
"Renewal_x0020_Month",
"New_x0020_Contract_x0020_Start_x0020_Date",
"secured_x0020_volume",
"secured_x0020_gross_x0020_margin",
"Monthly_x0020_Revenue",
"Monthly_x0020_Margin",
"New_x0020_Monthly_x0020_Revenue_x0020_per_x0020_secured",
"New_x0020_Monthly_x0020_Margin_x0020_per_x0020_Secured",
"Catch_x002d_up_x0020_Revenue",
"Catchup_x0020_GM",
"Comments",
"Contract_x0020_Amount",
"Renewing_x003f_",
"Index_Id",
"Orig_x0020_Est_x0020_Margin_x0020__x0024_",
"Period_x0020_of_x0020_Renewal",
"Period_x0020_of_x0020_Start_x0020_Date"
)
);

Hi @Jooorge ,

Try this

With(
   {
      wCurrent:
      Filter(
         '[dbo].[CurrentR]',
         Region="Heartland Area"
      )
   },
   ClearCollect(
      CurrentR,
      ShowColumns(
         wCurrent,
         "Entity_x0020_Adjust_x0020_for_x0020_S_x0020_Branches",
         "Agreement_x0020_Name",
         "Agreement_x0020_Number",
         "LOB",
         "Agreement_x0020_Type",
         "Inter_x0020_Comp_x0020_Indicator",
         "Price_x0020_Escalation",
         "gm_x0020__x0025__x0020__x0028_equal_x0020_to_x0020_estimate_x002c__x0020_change_x0020_as_x0020_req_x0027_d_x0029_",
         "Contract_x0020_Duration",
         "Renewal_x0020_Month",
         "New_x0020_Contract_x0020_Start_x0020_Date",
         "secured_x0020_volume",
         "secured_x0020_gross_x0020_margin",
         "Monthly_x0020_Revenue", 
         "Monthly_x0020_Margin",
         "New_x0020_Monthly_x0020_Revenue_x0020_per_x0020_secured",
         "New_x0020_Monthly_x0020_Margin_x0020_per_x0020_Secured",
         "Catch_x002d_up_x0020_Revenue",
         "Catchup_x0020_GM",
         "Comments",
         "Contract_x0020_Amount",
         "Renewing_x003f_",
         "Index_Id",
         "Orig_x0020_Est_x0020_Margin_x0020__x0024_",
         "Period_x0020_of_x0020_Renewal",
         "Period_x0020_of_x0020_Start_x0020_Date"
      )
   )
)

 

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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (4,112)