Update blank/Null value to fields (Date, User, Choice field etc.) in SharePoint list / database

[Anonymous]

Scenario :  I have saved an item to the SharePoint list and later decided to remove data from some fields (Date field, User field etc).  Edit the item and delete the field value and Save it back to datasource (SharePoin list / SQL database)

Issue :  PowerApps does not update the field to empty / null value. The field value remains as such in list.

Solution : Enable the "Experimental features" as shown in below screenshot. This will resolve the issue and you will be able to update field value to null after that.

 

Reference : https://powerapps.microsoft.com/en-us/blog/new-feature-error-handling-and-writing-null-values-to-dat...

[Anonymous]

Hmm... Smart! That should work. I was just considering workarounds and was going to try conditioning everything to the 1800s and making the admin enter a designated ancient date. Your solution would keep things in the realm of common sense. Thanks!

[kbirstein1]

I don't know how useful this is but I found a way to set a SharePoint date time field to NULL by accident! This was the scenario:

I had a Date Picker control (it is not in the Gallery) and I had this PATCH command (on a button in the Gallery):

Patch(SharePointListName,
{ID:ThisItem.ID},

{SPDateFieldName: If(!IsBlank(DatePickerName.SelectedDate),DatePickerName.SelectedDate)}

)

When I clicked the button there was no value in the Date Picker (it was Blank so it didn't meet the criteria of the IF statement) but there was a current value in SPDateFieldName in the SharePoint list. To my surprise when I looked at the list the SPDateFieldName value had been cleared out! I assumed it would remain and only be overwritten if there was a value in the Date Picker control.

I had to change the command to:

Patch(SharePointListName,
{ID:ThisItem.ID},

{SPDateFieldName: If(!IsBlank(DatePickerName.SelectedDate),DatePickerName.SelectedDate,ThisItem.SPDateFieldName)}

)

To preserve the value in the SharePoint date field even when then Date Picker control is Blank.

Maybe not a great workaround but it shows that it is possible to set SharePoint date fields to NULL.

[ktvision]

I think that the following is easier solution.

Update the Default Value and On change properties as below.  It works well.  Hope it helps you.

In the PowerApp,

Date picker Name : dDue

Create a Variable: vDate 

Default Value	If(vDate="Empty",Blank(), If(vDate="NotEmpty", dDue.SelectedDate, Parent.Default ))
On Change	If(IsBlank(dDue), Set(vDate, "Empty"), Set(vDate, "NotEmpty"))

[joshnystrom]

Hey @kbirstein1 and @ktvision,

Thanks for providing your insights on how this limitation might be overcome. Sadly, in testing, I've confirmed that the use of a blank date picker to clear a SharePoint date field is still reliant on the Experimental Feature referenced in the original post - https://powerapps.microsoft.com/en-us/blog/new-feature-error-handling-and-writing-null-values-to-dat....

I had no luck with this prospective solution until I enabled that experimental feature and started a new editing session. Once the feature was initialized (following the all-important reload), I was able to blank the date field. I can reliably break/unbreak this ability to nullify date field entries by disabling/enabling the experimental feature and reloading the app in edit mode. Perhaps you knew this already - this is certainly the only way that I've encountered to clear a date field. I have a few posts in https://powerusers.microsoft.com/t5/General-Discussion/Clearing-out-people-picker-field/m-p/250654 that cover my standard-functionality approach to clearing Choice/Lookup and Person Picker fields.

Thanks,

Josh

[Anonymous]

Hello,

Here is a solution that works for me. The underlying data is Azure SQL though it should also work on prem SQL. Caution: it is not a pretty solution.

Step 1. Create a stored procedure: uspMakeNull. Note: We use IDENTITY column, id_num, as a row identifier in all tables, 

CREATE PROCEDURE [Exodus].[uspMakeNull] @ColumnName nvarchar(255), @TableName nvarchar(255), @ID_num int
AS

DECLARE @SQL nvarchar(max);

SET @SQL = 'UPDATE Exodus.' + @TableName + ' SET ' + @ColumnName + '=NULL' + ' WHERE id_num=' + CAST(@id_num AS nvarchar(255));

EXEC SP_EXECUTESQL @SQL;

GO

Step 2. In Flow, create a flow: MakeNull (see attachment)

 The flow has: 1. PowerApps trigger

                       2. Execute stored procedure block with:

                                     Procedure name: name from Step 1.

                                     ColumnName: Executestoredprocedure_ColumnName (PowerApps)

                                     id_num: Executestoredprocedure_id_num (PowerApps)

                                     TableName: Executestoredprocedure_TableName (PowerApps)

Step 3. In PowerApps, OnChange property for the desired column:

If(IsBlank(dcvDate.SelectedDate), UpdateContext({BlankDate: true}), UpdateContext({BlankDate: false}))

Step 4: In PowerApps, after the SubmitForm statement (I am calling SubmitForm from a button's OnSelect property)

If(
    BlankDate,
    MakeNull.Run(
        "DeathDate",
        frmFormName.LastSubmit.id_num,
        "YourTableName"
    );
    UpdateContext({BlankDate: false})
)

I hope this helps.

[ganeshsanap]

I was able to set the the blank values to SharePoint columns using Blank function like:

Patch('SharePointList', {
    DateTimeCol: Blank(),
    NumberCol: Blank(),
});

[DragonLord]

setting to Blank() does not work .

Cant believe that this is so much hard work!

[marpio]

Seemed not to work with SharePoint... but it actually does. Just leave the date field as it is, and if empty, it will clear the field in SP too!

[cbrazenec]

Use Date(Blank(),Blank(),Blank()).  It returns a Null value, but formatted as a date.

[cbrazenec]

Try Date(Blank(),Blank(),Blank()).  Sets the value to null but in date format.