cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seraph787 Advocate II
Advocate II

Set DatePIcker to Blank and Update Data Source

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Set DatePIcker to Blank and Update Data Source

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.

View solution in original post

8 REPLIES 8
Power Apps
Power Apps

Re: Set DatePIcker to Blank and Update Data Source

Can you try using the Blank() value?

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

Re: Set DatePIcker to Blank and Update Data Source

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

Re: Set DatePIcker to Blank and Update Data Source

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 Continued Contributor
Continued Contributor

Re: Set DatePIcker to Blank and Update Data Source

@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.

Community Support
Community Support

Re: Set DatePIcker to Blank and Update Data Source

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
seraph
Regular Visitor

Re: Set DatePIcker to Blank and Update Data Source

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
Regular Visitor

Re: Set DatePIcker to Blank and Update Data Source

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

Re: Set DatePIcker to Blank and Update Data Source

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.

View solution in original post

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!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (7,413)