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

ForAll (Collect()) Syntax Problem

Hello,

 

I'm struggling with the following code:

 

ForAll(invoices,Collect(invWorkOrders,Filter('[dbo].[WorkOrder]',InvoiceID=ID)));

 

The steps:

 

1) Collect the invoices related to the current billing cycle

2) For each invoice, collect all of the WorkOrders based on the WorkOrder.InvoiceID = invoices.ID.

 

I cannot figure out how to get the syntax to work in order to reference the invoices.ID of the current loop iteration in the ForAll. I've seen something like [@ID] before, but I can't find any record of that on the forum.

 

How do I reference the current record values in invoices in the above example?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @ocdc2008 ,

A bit of a guess here, but try the below - I am suspecting '[dbo].[WorkOrder]' also has an ID and there is some Ambiguity. There is nothing wrong with your code syntax.

ForAll(
   RenameColumns(
      invoices,
      "ID",
      "ID1"
   ),
   Collect(
      invWorkOrders,
      Filter(
         '[dbo].[WorkOrder]',
         InvoiceID=ID1
      )
   )
)

 

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

6 REPLIES 6
chsanche
Community Support
Community Support

Hi @ocdc2008 ,

 

Can you please share with me the exact error message that you are receiving?

 

Best Regards

If this post helps you give a :thumbs_up: and if it solved your issue consider Accept it as the solution

Hello,

 

I am not receiving an error message. The syntax seems to be valid, but I'm thinking the formula is confused as to what which :ID I am attempting to reference.

 

My guess is it thinks I am trying to say pull records when ['dbo'].['WorkOrder'].InvoiceID = ['dbo'].['WorkOrder'].ID as opposed to what I want: ['dbo'].['WorkOrder'].InvoiceID = ThisInvoiceFromTheLoop.ID

Hi @ocdc2008 ,

A bit of a guess here, but try the below - I am suspecting '[dbo].[WorkOrder]' also has an ID and there is some Ambiguity. There is nothing wrong with your code syntax.

ForAll(
   RenameColumns(
      invoices,
      "ID",
      "ID1"
   ),
   Collect(
      invWorkOrders,
      Filter(
         '[dbo].[WorkOrder]',
         InvoiceID=ID1
      )
   )
)

 

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

@WarrenBelz 

 

This worked! It seems like a bit of a work around when there should be built in syntax for specificity, but this will do!

 

Thank you.

Hi @ocdc2008 ,

Yes there is disambiguation syntax available, but when coming from a collection, renaming is always reliable.

@WarrenBelz Thanks for providing both options. Very helpful.

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!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,453)