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

Patch not updating on null values in database tables ?

Noticed this in an app I am designing, so tested from a blank new app and new table.

 

1° Created on Sql server a table :

CREATE TABLE [dbo].[TestTable](

[ID] [int] IDENTITY(1,1) NOT NULL,

[TESTFIELD1] [nvarchar](50) NULL,

[TESTFEILD2] [nvarchar](50) NULL,

with ID as primary key & identitiy specification

 

2° created a record :

ID TESTFIELD1 TESTFEILD2
1 DummyValue NULL

 

3° Connected this table in powerapps and loaded the table into a collection

ClearCollect(collection1,'[dbo].[TestTable]')

 

> when patching on TESTFIELD2 nothing happens (values stays null)

Patch(collection1,First(collection1),{TESTFEILD2:"DummyValue2"})

updating however same or other field with a value (or zero-length string) works fine.

Same behavior seems to be the case when patching directly on a datasource (instead of going through collection).

 

Spent hours on this :-( Would like to understand.

Anybody experienced the same ?

 

Br,

Tom.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Patch not updating on null values in database tables ?

Yes, this is something that I've seen before. PowerApps doesn't handle nulls well.

As you've noticed, updating zero-length string fields work fine. Therefore, the way I usually work around this problem is to update the null values to an empty string in the local collection like so:

 

ClearCollect(collection1,'[dbo].[TestTable]'); 
UpdateIf(collection1, IsBlank(TESTFEILD2), {TESTFEILD2:""})

After you do this, your patch function should work.

3 REPLIES 3
Super User
Super User

Re: Patch not updating on null values in database tables ?

Yes, this is something that I've seen before. PowerApps doesn't handle nulls well.

As you've noticed, updating zero-length string fields work fine. Therefore, the way I usually work around this problem is to update the null values to an empty string in the local collection like so:

 

ClearCollect(collection1,'[dbo].[TestTable]'); 
UpdateIf(collection1, IsBlank(TESTFEILD2), {TESTFEILD2:""})

After you do this, your patch function should work.

treniers
Level: Powered On

Re: Patch not updating on null values in database tables ?

thanks, good work around. (though feel this is quite basic I wouldn't expect in PowerApps :-) )

ChrisHH
Level: Powered On

Re: Patch not updating on null values in database tables ?

Is there a quick way to do this with all columns or will I have to use this expression for each one?

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Tomorrow, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 222 members 4,798 guests
Please welcome our newest community members: