ArcGIS Pro Select By Attribute IS NOT NULL

9918
14
Jump to solution
03-26-2018 01:24 PM
JoeBorgione
MVP Emeritus

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

That should just about do it....
0 Kudos
1 Solution

Accepted Solutions
KoryKramer
Esri Community Moderator

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?

View solution in original post

14 Replies
KoryKramer
Esri Community Moderator

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?

JoeBorgione
MVP Emeritus

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

That should just about do it....
0 Kudos
JoeBorgione
MVP Emeritus

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

That should just about do it....
KoryKramer
Esri Community Moderator

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?

DanPatterson_Retired
MVP Emeritus

Hmmmm

works for me

latest Pro version

JoeBorgione
MVP Emeritus

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

That should just about do it....
0 Kudos
DanPatterson_Retired
MVP Emeritus

... Feature Service ....

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

0 Kudos
KoryKramer
Esri Community Moderator

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?

JoeBorgione
MVP Emeritus

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

That should just about do it....