In this walk-through , I will give an overview of multi select lookups with filtration of lookup values based on another multi select lookup. There was requirement of five parameters for a report including: from date, To Date, Project groups, Project names and customer of selected projects as shown below :
In following sections I will show you the implementation of the multi select lookups step by step:
1. Creating UI Builder class
Create a class and extend it with SysOperationAutomaticUIBuilder
class SL_InvoiceDetailsUIBuilder extends SysOperationAutomaticUIBuilder
{
//Dialog fields declaration
DialogField dfprojGroup, dfprojName, dfcustName, dfFromDate, dfToDate;
container ProjGroupsContainer, ProjIdContainer;
//Multiselect grid declaration
SysLookupMultiSelectGrid msCtrlProjGroups, msCtrlProjNames, msCtrlCustNames;
SL_InvoiceDetailsContract contract;
}
Override the build method to add dialog fields as follows:
public void build()
{
contract = this.dataContractObject();
dfFromDate = this.addDialogField(methodStr(SL_InvoiceDetailsContract, parmFromDate), contract);
dfToDate = this.addDialogField(methodStr(SL_InvoiceDetailsContract, parmToDate), contract);
dfprojGroup = this.addDialogField(methodStr(SL_InvoiceDetailsContract, parmProjGroups), contract);
dfprojName = this.addDialogField(methodStr(SL_InvoiceDetailsContract, parmProjNames), contract);
dfcustName = this.addDialogField(methodStr(SL_InvoiceDetailsContract, parmCustNames), contract);
}
Override the PostBuild method to register the multi select lookups with lookup events as follows:
public void postBuild()
{
super();
//fromDate
dfFromDate = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SL_InvoiceDetailsContract, parmfromdate));
// to date
dfToDate = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SL_InvoiceDetailsContract, parmtodate));
//Project Group
dfprojGroup = this.bindInfo().getDialogField(contract, methodStr(SL_InvoiceDetailsContract, parmProjGroups));
dfprojGroup.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SL_InvoiceDetailsUIBuilder, ProjGroupLookup), this);
if (dfprojGroup){
dfprojGroup.lookupButton(2);
}
//Project Names
dfprojName = this.bindInfo().getDialogField(contract, methodStr(SL_InvoiceDetailsContract, parmProjNames));
dfprojName.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SL_InvoiceDetailsUIBuilder, ProjNamesLookup), this);
if (dfprojName){
dfprojName.lookupButton(2);
}
//Customers Name
dfcustName = this.bindInfo().getDialogField(contract, methodStr(SL_InvoiceDetailsContract, parmCustNames));
dfcustName.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SL_InvoiceDetailsUIBuilder, CustNamesLookup), this);
if (dfcustName){
dfcustName.lookupButton(2);
}
}
There are three multi select lookups Project Groups, Project Names and Customer names, the implementation of Project Group lookup is as follows:
private void ProjGroupLookup(FormStringControl _control)
{
msCtrlProjGroups = SysLookupMultiSelectGrid::construct(_control, _control);
msCtrlProjGroups.parmQuery(this.ProjGroupQuery());
msCtrlProjGroups.run();
}
The following method returns the query for ProjGroupLookup to fill in the data.
private Query ProjGroupQuery()
{
Query query;
query = new query();
query.addDataSource(tableNum(ProjGroup));
query.dataSourceTable(tableNum(ProjGroup)).addSelectionField(fieldNum(ProjGroup, ProjGroupId));
query.dataSourceTable(tableNum(ProjGroup)).addSelectionField(fieldNum(ProjGroup, Name));
return query;
}
Implementation of Project names lookup:
private void ProjNamesLookup(FormStringControl _control)
{
msCtrlProjNames = SysLookupMultiSelectGrid::construct(_control, _control);
msCtrlProjNames.parmQuery(this.ProjNameQuery());
msCtrlProjNames.run();
}
The following method returns the query for ProjNamesLookup to fill in the data; the project group range is also applied to the query that is selected on the parameter screen.
private Query ProjNameQuery()
{
Query query;
int counter;
query = new query();
query.addDataSource(tableNum(ProjTable));
if(msCtrlProjGroups){
//container is filled with selected project groups on the parameter screen
ProjGroupsContainer = msCtrlProjGroups.getSelected();
}
//Loop over the container values and apply range to query
for(counter = 1; counter <= conLen(ProjGroupsContainer); counter++)
{
query.dataSourceTable(tableNum(ProjTable)).addRange(fieldNum(ProjTable, ProjGroupId)).value(queryValue(ProjGroup::sl_findRecId(conPeek(ProjGroupsContainer , counter)).ProjGroupId));
}
query.dataSourceTable(tableNum(ProjTable)).addSelectionField(fieldNum(ProjTable, ProjId));
query.dataSourceTable(tableNum(ProjTable)).addSelectionField(fieldNum(ProjTable, Name));
return query;
}
Implementation of Customer names lookup:
private void CustNamesLookup(FormStringControl _control)
{
msCtrlCustNames = SysLookupMultiSelectGrid::construct(_control, _control);
msCtrlCustNames.parmQuery(this.CustNameQuery());
msCtrlCustNames.run();
}
The following method returns the query for CustNamesLookup to fill in the data; only those customers are filled whose projects are selected in project names parameter.
private Query CustNameQuery()
{
Query query;
int counter;
query = new query();
query.addDataSource(tableNum(CustTable));
query.dataSourceTable(tablenum(CustTable)).addDataSource(tableNum(DirPartyTable));
if(msCtrlProjNames)
{
//container is filled with selected projects on the parameter screen
ProjIdContainer = msCtrlProjNames.getSelected();
}
//Loop over the container values and apply range to query
for(counter = 1; counter <= conLen(ProjIdContainer); counter++)
{
query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, AccountNum)).
value(queryValue(ProjTable::findRecId(conPeek(ProjIdContainer, counter)).CustAccount));
}
query.dataSourceTable(tableNum(CustTable)).addSelectionField(fieldNum(CustTable, AccountNum));
//Below relation is applied to get customer name along with customer account
query.dataSourceTable(tablenum(DirPartyTable)).joinMode(JoinMode::InnerJoin);
query.dataSourceTable(tablenum(DirPartyTable)).relations(true);
query.dataSourceTable(tablenum(DirPartyTable)).addSelectionField(fieldNum(DirPartyTable, Name));
return query;
}
This completes my implementation of multiselect lookups for SSRS report, remember to comment the super in postRun() method after overriding it, to avoid the exception of RegisterOverrideMethod was called twice for the same object for method ‘lookup’
2. Contract class
The implementation of contract class for sl_InvoiceDetailsUIBuilder is given below:
Class Declaration:
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classstr(SL_InvoiceDetailsUIBuilder)),
SysOperationGroupAttribute('Date range', literalStr("@SYS329083"), '1',FormArrangeMethod::HorizontalFlushRight),
SysOperationGroupAttribute('Criteria', literalStr("Criteria"),'2')
]
class SL_InvoiceDetailsContract
{
FromDate fromDate;
ToDate toDate;
List projGroup, projName, Customers;
}
From Date data contract methods:
[
DataMemberAttribute('FromDate'),
SysOperationLabelAttribute(literalStr("@SYS329084")),
SysOperationGroupMemberAttribute('Date range'),
SysOperationDisplayOrderAttribute('1')
]
public FromDate parmFromDate(FromDate _fromDate = fromDate)
{
fromDate = _fromDate;
return fromDate;
}
To Date data contract methods:
[
DataMemberAttribute('ToDate'),
SysOperationLabelAttribute(literalStr("@SYS329086")),
SysOperationGroupMemberAttribute('Date range'),
SysOperationDisplayOrderAttribute('2')
]
public ToDate parmToDate(ToDate _toDate = toDate)
{
toDate = _toDate;
return toDate;
}
Project Group data contract methods:
[
DataMemberAttribute("ProjGroupList"),
AifCollectionTypeAttribute("ProjGroupList", Types::String),
SysOperationLabelAttribute(literalStr("Project Groups")),
SysOperationGroupMemberAttribute('Criteria'),
SysOperationDisplayOrderAttribute('1')
]
public List parmProjGroups(List _projGroups = projGroup)
{
projGroup = _projGroups;
return projGroup;
}
Project Names data contract methods:
[
DataMemberAttribute("ProjNameList"),
AifCollectionTypeAttribute("ProjNameList", Types::String),
SysOperationLabelAttribute(literalStr("Project Names")),
SysOperationGroupMemberAttribute('Criteria'),
SysOperationDisplayOrderAttribute('2')
]
public List parmProjNames(List _projNames = projName)
{
projName = _projNames;
return projName;
}
Customer Names data contract methods:
[
DataMemberAttribute("CustNameList"),
AifCollectionTypeAttribute("CustNameList", Types::String),
SysOperationLabelAttribute(literalStr("Customers")),
SysOperationGroupMemberAttribute('Criteria'),
SysOperationDisplayOrderAttribute('3')
]
public List parmCustNames(List _custNames = Customers)
{
Customers = _custNames;
return Customers;
}
This completes my data contract class implementation, Below are the few snapshots for demonstration.
3. Demonstration
Figure 1 : Multi select Project group parameter
Figure 2: Multi select Project names parameter filtered according to project groups selected
Figure 3: Multi select customer parameter filtered according to project names
Note : This article assumes that you have prior basic knowledge of Data contract class, User Interface (UI) Builder Class and Report Data Provider Class.
