cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

SharePoint Lookup Fields - Setting to Null in PowerApps

Hi Power Apps community,

 

I was hoping someone might be able to help, I've been trying to clear the value from a SharePoint Lookup Field using Power Apps.

 

The simplest way I can explain to recreate the steps are:

  1. Create a field in a SharePoint list that lookups a value from another list (in this case it's an address list) I have not made this a required field
  2. Set the new and edit form to use Power Apps, save and publish the app
  3. I go to modify an existing item that has a lookup and clear the lookup from the field
  4. Save the change and the lookup is still present.

This is actually related to a much larger app I'm creating but the above was the easiest way to explain how to replicate the issue. Essentially I need a way to set the field to Null, which I would in CSOM and all would be ok.

 

I've tried various methods e.g. using Blanks() and {} inside setting the value manually, e.g. below:

 

{'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
           Id: Blank(),
           Value: Blank()
}

 

 

At the moment the workaround I'm using is having a blank address in my address list and if I need to update a record to no longer be associated with an address I set it to this placeholder. It's not ideal and I would prefer to just clear out the lookup column!

 

Many thanks for any help,

 

6 REPLIES 6
Highlighted
Super User III
Super User III

@Pyman 

This is tricky with SharePoint!  

Try setting the Id to -1 instead of Blank()

Also, in the past, we found that the Formula-level error management experimental feature in settings would impact this result.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

Hi Randy,

Thank you very much for the reply, I had seen the -1 setting but when I have tried that I always get the following error 

"The requested operation is invalid. Server Response: "The list item could not be inserted or updated because invalid lookup values were found for the following field(s) in the list: [Address ID]"

With Address ID being the lookup field I'm trying to blank.  The data I'm sending is (also tried with Value being ""):

 

{ '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id : -1, Value: Blank()} 

 

 

I flicked on the Formula Level error management and tried again with the -1 Id and get the same response.

Anything else I could try? 

Many thanks

Highlighted

@Pyman 

I'm not sure if anything has changed in that arena over the past year or so when we first encountered it, but it seemed hit or miss depending on the column type and other factors.  Ultimately we abandoned even trying and just worked around it from the list itself with a text column...redundant, yes, but much easier to work with.  We used Power Automate (Flow at the time) to keep everything in sync between the two columns - except again, you never could "blank out" the actual Lookup column.

Not really the best solution, but it is effective.  If others have a way, please share.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Highlighted

Thanks Randy,

 

It would be great to know if others have workarounds to suggest too!

Highlighted

@Pyman 

I run in this issues today when I tried to have a form returned a null value. This thread kind of helped me find a solutions, but a little different.

 

What you can use is:

 

{Id:Coalesce(Combo.Selected.Id, -1)}

 

 

No need to use the Odate part 😉

If you don't need to reset the value there are also a few ways to save the date without the OData part, for example you can do:

 

LookUp(Choices(List.RelatedList), Id = Combo.Selected.ID)

 

 

Hope this helps you.

 

Paul

Highlighted
Resident Rockstar
Resident Rockstar

Extra tip, because you are building a big app. You say you have a edit and new form in your app. You better have just one form and use NewForm(Form), EditForm(Form) and ViewForm(Form) formulas to set the state of this one form.

Also, a good approach (in my opinion) is to enable Container Control in advance settings. This is an experimental option but around for more than two years now, so okay to use. Then you have a (main) Container. In that container you put two containers one for your icons (which controls submit, cancel, close, edit mode operations) and one for the Form. You use Parent en Self a lot to scale all controls inside the main control. The main control handles the visibility, dimensions, and border and Fill colors. Just some advice 😉

 

Good luck,

 

Paul

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (16,366)