cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joseph1
Level: Powered On

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

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
Level: Powered On

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

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
Level: Powered On

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

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"))
joshnystrom
Level 8

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

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

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

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.

 

 

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,975)