Set DatePIcker to Blank and Update Data Source

[seraph787]

Hi,

I'm developing a task tracking app.  However, I can't seem to find a way to remove the date when the task is completed.

I have an Excel Table called providerTable, gallery with an editable DatePicker called dpTaskFU, and a button called btnReset-dpTaskFU with a Text value of: "Complete"

For the dpTaskFU properties:

OnChange is set to:

Patch(providerTable,ThisItem,{'taskFU-Date': Value(DateAdd(dpTaskFU.SelectedDate,-TimeZoneOffset(),Minutes))})

Reset is set to:

'btnReset-dpTaskFU'.Pressed

And the OnChange doesn't seem to fire when the reset goes off successfully.  So I'm trying different things with the button like setting the OnSelect to...

Patch(providerTable,ThisItem,{'taskFU-Date': dpTaskFU.SelectedDate})

Patch(providerTable,ThisItem,{'taskFU-Date': ""})

Patch(providerTable,ThisItem,{'taskFU-Date': IsBlank()})

Patch(providerTable,ThisItem,{'taskFU-Date': IsBlank("")})

No matter what I do, these blank dates are not accepted (it says it's looking for a date value).

I just need to set the field in my excel table to a "" blank value when the task is completed.  Any ideas on how to accomplish this?

[CarlosFigueira]

Can you try using the Blank() value?

Patch(providerTable,ThisItem,{'taskFU-Date': Blank()})

[seraph787]

Hi Carlos,

That seems to at least remove the error in the button but now the button no longer clears the datepicker and the button also does not make any changes in the excel data table.

Now after clicking the button using Blank(), the datepicker does not clear and there's an error in the datepicker instead of the button.  "Invalid use of '.'"

I am unable to clear the error during the session after clicking the button.  If I save the app, close it, and open it again - the error is gone until I press the button with that formula again.

[seraph787]

I tried a few more things today but still no luck.  Thought I'd at least add what I've tried in case it gives ideas?

I tried the following in button to try and manually patch a blank value:

Patch(providerTable,ThisItem,{'taskFU-Date': Value(DateValue(Blank()))})

This at least did something.  It changed the date to 1/1/1970.  Why that date, I have no clue.  Why any date at all when it's just the Value of a DateValue of Blank()... So I'd like to just be blank 😞

If anyone at all has any ideas on how to force a blank into a data field that I use with a datepicker control, I'd really appreciate it. 🙂

[tianaranjo]

@seraph787 - I've reached out to the community with the same need, (except I am posting to SharePoint), with no luck.  I wanted to comment here so I can follow this discussion and try any solutions suggested.  If I find a way to resolve within my app, I'll pass your way.

[v-xida-msft]

Hi @seraph787,

Could you please share a screenshot of your PowerApps app's configuration?

Is the taskFU-Date column is a column within your Excel table (providerTable)?

I have made a test and the issue is confirmed on my side. I afraid that there is no way to set the field in your Excel table to empty using the formula within the OnChange property of the DatePicker control in PowerApps currently. If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

Please also check if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Clear-Data-Values-leaving-them-blank/td-p/572...

Best regards,

Kris

[seraph]

Thanks.

Someone has already posted this idea (but for both date and numeric values).

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Ability-to-set-update-a-Datasource-field-value-t...

Hope it's an easy bug to fix.

[seraph]

I'll add screenshots later. Did you just want screenshots of the side bar properties section of the date picker and button?

[Anonymous · Accepted solution]

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.