Sometimes, our client requires a lookup with many information from two or more tables. The recipe below will show how to use Multiple Tables for lookup on a form field.
public void lookup()
{
Query query = new Query();
QueryBuildDataSource qbdsItem;
QueryBuildDataSource qbdsInventTableModule;
QueryBuildDataSource qbdsInventItemLocation;
QueryBuildRange qbrModuleType;
SysMultiTableLookup sysTableLookup;
;
qbdsItem = query.addDataSource(tableNum(InventTable));
qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Purch");
qbdsInventTableModule.relations(true);
qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
qbrModuleType.value(queryValue(ModuleInventPurchSales::Purch));
qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Invent");
qbdsInventTableModule.relations(true);
qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
qbrModuleType.value(queryValue(ModuleInventPurchSales::Invent));
qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Sales");
qbdsInventTableModule.relations(true);
qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
qbrModuleType.value(queryValue(ModuleInventPurchSales::Sales));
qbdsInventItemLocation = qbdsItem.addDataSource(tableNum(InventItemLocation));
qbdsInventItemLocation.relations(true);
qbdsInventItemLocation.fetchMode(QueryFetchMode::One2One);
sysTableLookup = SysMultiTableLookup::newParameters(this, query); //Use the Query for lookup
sysTableLookup.addLookupField(fieldNum(InventTable, ItemId));
sysTableLookup.addLookupField(fieldNum(InventTable, ItemName));
sysTableLookup.addLookupMethod(tableMethodStr(InventTable, ConfigActive), 1, "Is configuration active?");
sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 2, false, "Purch Item Tax Group");
sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 3);
sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 4);
sysTableLookup.addLookupField(fieldNum(InventItemLocation, CountGroupId), 5);
sysTableLookup.performFormLookup();
}
Happy Daxing :-)
public void lookup()
{
Query query = new Query();
QueryBuildDataSource qbdsItem;
QueryBuildDataSource qbdsInventTableModule;
QueryBuildDataSource qbdsInventItemLocation;
QueryBuildRange qbrModuleType;
SysMultiTableLookup sysTableLookup;
;
qbdsItem = query.addDataSource(tableNum(InventTable));
qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Purch");
qbdsInventTableModule.relations(true);
qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
qbrModuleType.value(queryValue(ModuleInventPurchSales::Purch));
qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Invent");
qbdsInventTableModule.relations(true);
qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
qbrModuleType.value(queryValue(ModuleInventPurchSales::Invent));
qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Sales");
qbdsInventTableModule.relations(true);
qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
qbrModuleType.value(queryValue(ModuleInventPurchSales::Sales));
qbdsInventItemLocation = qbdsItem.addDataSource(tableNum(InventItemLocation));
qbdsInventItemLocation.relations(true);
qbdsInventItemLocation.fetchMode(QueryFetchMode::One2One);
sysTableLookup = SysMultiTableLookup::newParameters(this, query); //Use the Query for lookup
sysTableLookup.addLookupField(fieldNum(InventTable, ItemId));
sysTableLookup.addLookupField(fieldNum(InventTable, ItemName));
sysTableLookup.addLookupMethod(tableMethodStr(InventTable, ConfigActive), 1, "Is configuration active?");
sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 2, false, "Purch Item Tax Group");
sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 3);
sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 4);
sysTableLookup.addLookupField(fieldNum(InventItemLocation, CountGroupId), 5);
sysTableLookup.performFormLookup();
}
Happy Daxing :-)
Thanks It help me alot but for simplicity and more understanding you can also visit: https://360dynamics.blogspot.com/2017/10/multi-table-lookup-display-fields-from.html
ReplyDelete