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!

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,358)