6

Value maps have been set for some attributes in the properties of a layer (which is an offline edit of a PostGIS database saved as a spatialite file).

enter image description here

But in the drop down selection it does not allow for a Null to be selected. So if a user selects one of them, there is no way to undo the selection.

enter image description here

I have tried entering both Null, and NULL, into the value map, and having an extra blank row.

enter image description here

The extra blank row does give me a blank option,

enter image description here to give enter image description here

but the value in the field is '' rather than NULL.

enter image description here

And selecting NULL gives me 'NULL' with the single quotation marks

enter image description here

Edit 1

In response to answer Bhaskar

When I run this SELECT query, I would like to have just these 4 values

enter image description here

not,

enter image description here

How can set it up so that a user can essentially deselect a value and return it to its previous NULL state?

EDIT 2

I have tried to run the SQL suggested by Bhaskar to just swap out a inserted NULL through the value map.

INSERT INTO awrtracker.awr_tracker_sync (threat_lvl) VALUES (NULL) where threat_lvl is 'NULL';

In place of the final 'NULL' in the expression I have tries 'Null' and 'null'. But the error is the same "ERROR: syntax error at or near "where".

It's an SQL problem isn't it, from what I have researched the WHERE statement is invalid here for PostgreSQL?

5
+50

Implemented the value maps of Edit Widget Property on one of Reservoirs layer. I have an attribute with type of reservoir (this water body could be Reservoir, Dam, Anicut or even null) and I had no problem with it.

Initially, I clicked on Load Data From Layer button and selected my layer from the Layer dropdown. Then selected type attribute for both Value and Description. The important thing was to check the Insert Null value on top checkbox. this will actually automatically add null values to the list.

Edit Widget Properties - Load Values from layer

After clicking Ok, I got the values as follows:

Loaded Values

Then you will be able to select values from the dropdown with null values as well. had no problem changing from null to values and from values to null.

Drop down values

Here is a video of what I did, Gives a clear picture how I did it. :

https://youtu.be/qpJ9Etlop-Y

With reference to your further edits in question and with my 10 years of software development experience, the inference i could make from the Value Map is that it cannot be changed with respect to the insert statement that its making with null values. The tool has been programmed to insert the null values as string. Unless and until someone is capable of reprogramming the tools to their required needs, the null insertion cannot be changes. If i am wrong then someone can correct me. But there is an alternative.

As you mentioned you only need to display other values except NULLs in the select query statement, there is a workaround. what you need to do is to initially change the NULL as string to null through UPDATE statement and then execute your select query.

first run the insert statement and then execute the select query. then you will get the desired results as you mentioned.

Have recreated the situation with the data I have with reservoirs. Actually you need to implement update statement instead of insert. I inserted null values into reservoir database from the dropdown created using value maps. have a look at the two tables in the snapshot below:

enter image description here

Here is a query I ran after updating the null values from the dropdowns. you can notice that null values have been displayed for results section of the query for distinct values:

enter image description here

Then I updated the database using update statement like in the snapshot and then ran the sql statement for distinct values. Got the results like desired and like displayed in the figure below:

enter image description here

Change your sql statement like this:

UPDATE awrtracker.awr_tracker_sync SET threat_lvl=null where threat_lvl='NULL';
  • I appreciate the time you have put into your answer, unfortunately it still gives me the same issue as described in my question. I perhaps hadn't made it clear what I am after, so I have added some extra explanation at the end. Having a NULL field as you have described does at least achieve the aim of giving the user NULL as an option. Only the NULL value it adds is distinct from the NULL that is in the 'untouched' fields. – Martin Hügi Mar 18 '17 at 12:46
  • @MartinHügi - That makes it a bit clear. will edit my comments further with a snapshot. – Bhaskar Reddy Pulsani Mar 18 '17 at 15:19
  • Ok, thanks. That probably explains it then. The 'awr_tracker_sync' table is user editable, so I do run an update script to first check this sync table for errors, and then to update the changes made by users to an awr_tracker_master table. I could add in a line as you suggest to change the 'NULL' into NULL at this point. I do get a syntax error with your INSERT INTO script, something to do with WHERE. Did this script run for you? – Martin Hügi Mar 18 '17 at 16:22
  • if you can share your database with me then i will be able to make it work – Bhaskar Reddy Pulsani Mar 18 '17 at 17:39
  • That is a very kind offer, but I won't be able to do that as its very large and contains protected work data. I've been trying various SQL and researching along the line of what you have suggested, but at present haven't managed to get it to work. – Martin Hügi Mar 18 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.