cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ocdc2008
Advocate II
Advocate II

Replacing In Operator with ForAll - Delegation

Hello,

 

I am building an Expense Report application with SharePoint Lists as the back end. There are two primary transactional data tables: 1) ExpenseReport and 2) ExpReportLine.

 

I'd like to do the following:

 

ClearCollect(expenseReport,Filter(ExpenseReport,Stage="Ready to Post"||Stage="Exported"));

ClearCollect(expenseReportLine,Filter(ExpReportLine,ExpenseReportID in expenseReport.ID));

 

This works, but the "in" operator is not delegable to SharePoint Lists. Over the course of a year the data will grow beyond the delegation limits, so I'm trying to use a ForAll to solve the problem.

 

ClearCollect(expenseReport,Filter(ExpenseReport,Stage="Ready to Post"||Stage="Exported"));

ClearCollect(expenseReportLine,Blank());
ForAll(expenseReport.ID,Collect(expenseReportLine,Filter(ExpReportLine,ExpenseReportID=ID)));

 

The underlined part of the ForAll brings a runtime error "An error occurred on the server. Server Response: Not a valid connector Error response."

 

How do I refer to the current line or iteration of the ForAll loop?

 

 

14 REPLIES 14
RezaDorrani
Dual Super User II
Dual Super User II

Hi @ocdc2008 

 

ForAll(expenseReport,Collect(expenseReportLine,Lookup(ExpReportLine,ExpenseReportID=ID)));

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Hello,

 

Thank you for the quick response!

 

Unfortunately, that did not solve problem. I'm still receiving the same error message.

@ocdc2008 

 

Are your sure error is on that line

 

Break ur problem into different buckets

 

First

ClearCollect(expenseReport,Filter(ExpenseReport,Stage="Ready to Post"||Stage="Exported"));

 

Check to see if expenseReport collection has data

 

ClearCollect(expenseReportLine,Blank());

replace with Clear(expenseReportLine)

 


ForAll(expenseReport.ID,Collect(expenseReportLine,Lookup(expenseReport,ExpenseReportID=ID)));

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

Hello,

 

The expenseReport collection pulls the correct data as expected.

The Clear/ClearCollect(Blank()) change did not make a difference. The blank collection contains all of the appropriate columns in either case.

 

Your revision to the Lookup portion of the ForAll would not pull properly because it isn't pointing at the correct data source that I'm trying to pull down to the collection. My instructions are:

 

For each record in expenseReport, pull all of the records in Data Source: ExpReportLine where the field ExpenseReportID is equal to the expenseReport.ID of the current iteration.

@ocdc2008 

 

Ok so ExpReportLine is a different data source 

 

Can you try (just for testing)

 

ForAll(expenseReport.ID,Collect(expenseReportLineNew,Lookup(ExpReportLine ,ExpenseReportID=expenseReport[@ID])));

 

Assumptions ExpenseReportID is in the ExpReportLine  data source

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

Nice!

 

This worked, but it generated a new delegation warning. It seems the table record reference isn't supported within the Filter(). Does this mean when the function executes, it will only pull the first 500 records before applying the Filter logic?

@ocdc2008 

 

Did not understand your question

I attached a screenshot of the warning message. If this approach generates a delegation warning, is it any better than the original approach using the "in" operator. ClearCollect(expenseReportLine,Filter(ExpReportLine,ExpenseReportID in expenseReport.ID));

@ocdc2008 

 

No it is not any better 

What type of column is ExpenseReportID?

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,135)