Answered

Filtered ClearCollect not bringin all rows from SQL Table

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Jooorge 28 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
Jooorge 28 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Jooorge 28 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No