Filtered ClearCollect not bringin all rows from SQL Table

[Jooorge]

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

[WarrenBelz]

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.

[Jooorge]

@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"
)
);

[WarrenBelz]

@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.

[Jooorge]

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

[WarrenBelz]

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.

[Jooorge]

@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"
)
);

[WarrenBelz · Accepted solution]

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.