cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alex1857
New Member

Issue for Sharepoint reference list update with Power Apps - Patch Lookup RemoveIf

Hello,

I have a SharePoint list where one of the columns contains references (SharePoint search field) to items of this list. This allows me to "link" items together. For example, if item A is linked to B and C and item B is linked to C and D, it becomes :

 

ItemRelated Item
AB, C
BA, C, D
CA, B
DB

 

To update all this, so to go from an old list to a new list, I proceed in two steps. First, I delete the old ones that are not in new, then I add the new ones that are not in old.

Example to go from A linked to B,C to A linked to C,D :

OldList : B, C

NewList : C, D

1) Delete link of B because B isn't in NewList

2) Add link of C because C isn't in OldList.

 

In Power apps code, this becomes (selectedItem is item A in our example):

 

Set(OldList; LookUp(Base; ID = selectedItem.ID; RelatedItems));;
Set(NewList; DataCardValue.SelectedItems);;

//Step 1 - Delete old links
ForAll(OldList As _item;
If(Not(_item.Id in NewList.Id);
Patch(Base; LookUp(Base; ID = _item.Id);
{RelatedItems:
RemoveIf(Lookup(Base; ID = _item.Id; LinkedItem); Id = selectedItem.ID)
})));;

//Step 2 - New links
ForAll(NewList As _item;
If(Not(_item.Id in OldList.Id);
Patch(Base; LookUp(Base; ID = _item.Id);
{RelatedItems:
Ungroup(
    Table({Column:LookUp(Base; Id=_item.ID; RelatedItems)};
          {Column:Table({Id:selectedItem.ID; Value:selectedItem.Title})});
    "Column"
)
})));;

Patch(Base; selectedItem; {LinkedItem:NewList});;

 

 

Step 2 works, I can add new links to an existing list.

The patch in step 1 does not work (No changes in SharePoint).

 

In fact, what I am trying to do is to remove a reference item from a SharePoint list with Patch function and here are my tests:

 

 

Patch(Base; LookUp(Base; ID = _item.Id);
{RelatedItems: RemoveIf(Lookup(Base; ID = _item.Id; LinkedItem); Id = selectedItem.ID)})
//doesn't work while

Patch(Base; LookUp(Base; ID = _item.Id);
{Title: "aaaa"})
//works and

RemoveIf(Lookup(Base; ID = _item.Id; LinkedItem); Id = selectedItem.ID)
//provides the right list (checked with Concat(...; Id & "-" & Value & "; ") function)

 

Any ideas?

 

Greetings

Alex

 

10 REPLIES 10
yashag2255
Dual Super User II
Dual Super User II

Hi @Alex1857 

 

You can use the below expressions to configure this:

Set(OldList; LookUp(Base; ID = selectedItem.ID; RelatedItems);;
Set(TempList; Filter(Split(OldList,","), Id in DataCardValue.SelectedItems.Id));;
ClearCollect(TempList2; TempList);;Collect(TempList2;DataCardValue.SelectedItems);;
Set(NewList; Concat(Distinct(TempList2, Id),Result&","));;

 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Hi @yashag2255 

 

Thanks for your answer but it wasn't exactly what I expected.

My question is rather, how to remove a row from a table to update a SharePoint field.

 

In other words, how patch this table (a table where we remove an element)

RemoveIf(Lookup(Base; ID = _item.Id; LinkedItem); Id = selectedItem.ID)

like this

Patch(Base; LookUp(Base; ID = _item.Id);
{RelatedItems: RemoveIf(Lookup(Base; ID = _item.Id; LinkedItem); Id = selectedItem.ID)})

 

yashag2255
Dual Super User II
Dual Super User II

@Alex1857 

 

Can you please confirm the type of RelatedItems column in your datasource?

 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@yashag2255 

 

Something like that:

'@data.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpanderReference"

 

In fact it's a SharePoint "Search" field where I allowed multiple values.

The weird thing is that I just remove a row from a LookUp on this field.

But it doesn't work...

 

yashag2255
Dual Super User II
Dual Super User II

@Alex1857 

 

Can you try to use below expression:

 

Patch(Base; LookUp(Base; ID = _item.Id); {RelatedItems: Filter(Choices(Base.RelatedItems); Id in DataCardValue.SelectedItems.ID) } )

 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@yashag2255

 

Thank you but I'm sorry it's not my question.

 

Imagine that you want to go from this

ItemRelated Item
AB, C, D, E

 

to this

ItemRelated Item
AB, C, D

 

just by knowing A (the row) and E (the letter to remove).

 

I tried something like this but it doesn't work

 

Patch(
  Base;
  A;
  {'Related Item': RemoveIf(LookUp(Base; A; 'Related Item'); Id = E.ID)}
)

 

Okay I find something.

 

Instead of using RemoveIf function, I use Filter function like this

Patch(Base; A; {'Related Item'Filter(LookUp(Base; A; 'Related Item'); Id <> E.ID))

I don't know why I didn't think of this before...

 

However, it's still not perfect because I lose the delegation.

In a ForAll loop, I can't declare the LookUp in a variable...

 

I don't know if you have an idea @yashag2255 , keep the delegation without using RemoveIf... Otherwise, I'll close this topic.

yashag2255
Dual Super User II
Dual Super User II

@Alex1857 

 

RemoveIf function does not work in this context. The expression I shared earlier, it was for updating the selected items but now looking at the example, I understood that you want to keep everything but remove the selected option. For that, can you try the below expression?

ClearCollect(ItemsToPatch;Filter(LookUp(Base; ID = _item.Id).RelatedItems; !(Id in DataCardValue.SelectedItems.ID)));;
Patch(Base; LookUp(Base; ID = _item.Id); {RelatedItems:Filter(Choices(Base.RelatedItems); Id in ItemsToPatch.Id) })

 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@yashag2255 

 

Thank you for your answer but we can't use ClearCollect in a ForAll function.

Moreover, I don't really understand your method.

I don't understand why you use DataCardValue.SelectedItems which contains the new links from A.

 

The idea was:

For example, with this list

ItemRelated Item
AB, C
BA, D
CA
DB

If I say A is now linked to C and D, the table becomes

ItemRelated Item
AC, D
BD
CA
DA, D

 

and for row B, it was necessary to remove A, what I couldn't do with my old method.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Top Kudoed Authors
Users online (1,701)