Nested ForAll: Updating multiple records

[BenDonahue]

The Setup

I have a situation where I need to update a value in one table based on values in another table.

Table 1: Chart   (used to create colAssignCharts)

ChartKey 

ChartKey2 (identical to ChartKey)

ProviderKey (based on RosterKey)

ChartAuditorKey (based on RosterKey)

Table 2: Roster  (used to create colAssignRosterIndex)

RosterKey

ChartAuditorKey

colAssignCharts starts off with ProviderKey and ChartKey populated, but ChartAuditor is blank.

colAssignRosterIndex has RosterKey and ChartAuditorKey populated, with ChartAuditorKey being generated in the collection, which will drive updating ChartAuditorKey in colAssignCharts.

When a person is assigned to be a ChartAuditor, their RosterKey is entered in ChartAuditorKey in Chart table on the line with the Chart they are to audit.

The Goal

Multiple Charts can be assigned to one person. I want to, based on the assignments generated in colAssignRosterIndex, enter the ChartAuditorKey from colAssignRosterIndex into ChartAuyditorKey column in colAssignCharts. This will make that person the Chart Auditor for that chart.

The Failed Code

This code updates only the first instance of the ProviderKey in the table colAssignCharts:

ForAll(
    colAssignRosterIndex,
    ForAll(
        Filter(
            colAssignCharts,
            colAssignCharts[@ProviderKey] = colAssignRosterIndex[@RosterKey]
        ),
        Patch(
            colAssignCharts,
            LookUp(
                colAssignCharts,
                colAssignCharts[@ProviderKey] = colAssignRosterIndex[@RosterKey]
            ),
            {ChartAuditorKey: colAssignRosterIndex[@ChartAuditorKey]}
        )
    )
)

The Example

Based on the ChartAuditorKey assignment in colAssignRosterIndex, make those assignments in colAssignChart.

colAssignRosterIndex

RosterKey	ChartAuditorKey
1	2
2	3
3	4
4	5
5	6
6	7
7	1

So, every time there is a 1 for ProviderKey in colAssignChart, below, there should be a 2 in ChartAuditorKey in colAssignChart.

colAssignChart

ChartKey	ProviderKey	ChartAuditorKey
11	1	2
12	2	3
13	1	2
14	3	4
15	4	5
16	1	2
17	2	3
18	4	5
19	3	4

The Question

How can I update all records in the filtered list of colAssignCharts such that the column ChartAuditorKey is populated with the same value for RosterKey from the ChartAuditorKey column of colAssignRoster?

The Thanks

I felt that this would be simple enough, but, after hours of banging my head against this problem, I still have nothing. 

Thank you, sincerely, for your assistance in this.

Other Failed Attempts

I really thought I had it with this one

ForAll(
    colAssignRosterIndex,
    ForAll(
        Filter(
            colAssignCharts,
            colAssignCharts[@ProviderKey] = colAssignRosterIndex[@ChartAuditorKey]
        ),
        Patch (
            colAssignCharts,
            LookUp(
                colAssignCharts,
                ChartKey = colAssignCharts[@ChartKey]
            ),
            {ChartAuditorKey: colAssignRosterIndex[@ChartAuditorKey]}
        )
    )
)

[RandyHayes · Accepted solution]

@BenDonahue 

The first problem is that you're using the ForAll like a ForLoop in a development language.  In the no-code world of PowerApps, ForAll is a function that returns a table of records defined in your ForAll.

Please consider changing your Formula to the following:

ClearCollect(
    colAssignChart,
    ForAll(colAssignChart As _item,
        Patch(_item, {ChartAuditorKey: LookUp(colAssignRosterIndex, RosterKey = _item.ProviderKey, ChartAuditorKey})
    )
)

I hope this is helpful for you.

[BenDonahue]

Works like a dream.

You helped me twice on this project, once with applying an index to my collection, and now this.

I am really going to have to get a handle on this As business, it seems.

Thank you, yet again, @RandyHayes.

[RandyHayes]

@BenDonahue 

No problem.  "As" is just a way to give a table a name. Much easier to work with than the disambiguation syntax.

[thisisdylanjame]

@RandyHayes - this solution was extremely helpful! Thanks a ton!

I was stuck on this issue for several days, i tried a similar solution, but it resulted in the same value being populated in all rows.

The main difference in your code is the use of "As" keyword, that made the world of a difference.