Hi Roman,

My SQL is a bit rusty, but if I am reading this correctly, it looks like you only want to retrieve job descriptions that match up to someone in the staff table. That makes sense, since you will never have a case where a particular staff record refers to a job description that does not exist in the table.

Hi Mike,

Sorry that it took so long to answer. I'll try to outline my idea. As a short reminder the table structure:

We have the catalogue table job_descriptions with the columns primkey, deletedate and name and  we have the 'staff' table with the columns primkey, last_name and fk_job_description.  fk_job_description is a foreign key linked to job_descriptions.primkey

Normally we have the data in DataTables which are filled by the commands
-SELECT primkey, name FROM job_descriptions WHERE deletedate IS NULL
-SELECT fk_job_description FROM staff WHERE primkey = @primkey (e.g. primkey = 1)
and the DataBinding for data and catalogue is as usual (see above).

My question is, if it would be possible with UltraCombo or UltraComboEditor to establish a DataBinding which works when the second SQL statement is changed to

SELECT
job_description.name,
job_description. primkey,
staff. fk_job_description
FROM staff
LEFT OUTER JOIN job_description ON job_description.primkey = staff. fk_job_description

In that manner, it would be possible to display always the current (human readable) value, no matter whether the catalog entry was deleted and the values for selection would contain only the valid values.

Another advantage would be, that less data has to be transferred from database (especially when many items in the catalogue where deleted).

Now the DataBinding should somehow operate on job_description.name.

I'm aware, that updating the database after editing couldn't use some default mechanisms and will be more sophisticated.

I hope was able to clarify the idea.

Roman

Hi Roman,

Romande said:

Might there be another way, like joining the catalogue table by SQL and then set the DataBinding to something else but Value <-> fk_job_description?

I'm not sure what you mean by this. In order to do the translation from Value to DisplayText, the item must exist on the list. If you know in advance that the obsolete item is not need, you could, of course, exclude it from your list, but I suspect you do not know until after it's loaded, and it would be less efficient to modify the list every time you load a record than the simply load all of the list data once, I think.

Hi Mike,

Mike Saltzman"]

What I would do is use UltraCombo, instead of UltraComboEditor and then you can either hide row 3 or disable it. That way, it's still on the list and the control can translate the DataValue into the DisplayText, but the user cannot choose that item in the future.

Your solution works perfectly for me. The effect can be tested, when a valid, but filtered out ValueMember value is entered. Then the UltraCombo translates that value into a human readable Text but that item doesn't appear in the DropDown, neither when dropped down, nor when auto-completing.

The only disadvantage might be, that perhaps much data is loaded into the catalogue table which would never be needed. Might there be another way, like joining the catalogue table by SQL and then set the DataBinding to something else but Value <-> fk_job_description? As I mentioned above: your solution works for me, this question is somewhat more academic...

Thanks,

   Roman