Dynamics Community Forum Thread Details

Add lookup method to form datasource with multiple tables and use GroupBy

Hi,

I have a form that has Table 1 as a data source, Table 1 contains these fields (custAccount,LId,LSId) (the three of them together should be unique)

Table1 Example

custAccount LId LSId

1 1 1

1 1 2
1 2 1

Table1 has a relation with table Table 3 based on LId and LSId

Table 3 Example

LId LSId

1 1

1 2

2 1

3 2

and Table 3 has relation with Table2 based on LId

Table2 Example

LId Name

1 first

2 second

3 third

Now in the form i want to add Lids to a specific customer. but Lid lookup is showing me Lid and LSId where Lid is repeating because there are more than one LSId for each LId

So i want to change the lookup to contain only LId and Name (where LId is not repeated in lookup) but the code below is not working

SysTableLookup          sysTableLookup  = SysTableLookup::newParameters(tableNum(Table1),_formControl);
Query                   query           = new Query();
QueryBuildDataSource    qbds            = query.addDataSource(tableNum(Table1));
QueryBuildDataSource    qbdsJoin        = qbds.addDataSource(tableNum(Table2));

qbdsJoin.joinMode(JoinMode::InnerJoin);
qbdsJoin.relations(false);
qbdsJoin.addLink(fieldNum(Table1, LId), fieldNum(Table2, LId));
qbds.orderMode(OrderMode::GroupBy);
qbds.addGroupByField(fieldNum(Table1,LId));
sysTableLookup.addLookupField(fieldNum(Table1, LId));
sysTableLookup.addLookupField(fieldNum(Table2, LName));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();

Quick Links