cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenDonahue
Skilled Sharer
Skilled Sharer

Nested ForAll: Updating multiple records

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

RosterKeyChartAuditorKey

1

2

2

3
34
45
56
67
71

 

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

 

colAssignChart

ChartKeyProviderKeyChartAuditorKey 

11

1

2

1223
1312
1434
1545
1612
1723
1845
1934

 

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]}
        )
    )
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User
Super User

@BenDonahue 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,011)