Insert NULL option into dynamic dropdown list

[TheSmitchell]

I want to insert the option to set a field to NULL for a DropDown list box. I attempted to put the option in the Additional Choices (pre) field for the control. They don't show up. Nothing I put in the additional options (pre) or (post) fields appears in my dynamic dropdown box. What am I missing?

---
Sarah

[TheSmitchell]

Re: Insert NULL option into dynamic dropdown list

So. I got a bit creative, I guess, and found a way to do this using UNION and SELECT statements. And also some views.

Exhibit A:
UNIONing NULL as var: http://stackoverflow.com/questions/8...on-null-as-var

The syntax for adding NULL in as an option for a list of whatever from your database is more or less as follows (this is for MySQL; syntax will vary on other systems, maybe):

(SELECT varA, varB
FROM some_table)
UNION
(SELECT 'None' AS varA, NULL AS varB
FROM some_table)

This effectively gives you a list of all varA and varB in some_table with an extra result where varA = 'None' and varB = NULL.

Problem:

You can't put parentheses around SELECT syntax in the Alpha Five SQL Query Genie. It issues parsing errors. ::sad pandas::

Solution:

Put your UNION in a view in your database. Then, when setting up your dropdown box, use that view as your dynamic list.

Exhibit B:
How to insert a NULL value into a Grid Numeric Field: http://wiki.alphasoftware.com/How+to...opdown+Control

dim args as sql::arguments 
args.xml = e.argsxml
dim id as n
'The argument name for the 'Response1' value is 'New_Response1'
id = args.ArgumentNumber("new_Response1")
if id > 0 then 
    if args[id].data = -1 then 
        args[id].isNull = .t.
    end if 
    e.argsXMLModified = args.xml
end if

You'll need to change args[id].data = -1 to args[id].data = 0 as NULL is interpreted as 0 in the dropdown. I suppose you could change your UNION to use some value not NULL and decidedly NOT in the table already instead of using NULL since NULL and 0 are going to conflict here...

Also, "Response1" refers to the Column/field value that is being updated. In my case, I was updating a field called "Collection", so I changed "new_Respons1" to "new_collection". Tested it out. Everything worked brilliantly!

Most exciting. Problem solved.

---
Sarah