Nulls Dnd

[Miss Ruhnke]

I am noticing that data in the Field Maps App is being submitted with nulls in required fields. The layers have a mix of required fields with domains and type-in. I uploaded my Web Map from ArcGIS Pro 3.13, where I set the domains and requirements within a FGDB. I also allowed attachments, Editor Tracking and GPS metadata to the editable layers.

I reuploaded the map I was working on, thinking it was a funky one-time glitch, but not only is it the new uploaded Map still allowing required fields to be submitted with nulls, I am now noticing that older maps that have been in use are having this issue..

I had this problem with my data and solved it by changing the properties for the required fields so that they do not have the "Allow NULL" option checked (highlighted in yellow). Note that you can only change that property on an empty table.

I am not sure if this is still the case, but in much older versions of Field Maps, we did find that a user could sometimes navigate around the form enough and sometimes get a required field to lose that designation. I believe that was many, many versions ago though.

If it's the latter case, unless this has changed in a release without me knowing, users can also use the Edit Multiple tool which does not enforce the form logic - so you would still need to go the route to change its behavior in the underlying layer or table for this scenario.

We are also seeing this. Fields with allow null=false are letting it submit on Field Maps (iOS/Android). The REST service shows Nulls=false on the field. I may have narrowed it a bit - it only affects a field that has domains. Double and Date fields seem to default to whatever the lowest value is in the that attribute field. So, 1899 for Date. (I set blank data to that date intentionally) and it defaults to that. And 0 for 'turns' because that is the lowest value there. I wish they stayed blank and then yelled at the end user, instead. But, at least I will know 1899 is a junk data entry. Again, would be more useful to stay blank and prompt users to enter a value.

But the text field with domains, "Condition" stays blank; but it happily lets users Submit. Even though it shouldn't. This is SDE on prem in SQL Server on ArcGIS Enterprise server 10.9.1. My hunch is maybe this is a bug that is fixed in Enteprise 11.x or if not, it will be fixed at some point.

I see Nulls=false on the Field Maps 'Designer'. I was not able to toggle in Designer as it was greyed out and hard-coded to Require. (using Field Maps Designer to Require fields with Domains DID work, however, interestingly. Those fields allowed nulls at the SDE eGDB level; but using Designer to override and not allow nulls ie Require input, worked) The fields are in a related table, to be used on Inspections. So I am thinking, maybe it's when fields have Domains and on Enterprise 10.9.1? I also tried using the webmap through Portal 10.91 same result, as well as seeing this when accessing the service through a webmap on ArcGIS Online. Also escalated this as a Case with TS. Having 'required' fields is vital for inspections work so that we ensure users enter key info such as Date Inspected, Inspector Name, Condition, etc.

The same happens also in 11.1. However, if a field remains Null = True in SDE, but only checked as "Required" in the field map design, it does not let you save the data, until the required field is filled out.

In SQL and Relational Theory (C.J. Date, 2009) chapter 4 advocates avoiding duplicate rows, and also to avoid NULL attributes in the data we store. While I have no troubles avoiding duplicate rows, I am struggling to see how I can model data without making use of NULL. Take the following, for example - which is a bit from work.

We have an artist table, which has, amongst other columns, a gender column. This is a foreign key to the gender table. However, for some artists, we don't know their gender - for example we've been given a list of new music which has no descriptions of the artist. How, without using NULL is one meant to represent this data? The only solution I see is to add a new gender, "unknown", to the gender table.

Which is a real shame - because this was the solution I was waiting to read about! There is a reference to read the appendix which has lots of publications to read, but I was hoping for a little bit more of a down to earth summary before I dived into reading these.

Now, take the example that s.city is London, and p.city is Paris. In this case, London Paris, so the query is true. Now take the case that p.city is not Paris, and is infact xyz. In this case, (London xyz) OR (xyz Paris) is also True. So, given any data - this query is true. However, if xyz is 'NULL' the scenario changes. In this case both of these expressions are neither True nor False, they are in fact, Unknown. And in this case because the result is unknown you will not get any rows returned.

The move from 2 value logic to 3 value logic can easily introduce bugs like this. Infact, I just introduced one at work which motivated this very post. I wanted all rows where the type != 0 However, this actually ends up matching type == 0 OR type IS NULL - confusing behavior.

Whether or not I model my data with or without NULL in the future is unclear, but I'm very curious what the other solutions are. (I too have always been of the argument that if you don't know, you should use NULL).

1.1. Sixth Normal Form is the answer. But you do not have to normalise your entire database to 6NF. For each column that is optional, you need a child table off the main table, with just the PK, which is also the FK, because it is a 1::0-1 relation. Other than the PK, the only column is the optional column.

Look at this Data Model; AssetSerial on page 4 is a classic case: not allAssets have SerialNumbers; but when they do, I want them to store them; more important I want to ensure that they are Unique.

1.2. For each such table, use a View to provide the 5NF form of the table. Sure, use Null (or any value that is appropriate for the column) to identify the absence of the column for any row. But do not update via the view.

1.3 Do not use straight joins to grab the 6NF column. Do not use outer joins, either (and have the server fill in a Null for the missing rows). Use a subquery to populate the column, and specify the value that you want returned for a missing value (except if you have Oracle, because its Subquery processing is even worse than its set processing). Eg. and just an eg. you can convert a numeric column to string, and use "Missing" for the missing rows.

You can use Null substitutes. I use CHAR(0) for character colomns and 0 for numeric. But I do not allow that for FKs. Obviously you need a value that is outside the normal range of data. This does not allow Three Valued Logic.

In addition to (2), for each Nullable column, you need a boolean Indicator. For the example of the Sex column, the Indicator would be something like SexIsMissing or SexLess (sorry). This allows very tight Three Valued Logic. Many people in that 5% like it because the db remains at 5NF (and less tables); the columns with missing info are loaded with values that are never used; they are only used if the Indicator is false. If you have an enterprise db, you can wrap that in a Function, and always use the UDF, not the raw column.

Of course, in all cases, you can never get away from writing code that is required to handle the missing info. Whether it is ISNULL(), or a subquery for the 6NF column, or an Indicator to check before using the value, or an UDF.

It's actually quite easy. You design such that whenever you have to leave some data missing, you can do so by leaving a whole row missing. If a row isn't there, it isn't a row full of NULLs. It just plain isn't there.

So, in the case of "DateOfDeath", we have a table with two columns, namely, PersonId and DateOfDeath. PersonId references Id in the Persons table. If there is no DateOfDeath to be stored, we don't store the row. End of discussion.

If you do an OUTER JOIN between this and the Persons table, you'll get a NULL for the DateOfDeath wherever there was no row. And if you use this in a where clause, you'll get the usual perplexing behavior concerning 3-value logic. If you do an INNER JOIN, the rows for which there is no DateOfDeath will simply disappear from the join.

nulls are a consequence of theory meeting reality and having to be adjusted to be usable. In my opinion attempting to avoid all null values will ultimately lead to uglier and less maintainable code than just using null where appropriate.

A) You have a business reason. For example, in a table of payments, a NULL payment value would mean it was never paid. A 0.00 payment value would mean we intentionally paid nothing. For medical charts, a NULL value for a blood pressure reading would mean you didn't take a BP, a 0 value would mean the patient is dead. This is a significant distinction, and necessary in certain applications.

B) Your queries account for it. If you understand the affect of NULL on IN, EXISTS, inequality operators (like you specified in OP), etc. then it shouldn't be an issue. If you have NULL now in your tables and don't want the value for certain applications, you can employ views and either COALESCE or ISNULL to populate different values if the source table has a NULL.

In SQL Server a nullable column, say a character 10, will take one bit in a bitmap when null and 10 bytes when not nullable. So how does having a null hurt disk I/O. The way it hurts is when a value is inserted into a column where a null used to be. Since SQL did not reserve space there is not room in the row to just put the value so SQL Server has to shift data around to make room. Page splits, fragmentation, updating the RID if this is a HEAP, etc all hurt disk I/O.

Hi! I love the new built-in feature "create nulls from paths." However, sometimes I get an issue where the newly created nulls are not where they should be. Please see the examples below, where the front leg of the bicyclist kicks out four misplaced nulls. Anyone else having this issue? Any workarounds? Fixes? Advice?

c80f0f1006