relate two tables based on an 'active' column

So I have a question and I will give a related example. Say I have a list of records in a table:

Product Cost Active

hat 1.00 false

coat 2.00 true

I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer

Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.

Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.

So... you want a union of active items and inactive previously-bought items?

select *
from Products
where Active = 'true' --Should you be using a bit/tinyint/smallint here?
UNION ALL
select distinct p.*
from
Purchases pu
join

Products p
on p.ProductID = pu.ProductID
where p.Active = 'false'
and pu.Buyer = @.Buyer

Maybe?

Rob|||Hi,

well this is more a front-end related question. You can capture the SelectedIndexchangedEvent of the Combobox and select upon the value or the column where the row is marked as new / old if it can be choosen or not. Depending on your control you could also use another color for each combobox value (and therefore hrey out the non selectable). If you want to filter the combobox values in the first place without showing them to the user, you might want to hook into the ItemDatabound event. That highly depends on your used language / controls (which you did not mentioned)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I got it hammered out. I set the combobox data source on a filtered binding source when the OnBeginEdit event fired. The filtered string simply included and "or combobox.Value = sourceTable.ID". Then on the OnEndEdit event, I set the combobox datasource back to an unfiltered view of the items.