SharePoint Lookup Fields - Setting to Null in PowerApps

[Pyman]

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,

[RandyHayes]

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

[Pyman]

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

[RandyHayes]

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

[Pyman]

Thanks Randy,

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

[KroonOfficeSol]

@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

[KroonOfficeSol]

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

[TLHost]

Put this code in the Default property of the Card:

If (crdOffice.Visible, ThisItem.Office, { Id: -1, Value: Blank ( ) })

In my case, if the card is visible, use the default value, otherwise set the value to null/blank.  You must return a record for Lookup columns.