How to allow selecting a NULL value in a TDBLookup

Question

I have a TDBLookupComboBox showing a TStringField of kind fkLookup, which allows Null values (from a nullable Integer database column).

The dropdown list displays the items from the assigned LookupDataSet, which comes from a joined table. If the field is Null, none of the list items are displayed, the combobox is empty. If the field has a value, the correct description is shown.

I can reset it to Null by pressing the assigned NullValueKey.

That's all ok, but the users prefer using the mouse. So I could provide a clear button, but I think an additional row on top of the list would be much better. How can I do that?

Answer 1

This is not exactly what was requested, but it might be the better solution for developers having a DevExpress VCL ExpressEditors subscription: there is a hidden feature in TcxDBLookupComboBox which can provide a nice clear button inside the combobox!

It works exactly like in the TcxButtonEdit, where you can add buttons at designtime, just that this Buttons property isn't exposed in TcxDBLookupComboBox, so it can only be set at runtime :(

procedure TForm1.FormCreate(Sender: TObject);
begin
  AddClearButton(cxDBLookupComboBox1.Properties);
end;

procedure TForm1.AddClearButton(Prop: TcxCustomEditProperties);
begin
  with Prop.Buttons.Add do begin
    Kind:= bkText;
    Caption:= #$2715; //Unicode X-symbol
  end;
  Prop.OnButtonClick:= ClearButtonClick;
end;

procedure TForm1.ClearButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  if AButtonIndex = 1 then
    with TcxCustomEdit(Sender) do begin
      EditValue:= Null;
      PostEditValue;
    end;
end;

This might also work with other edit controls, however, at least with TcxDBSpinEdit it does not.

Answer 2

You can use as your LookupDataSet a query with the next SQL (Firebird syntax)

SELECT CAST (NULL AS INTEGER) AS ID, CAST ('clear' AS VARCHAR(80)) AS NAME FROM table_name
UNION
SELECT ID, NAME FROM table_name

However, in this implementation clear item will be at the end of the list.

Added after our discussion in the chat

I'm afraid we never reach to have null-value field's behavior like normal fields, because null is not a value, as was correctly mentioned here: https://petercai.com/null-is-not-a-value/ . We can only make some workarounds for it.

For example, we can see custom displayed value for null such as SELECT CASE WHEN OurField IS NULL THEN '(empty)' ELSE OurField END AS OurField. And we can set null with artificial menu item. But this is not a full, complex solution.

Answer 3

You can put the empty row in your query, and if you need it sorted you can make it appear at the top in your list like this:

select 0 as sort,
       convert(int, null) as UserID,
       'Clear' as Name
union all
select 1 as sort,
       u.UserID,
       u.Name
from   tblUser u
order by sort, Name

The sort column will make it appear at the top, after that you can sort on whatever you need.