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
Microsoft
Microsoft

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (55,648)