ForAll updating too many records

[Mattw112IG]

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?

[Drrickryp]

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]

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]

Try wrapping your condition statement with First after Filtering.

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

[Anonymous · Accepted solution]

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

[Mattw112IG]

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