Can I make a lookup from two datasets at once?

Question

In this question, I asked about breaking a dataset into subsets, and got a good response. Now I have the opposite problem. I've got two different datasets, representing objects of two different subclasses of a common ancestor, and I need to have the same TDBLookupComboBox search both of them at the same time, using two properties, ID # and Name, that exist in the parent class.

A lookup field allows you to look things up from multiple source fields, but only in the same dataset, not two different ones. And a lookup control only allows you to specify one lookup field to search. Does anyone know how I could get data from both datasets to appear together in the combo box?

Answer 1

The answer is in your original question you linked to. You say the members of both datasets have a common ancestor. So put all those object records into on single TClientDataset, and then for your two descendant datasets just have them use a cloned cursor from that base dataset. So you can access them as to distinct sets, or as their one combined set.

Of course the rub comes in any data that is added in the descended classes that isn't shared the base class, assuming you have any. . .

Answer 2

You could clone the records from both data sets into a new dataset that contains the union of both.

Answer 3

Create your own TDBLookupComboBox that takes multiple data sources. . . . I've created DB aware controls before (specifically drop down combo boxes). They are pretty easy to do and give you all the control you need.

Answer 4

Create a TDataSource or a TDataSet descendant that is actually a union of other TDataSets. Then on insert you either always insert into one specific TDataSet, or have rules based on the value of a certain field that determines which TDataSet to insert into. . . .