cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

ForAll working inconsistently - why?

Hi,

 

I have 3 tables:  "Job Setup" (a header record), "Project Job Category" (a line item to Job Setup), and "Job Category" (a set of default records I want to use to create the line items).  Every time I create a header, I want to copy over the records from "Job Category" to "Project Job Category" as a starting point for the user to further edit.  "Project Job Category" has lookups to both of the other tables mentioned.  In other words, "Project Job Category" is a join table, representing a many-to-many relationship between "Job Setup" and "Job Category".

 

I can iterate "Job Category" successfully with a ForAll, using Collect to visualize that I am indeed looping multiple times.  However, when I try to ForAll through "Job Category" to create the "Project Job Category" rows, it only runs once.  There are about 10 "Job Category" records.

 

For example, I successfully iterate over Job Category here:

 

//This iterates multiple times and successfully creates multiple records in the collection (as intended)
ForAll(
    ['Job Categories'],
    Collect(
        TGTestCategoryIdCollectionFromForAll,
        'Job Categories'
    )
);

 

 

But when piecing this together with a patch, it only runs once:

 

//This creates a single linked record successfully, but only iterates once... why?
ForAll(
    ['Job Categories'],
    Patch(
        'Project Job Categories',
        Defaults('Project Job Categories'),
        {
            'Job Category': LookUp(    //Use lookup because I can't figure out how to reference the current record from ForAll
                'Job Categories',
                'Category ID' = 'Job Categories'[@'Category ID']
            ),
            'Job Setup': gblSelectedJob   //gblSelectedJob is the Job Setup that I want to link to
        }
    )
);

 

 

Questions:

-Why does the second ForAll only run once when the first one runs multiple times?

-Is there a better way to create some default records in one step than this?

 

Thanks for any help or guidance, much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: ForAll working inconsistently - why?

Hardit,

 

Eventually found the solution.  Here is the working version in case it ever helps.

 

The required changes were:

  1. Changing syntax of table references... note the lack of braces in ForAll, but the inclusion of [@..] braces in table references inside the Patch
  2. Adding of RenameColumns in ForAll, to eliminate ambiguity the in LookUp
  3. Had to reference some of the columns by their internal name in order for the formula editor to accept them. Not sure why.

Final version

//This works
ForAll(
    RenameColumns('Job Categories', "cr8b0_jobcategoriesid", "Category ID Tmp"),
    Patch(
        [@'Project Job Categories'],
        Defaults([@'Project Job Categories']),
        {
            'Job Category': LookUp('Job Categories', 'cr8b0_jobcategoriesid' = 'Category ID Tmp'),
            'Job Setup': gblSelectedJob
        }
    )
);

 

Thanks for the help thinking through it, much appreciated!

View solution in original post

10 REPLIES 10
Highlighted
Super User
Super User

Re: ForAll working inconsistently - why?

Have you tried?

//This creates a single linked record successfully, but only iterates once... why?
ForAll(
    ['Job Categories'],
    Patch(
        'Project Job Categories',
        Defaults('Project Job Categories'),
        {
            'Job Category': the field in Job Categories which is a lookup to Job Category,         
            'Job Setup': gblSelectedJob  //gblSelectedJob is the Job Setup that I want to link to
        }
    )
);

 

Let me know if this helps. If not, I will share an example that might give you an idea. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Frequent Visitor

Re: ForAll working inconsistently - why?

Hi Hardit,


Thanks for the reply.

 

It doesn't like that, it is seems to want the entire record for that reference....

 

 

Annotation 2020-04-09 114857.png

Highlighted
Super User
Super User

Re: ForAll working inconsistently - why?

Can you show me the data structure? Sorry if you have answered this before, but are these SP lists or collections or some other data source? 

 

What is the lookup field on the Job Categories table that references the Job Category? The one referenced below in your screenshot is the Job Categories table which is why you are getting the error that it needs a record, not a table. 

 

Let me know. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Frequent Visitor

Re: ForAll working inconsistently - why?

Hardit,

 

These are CDS entities.

 

Here is the schema for Project Job Category (records I am trying to create), and Job Category (records I am trying to loop through).

 

Job Category is a simple list entity, just the ID GUID column and a name column for the name of the category.

 

Project Job Category is the join table - with a lookup each to Job Setup and Job Category (Job Setup and Job Category, respectively).  The Job Setup relation is working fine, so not including that schema.

 

 

 

Job Category Schema.pngProject Job Category Schema.png

Highlighted
Super User
Super User

Re: ForAll working inconsistently - why?

Try this: 

ForAll(
    ['Job Categories'],
    Patch(
        'Project Job Categories',
        Defaults('Project Job Categories'),
        {
            'Job Category': LookUp('Job Categories', 'Category ID' = 'Category ID',         
            'Job Setup': gblSelectedJob  //gblSelectedJob is the Job Setup that I want to link to
        }
    )
);

What I am trying to do here, is inside my Patch, for the Job Category lookup field, I am using a lookup to Job Categories, using the ID of the category ID of the current row in the ForAll loop. The reason I am using LookUp is because there is no way to fetch the entire record within a ForAll loop. 

 

Let me know if this works. If not, I will replicate your use case using real entities and give you an answer. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Frequent Visitor

Re: ForAll working inconsistently - why?

Hardit,

 

That version is accepted by formula editor.  When it runs though, it doesn't successfully find the Job Category and set it.  You can see the result in the first row shown below.

 

Adjusting it slightly gets us back to the formula I had in my original question.  The record also gets created, but the Job Category is set correctly.


In either version - the ForAll only loops once, or at least only creates 1 Project Job Category record (the screenshot shows the process having run twice - first row with your formula, the second with ('Category ID' = 'Job Categories'[@'Category ID']) instead of ('Category ID' = 'Category ID').

 

I also included screenshot of rows in Job Category for reference - there are 12, so I expect 12 Project Job Category records to be created.

 

What is created from two different function versions:

createdRecordsExample.png

 

Data in the Job Category table:

jobCategoryRows.png

Highlighted
Super User
Super User

Re: ForAll working inconsistently - why?

In trying to replicate your scenario, do you think I would need Job Setup or can I ignore that lookup field in Project Job Category? 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Frequent Visitor

Re: ForAll working inconsistently - why?

I think you can ignore it, that part is working OK.

Highlighted
Frequent Visitor

Re: ForAll working inconsistently - why?

Hardit,

 

Eventually found the solution.  Here is the working version in case it ever helps.

 

The required changes were:

  1. Changing syntax of table references... note the lack of braces in ForAll, but the inclusion of [@..] braces in table references inside the Patch
  2. Adding of RenameColumns in ForAll, to eliminate ambiguity the in LookUp
  3. Had to reference some of the columns by their internal name in order for the formula editor to accept them. Not sure why.

Final version

//This works
ForAll(
    RenameColumns('Job Categories', "cr8b0_jobcategoriesid", "Category ID Tmp"),
    Patch(
        [@'Project Job Categories'],
        Defaults([@'Project Job Categories']),
        {
            'Job Category': LookUp('Job Categories', 'cr8b0_jobcategoriesid' = 'Category ID Tmp'),
            'Job Setup': gblSelectedJob
        }
    )
);

 

Thanks for the help thinking through it, much appreciated!

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,963)