cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mattw112IG
Impactful Individual
Impactful Individual

ForAll updating too many records

I'm trying to use a Forall to update records which have a column in common with another list.. it's complicated... I will try to explain.

 

I have three lists.

 

Sales Person

Opportunity

Product

 

Opportunity contains column called sales person which links it to the sales person list

Product has a column called opportunity ID which links it to the opportunity list

 

...

 

Anyway... what I am trying to do is when an AM is inactive, make the related opps and products also not active...

 

For the most part everything is working fine with making the opportunities not active.

 

But.. right now ALL products are being changed to inactive not just the ones related to the reqs being made inactive??

 

here's the code:

 

If(
        DataCardValue104.Value = false,
        ClearCollect(
            RemovedAM,
            Filter(
                Opportunities,
                'Account Manager'.DisplayName = Gal_AMs.Selected.Name.DisplayName
            )
        );
        UpdateIf(
            Opportunities,
            'Account Manager'.DisplayName = Gal_AMs.Selected.Name.DisplayName,
            {
                CloseDate: Now(),
                Open: false
            }
        );
        ForAll(
            RemovedAM,
            UpdateIf(
                Products,
                Products[@OppID] = ThisRecord.OppID,
                {
                    NotActiveDate: Now(),
                    Active: false
                }
            )
        ),
        false
    ),
    false
)

 

The column in the Opportunity (OppID) is the same as the column in the product (OppID) so I have tried 

 

doing Products[@OppID] instead of just OppID so it knows the difference

 

Not sure if that is 100% right, but not throwing an error.

 

Also not sure if I'm using ThisRecord correctly?

 

So on my Forall why is it updating ALL products and not just the products tied to the Reqs that are tied to the sales person (AM)

 

Thanks,

Terry

 

PS the clear collect is right by the way.  It does create the collection with the right opportunities... but still updates products not tied to those records?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Eelman
Super User
Super User

@Mattw112IG 

You could also try using 'As', ie

ForAll(
            RemovedAM As R_AM,
            UpdateIf(
                Products,
                OppID = R_AM.OppID,
                {
                    NotActiveDate: Now(),
                    Active: false
                }
            )
        )

This can sometimes be a better way to remove the disambiguation issues.

View solution in original post

5 REPLIES 5
Drrickryp
Super User II
Super User II

In general it's better to leave the primary column in a list as just ID and to name the foreign key in a list after the parent list like you have. I think you may be having a problem because your formulas are ambiguous. All SharePoint lists have an ID column that is hidden but can be referenced in PowerApps. It is sequential numbers that qualify as a primary key.

Mattw112IG
Impactful Individual
Impactful Individual

This is the connections/relationships I have. And again it is pretty much working, just need to make sure my Forall/UpdateIfs are correct.

 

Thanks,

Terry

classicman
Regular Visitor

Try wrapping your condition statement with First after Filtering.

 

ForAll(
            RemovedAM,
            UpdateIf(
                Products,
                First(Filter(Products,Products[@OppID] = ThisRecord.OppID)),
                {
                    NotActiveDate: Now(),
                    Active: false
                }
            )
        )

 

Eelman
Super User
Super User

@Mattw112IG 

You could also try using 'As', ie

ForAll(
            RemovedAM As R_AM,
            UpdateIf(
                Products,
                OppID = R_AM.OppID,
                {
                    NotActiveDate: Now(),
                    Active: false
                }
            )
        )

This can sometimes be a better way to remove the disambiguation issues.

View solution in original post

Mattw112IG
Impactful Individual
Impactful Individual

Hi I used the As and it works, thanks!

 

I think I need to reread what the ThisRecord is for though, I was certain it would do what I was looking to do??

 

Thanks,

Terry

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,827)