cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FUS
Helper III
Helper III

Forall Lookup and disambigous reference

Hi All.

I got two tables.
Table 1 is the master table with data.
Table 2 is the request list... and these requests can be new rows, updates and deletion of rows.
And I have issues getting the updates to work.

I'm trying to use a Filtered ForAll forumla with a lookup function to identify which row to update... but I can't find the right row and when I tried to use disambigous reference it said I compared text to a table and in some cases I update only the first row (which is the wrong one).

I created a simple test area and this is the code I'm not getting to work:
ForAll(Filter(Var_test1, Change = true),
Patch(Var_test2,
LookUp(Var_test2,Var_test2[@Namn] = ThisRecord.Namn && Var_test2[@Yrke] = ThisRecord.Yrke)
,{Namn: Namn, Ålder: Ålder, Yrke: Yrke});
Patch(Var_test1,ThisRecord,{Change: false}));

Instead of ThisRecord I've also tried "Var_test1[@Namn]... with as little success.
Would be greatfull for some inputs...

1 ACCEPTED SOLUTION

Accepted Solutions
C-Papa
Super User
Super User

Hi, it looks like you are using local collections, i have adjusted your formula, let me know if it works for you

 

ForAll(

Filter(Var_test1, Change = true) As MyItem,

Patch(Var_test2, {ID: 

LookUp(Var_test2,[@Namn] = MyItem.Namn && [@Yrke] = MyItem.Yrke).ID}
,{Namn: MyItem.Namn, Ålder: MyItem.Ålder, Yrke: MyItem.Yrke});
Patch(Var_test1,MyItem,{Change: false}));

View solution in original post

5 REPLIES 5
C-Papa
Super User
Super User

Hi, it looks like you are using local collections, i have adjusted your formula, let me know if it works for you

 

ForAll(

Filter(Var_test1, Change = true) As MyItem,

Patch(Var_test2, {ID: 

LookUp(Var_test2,[@Namn] = MyItem.Namn && [@Yrke] = MyItem.Yrke).ID}
,{Namn: MyItem.Namn, Ålder: MyItem.Ålder, Yrke: MyItem.Yrke});
Patch(Var_test1,MyItem,{Change: false}));

Yes, I usually use collections when I need to experiment, so easy to set up... the original problem is in Sharepoint... but think I can solve it once I solve this one.

And I think I just did, thanks to your input.
Renaming the Filteration worked like a charm... but I had to remove the ID part and also the [@] part.

So the final code that worked is:
ForAll(
Filter(Var_test1, Change = true) As MyItem,
Patch(Var_test2,
LookUp(Var_test2,Namn = MyItem.Namn && Yrke = MyItem.Yrke)
,{Namn: MyItem.Namn, Ålder: MyItem.Ålder, Yrke: MyItem.Yrke});
Patch(Var_test1,MyItem,{Change: false}));

FUS
Helper III
Helper III

And with a little adjustment it worked perfect in the original Sharepoint list aswell.

Except... The second Patch isn't accepted... so I can't change the status of the "request" simultanious.
It says I can't update the record in the ForAll loop?

Ofcourse the status can be changes after... but I would really like to do it simultaneous as only those lines actually processed would be updated
Is there a way to solve this?

C-Papa
Super User
Super User

i just realised your filter criteria is what you are trying to update, if you collect you filter first it might work, I'm un able to test at the minute but give the below a try:

 

ClearCollect(Col_Filter_Results,Filter(Var_test1, Change = true))

;ForAll(Col_Filter_Results As MyItem,

Patch(Var_test2, {ID: 

LookUp(Var_test2,[@Namn] = MyItem.Namn && [@Yrke] = MyItem.Yrke).ID}
,{Namn: MyItem.Namn, Ålder: MyItem.Ålder, Yrke: MyItem.Yrke});
Patch(Var_test1,{ID:MyItem.ID},{Change: false}));

Ahh Thanks... so I need to create a collection of the desired changes to be able to update the original post directly.
Sounds logical.

Got the other code to work atm... but I will try this next time I make an update 🙂
Thanks!

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 (2,449)