topic Re: ArcGIS Pro Select By Attribute IS NOT NULL in ArcGIS Pro Questions

ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Mon, 26 Mar 2018 20:24:08 GMT

Simple selection: where field_name is not null. Notice I have the 'show all fields' button active.

But <null> appear as selected and it does not matter if I have show all records or show selected records active: they all appear as blue, selected. The number of selected records seems correct: emphasis on 'seems'....

When selecting for 'IS NULL', those records that are null don't show as selected when 'show all records' is active:

Again, the number of selected records 'seems' to be correct. If I toggle to 'show selected records' I get the correct selected records (no 'is not null' records are 'blue')

KKramer-esristaff‌

Re: ArcGIS Pro Select By Attribute IS NOT NULL

KoryKramer — Mon, 26 Mar 2018 22:49:40 GMT

What is the data source? Feature service, file geodatabase, enterprise gdb, shapefile?

If you activate a cell to edit it, is it blank or does it actually say <Null> ?

In the first example above, the field actually contains text that says <Null>

In the second example, the field is actually null.

Can you check that and let us know what happens?

Re: ArcGIS Pro Select By Attribute IS NOT NULL

DanPatterson_Retired — Mon, 26 Mar 2018 22:53:47 GMT

Hmmmm

works for me

latest Pro version

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Tue, 27 Mar 2018 01:14:55 GMT

The example is a feature service in our local portal. I'll take a second look in the morning as you suggest.

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Tue, 27 Mar 2018 01:17:03 GMT

Well Dan; beats me! I thought maybe it was some obscure setting under file/options but didn't see anything there. I'll do some more testing in the morning...

Re: ArcGIS Pro Select By Attribute IS NOT NULL

DanPatterson_Retired — Tue, 27 Mar 2018 02:11:01 GMT

... Feature Service ....

Remember, I work local, so you have already limited the problem to those cloudy things

Re: ArcGIS Pro Select By Attribute IS NOT NULL

KoryKramer — Tue, 27 Mar 2018 13:54:22 GMT

Joe, I realize you said local portal, but as a quick test I tried this with a hosted feature service on ArcGIS Online and the selections appear to be working as expected:

I think this one is definitely worth a call to technical support. Things we'd probably want to dig deeper on are 1. are you referencing data on your own enterprise gdb - what is the RDBMS? 2. If you run the selection on the local data (same database used to generate the service) what are the results?

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Tue, 27 Mar 2018 14:00:58 GMT

Here is a screen shot of an 'is null' selection on a file geodatabse:

Note the two records I have highlighted...(edited: I meant to highlight the blue <Null> record, not the 'Active' value...)

This record 'says it's null' but you are telling me it isn't really null.

And this record because it's blank is really <null>

Now you need to sell me on why this approach to data display is a good thing. I'm all ears! Call me crazy but when I see <Null> in a data field, I take it for face value....

(BTW, you got up a little earlier than me- as I was putting this response together, I see you added a post about an AGOL feature service...)

Kory Kramer‌

Dan Patterson‌

Chad Kopplin‌

Re: ArcGIS Pro Select By Attribute IS NOT NULL

KoryKramer — Tue, 27 Mar 2018 14:16:35 GMT

OK, if I followed the flow and screenshots there (and thank you a million times over for screenshots, as things like this are nearly impossible to follow without them) it does look like you have records in your data that actually contain the text "<Null>" . Where did the data come from to populate that database?

If that is the case, do a new selection to select all records with an actual text value = "<Null>"

Then Calculate Field field_name = None (by default, the Expression Type should be Python 3, no quotes around None should give you actual null values in the cells of the selected records).

Run the Select by Attributes again to check. Does that work?

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Tue, 27 Mar 2018 14:18:59 GMT

Here is an is null selection on the source enterprise gdb (SQL Server 2014, ArcGIS Server 10.4.1). My user on this data base only has read privileges so I can't get the pencil/editor icon to turn on. Notice the record where permit_id = 55.....

edited moment later: Just for fun we did a query in SQL server manager for permit_id is null. That query returns 2016 records: my query in ArcGis Pro returns 2017: the extra record being the permit_id = 55. I'm not feeling warm and fuzzy about this....

and edited a few more moments later: the same query in ArcMap 10.5.1 on the same data returns the same results as the SQL server manager query.....

Re: ArcGIS Pro Select By Attribute IS NOT NULL

KoryKramer — Tue, 27 Mar 2018 14:26:10 GMT

Thanks, Joe. I think it would be best to log a case with technical support as this is going to take some specific troubleshooting on that data/database. Thanks.

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Tue, 27 Mar 2018 14:52:21 GMT

Kory- I'll do that and let the community what I come up with.

Thanks!

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Wed, 28 Mar 2018 15:25:22 GMT

Late yesterday afternoon, I came to realize that what Kory suggested is actually true. In this particular database I've been using there are text strings that contain the 6 characters of <Null>. That problem is seemingly systemic in this database as I found tables that suffered from the same affliction.

Because the authoritative feature/tables are then used for feature services, AGOL, and even file geodatabases, the issue was pervasive across all data platforms giving the appearance to me that it was an ArcGIS Pro problem, when it was in fact a data problem.

Last night I had a nice large plate of crow for dinner and my lovely wife baked a delicious humble pie....

(view in My Videos)

@Kory_Kramer

Re: ArcGIS Pro Select By Attribute IS NOT NULL

ChadKopplin — Wed, 04 Apr 2018 16:26:32 GMT

Joe, thank you for the information, I believe that this could be an issue, especially if people are bringing nonspatial tabular data or spatial tabular data that was created in EXCEL or some other DB format. This makes sense, after reading the chain. There have been times years ago where I have calculated the field to Null, in stead of clicking the box in the geodatabase that allows you to accept Null values, and I have seen the issue that you posted, not knowing the difference.

Re: ArcGIS Pro Select By Attribute IS NOT NULL

JoeBorgione — Wed, 04 Apr 2018 16:57:51 GMT

Chad- I've encountered this issue before and will probably encounter it again I'm sure. There a a lot of GIS practitioners out there that just don't grasp the concept of a true <Null> value in a database; that's okay, but I'll do what I can to explain it and the virtues associated with using it!

Chad_Kopplin‌