Re: What types of apps is PowerApps appropriate fo... - Page 3

whitekn3 · ‎10-10-2017

I have a lot of experience with Access VBA, C++ development, and limited experience with C# and XAMARIN Forms. I am brand new to PowerApps. I am interested in learning more about PowerApps and have a project that would involve about 10 lookup tables, 4 or 5 main tables, Two moderate forms (Sub forms, simple code), and several single table minor forms for maintaing the lookup tables.

Does this sound like something PowerApps could handle?

Any limitations? Gotchas? I ought to be aware of before using PowerApps? At what level of complexity does it choke?

whitekn3 · ‎10-19-2017

Can you write a Zero Length string back to the Azure SQL Database? (I haven't used Azure SQL, but I'm assuming you can set a DISALLOW_ZERO_LENGTH constraint on the Azure SQL table.column and that it would not be set for the column in question.)

If so, that would be acceptable for my purposes. Having to write a dummy value, such as '_NA' would not make me happy. My experience has been that such data often creates additional complexity in the code and SQL.

As I'm considering PowerApps for "simple" field updates of data also used by very complex desktop apps I can't easily change the tables.

Anonymous · ‎10-19-2017

@JRaasumaa

Well, with all due respect I am infinitely familiar with the definition of Null, and Zero Length strings for that matter.

Not sure you understood what I posted - what I was saying?

Also:

"it's just not supported to write the value Null to a field in SQL"

You are referring only to Azure SQL, correct? Because it certainly is supported in non Azure (eg on prem) SQL Server databases. If it's not supported in Azure, it certainly should be. I *never* use ZLS or allow ZLS in any field (this is an option in Access).

Regarding:

"You could write your app to write a blank value "" or 0 to an integer field etc"

Sorry, but that is not good practice. A ZLS is of course not the same as a Null, and they are visually indistinguishable - which can result in a lot of confusion in queries where you have criteria against a field that is supposed to be (ie expected to be) Null, but someone has entered a ZLS. oops!

And substituting a zero (0) in an integer field that is supposed to be Null ... similar problem. Now you are putting real data (0) in a field that s/b (ie expected to be) Null.

I have not yet (but soon) worked with an Azure SQL db to confirm any of this.

Meneghino · ‎10-19-2017

Hi @JRaasumaa IMHO it is a bug. Once a field is set to a non-null value, then it cannot be set back to a null value by PowerApps. This is not a limitation of Azure SQL Database, which works perfectly.

The documentation for the Blank() function clearly admits this is a limitation (euphemism for bug):

"Note: At this time, storing blank values is supported only for local collections. We know that many data sources support blank (NULL) values, and we're working to lift this limitation."

"" is not the same as null, and 0 is also not the same as null

Meneghino · ‎10-19-2017

Hi @whitekn3, yes you can write back a "" to Azure SQL Database.

It is a work-around, but hopefully at some point we will be able to use blank.

I fully agree with @Anonymous

JRaasumaa · ‎10-20-2017

@Anonymous wrote:
@JRaasumaa
Well, with all due respect I am infinitely familiar with the definition of Null, and Zero Length strings for that matter.
Not sure you understood what I posted - what I was saying?

Also:
"it's just not supported to write the value Null to a field in SQL"
You are referring only to Azure SQL, correct? Because it certainly is supported in non Azure (eg on prem) SQL Server databases. If it's not supported in Azure, it certainly should be. I *never* use ZLS or allow ZLS in any field (this is an option in Access).

Regarding:
"You could write your app to write a blank value "" or 0 to an integer field etc"
Sorry, but that is not good practice. A ZLS is of course not the same as a Null, and they are visually indistinguishable - which can result in a lot of confusion in queries where you have criteria against a field that is supposed to be (ie expected to be) Null, but someone has entered a ZLS. oops!
And substituting a zero (0) in an integer field that is supposed to be Null ... similar problem. Now you are putting real data (0) in a field that s/b (ie expected to be) Null.

I have not yet (but soon) worked with an Azure SQL db to confirm any of this.

@Anonymous With all due respect your post made it sound like you did not which is why I tried to explain it to you. Writing a blank value or a 0 could absolutely be good practice if your data sets and filtering work to adjust for this. It depends on the circumstances of the app and what I gave was a perfectly acceptable solution to the fact that Null cannot be written in certain situations, in fact we use blank fields all the time for a string and it works absolutely fine. We also use a 0 in cases that the value should either be Null, 0 or always greater than 0, there is nothing wrong with that specific data set having a 0 instead of a Null and the filtering we use in PowerApps plans for this.

All of this literally depends on the app and data that's being worked on, my suggestion and solution works.

JRaasumaa · ‎10-20-2017

@Meneghino wrote:
Hi @JRaasumaa IMHO it is a bug. Once a field is set to a non-null value, then it cannot be set back to a null value by PowerApps. This is not a limitation of Azure SQL Database, which works perfectly.

The documentation for the Blank() function clearly admits this is a limitation (euphemism for bug):
"Note: At this time, storing blank values is supported only for local collections. We know that many data sources support blank (NULL) values, and we're working to lift this limitation."

"" is not the same as null, and 0 is also not the same as null

@Meneghino

It's not a bug in that the software is doing exactly what it is supposed to, it's a limitation.

A limitation is not a bug, it's the current design of the software, not a flaw, error, fault or failure of the software (bug).

Because of this my suggestion was a workaround that is a perfectly acceptable solution depending on the data and the app to resolve this. Please see my post above.

Anonymous · ‎10-20-2017

Here is another perspective:

In working with Microsoft Access for 25 years now, Access handles and supports Nulls quite well.

In a Table, when you define a new Field's Data Type as Number, Text, Memo, DateTime and a couple of others, unless you explicitly set a Default Value at the Table Level, the initial value of that field in each row is Null. Additionally for a Text Data Type, you can specifiy 'Allow Zero Length', which I would rarely do (importing data into this field from Excel might be an exception).

So at any time when updating a given Field (of the types noted above), you can write real data or a Null. You can overwrite real data with a Null, or vice-versa.

This is certainly the same case for SQL Server, as I do this all the time. However, I cannot (yet) speak for an Azure SQL db. But I find it hard to believe Azure does not work the same way.

So maybe we are all talking about something different. And there is some anamoly in PowerApps regarding Nulls and Azure. But if for whatever reason from within PowerApps you cannot write a Null to an Azure SQL db Field (eg a Text Data Type) - even though when defining that field it will initially be Null ... that ... is a serious problem, bug or otherwise.

Anonymous · ‎10-20-2017

@Meneghino

RE: " Once a field is set to a non-null value, then it cannot be set back to a null value by PowerApps."

So ... if this is the case ... then this is *definitely* a SERIOUS problem, bug or otherwise. So I totally agree.

Meanwhile though... writing a zero (0) or "" etc., as a workaround ... when a Null s/b written (if that is the case), then you are basically writing 'fake data' .. which is bound to bite one in the ass down the road, especially when it comes to querying that data for a report, etc., and criteria is filterering for and expecting a Null ... OOPS !

Microsoft needs to address this (basic) issue ASAP if not sooner.

JRaasumaa · ‎10-21-2017

@Anonymous wrote:
Here is another perspective:
In working with Microsoft Access for 25 years now, Access handles and supports Nulls quite well.
In a Table, when you define a new Field's Data Type as Number, Text, Memo, DateTime and a couple of others, unless you explicitly set a Default Value at the Table Level, the initial value of that field in each row is Null. Additionally for a Text Data Type, you can specifiy 'Allow Zero Length', which I would rarely do (importing data into this field from Excel might be an exception).

So at any time when updating a given Field (of the types noted above), you can write real data or a Null. You can overwrite real data with a Null, or vice-versa.

This is certainly the same case for SQL Server, as I do this all the time. However, I cannot (yet) speak for an Azure SQL db. But I find it hard to believe Azure does not work the same way.

So maybe we are all talking about something different. And there is some anamoly in PowerApps regarding Nulls and Azure. But if for whatever reason from within PowerApps you cannot write a Null to an Azure SQL db Field (eg a Text Data Type) - even though when defining that field it will initially be Null ... that ... is a serious problem, bug or otherwise.

I think it's great you've worked in MS Access for 25 years, I too have had decades of experience in databases and programming languages (but I don't give myself a nickname based on it), but keep in mind, this is not MS Access.

Putting a "" or a 0 into a column is NOT "fake" data (as you say in another post) IF the data set warrants this type of a workaround. There is NOTHING wrong with doing this as a resolution. It would be great if you focus on that instead of shouting that it's not a real Null. Depending on the data you may not need to write a Null and can work around this current limitation with this workaround and I will continue to offer up as a solution to people who need help with this.

Hopefully they resolve this limitation soon but for now there are certainly ways for people to work around it depending on their data, lets all focus on helping people with workarounds and pushing Microsoft and the PowerApps team to coming up with solutions to this products current limitations.