cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sanoj
Advocate I
Advocate I

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

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.

 

 Experimental Feature.png

 

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

19 REPLIES 19
Anonymous
Not applicable

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!

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
Helper I
Helper I

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 ValueIf(vDate="Empty",Blank(), If(vDate="NotEmpty", dDue.SelectedDate, Parent.Default ))
On ChangeIf(IsBlank(dDue), Set(vDate, "Empty"), Set(vDate, "NotEmpty"))

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
Not applicable

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
Super User
Super User

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

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

 

setting to Blank() does not work .

Cant believe that this is so much hard work!

marpio
Frequent Visitor

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
New Member

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

cbrazenec
New Member

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (65,037)