How do I create a search with fields from more than one data source?
I need to create a dynamic search on a form field that should display fields from two different data sources. I am trying to execute it like:
public void lookup()
{
query = new Query();
sysTableLookup = SysTableLookup::newParameters(tableNum(smmBusRelTable), this);
qbds = query.addDataSource(tablenum(smmBusRelTable));
// qbds.addDataSource(tableNum(DirPartyTable));
//qbds.relations(true);
sysTableLookup.parmQuery(query);
sysTableLookup.addLookupField(fieldNum(smmBusRelTable, Busrelaccount));
//sysTableLookup.addLookupfield(fieldNum(DirPartyTable, Name));
sysTableLookup.performFormLookup();
}
The commented lines are the operation I am trying to do to add another data source.
source to share
As far as I know, the SysTableLookup class does not support showing fields from other tables. The addLookup () method does not accept a TableId value and assumes that all fields are in the first data source of the request.
You can write your own version of SysTableLookup that supports field references from different tables. A simpler and more practical (and less expensive) approach might be to create a display method in the SmmBusRelTable to extract the name from the DirPartyTable (if it doesn't already exist) and use it as a field in the search. The display methods in the main table are supported if I remember correctly.
Depending on what exactly you are trying to accomplish, there may be an even easier way. You can add a display method to the AutoLookup table field group from SmmBusRelTable and avoid overriding the lookup () method.
source to share
It is actually very easy to combine multiple data sources into sysTableLookup. Here is the trick I used to be able to filter the name from EcoResProductTranslation
when searching for items.
1) Create a view that combines all of your data sources and adds the fields you would like to see in your view call.
2) Create a query from the view created in step 1.3
) Use them to perform your search like this:
static client void lookupItemActive(FormStringControl _ctrl)
{
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(<ViewName>),_ctrl);
Query query = new Query(queryStr(<QueryName>));
sysTableLookup.addLookupfield(fieldnum(<ViewName>, ItemId));
sysTableLookup.addLookupfield(fieldNum(<ViewName>, Name));
sysTableLookup.addLookupfield(fieldNum(<ViewName>, ItemGroupId));
sysTableLookup.addLookupfield(fieldnum(<ViewName>, Status));
sysTableLookup.addLookupfield(fieldnum(<ViewName>, RevId));
sysTableLookup.addLookupfield(fieldnum(<ViewName>, ItemType));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
}
source to share