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

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!

Highlighted
kbirstein1 Advocate III
Advocate III

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

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 Advocate IV
Advocate IV

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.

 

 

ganeshsanap Advocate II
Advocate II

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

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

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

 

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,845)