ForAll working inconsistently - why?

[taylordgordon]

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!

[PowerAddict]

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

[taylordgordon]

Hi Hardit,

Thanks for the reply.

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

[PowerAddict]

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

[taylordgordon]

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.

[PowerAddict]

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

[taylordgordon]

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:

Data in the Job Category table:

[PowerAddict]

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

[taylordgordon]

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

[taylordgordon · Accepted solution]

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!