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

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)})

 

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

 

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

@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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,851)