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

[Alex1857]

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 :

Item	Related Item
A	B, C
B	A, C, D
C	A, B
D	B

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

[yashag2255]

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!

[Alex1857]

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]

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

[Alex1857]

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

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

[Alex1857]

@yashag2255

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

Imagine that you want to go from this

Item	Related Item
A	B, C, D, E

to this

Item	Related Item
A	B, 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)}
)

[Alex1857]

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]

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

[Alex1857]

@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

Item	Related Item
A	B, C
B	A, D
C	A
D	B

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

Item	Related Item
A	C, D
B	D
C	A
D	A, D

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