Skip to main content
Dynamics 365 Community / Forums / Finance forum / lookup on all jobs and...
Suggested answer

lookup on all jobs and department in position x++

editSubscribe (0) ShareShare
ReportReport
Posted on by 311
I need to create lookup on field get all jobs , descriptions and department in positions group by jobs (I need job appear one time avoid duplicate)
I tried this but field of department is empty 
            Query                   query;                QueryBuildDataSource    QueryBuildDataSource,QueryBuildDataSource1 , QueryBuildDataSource2;                SysMultiTableLookup SysMultiTableLookup;                query = new Query();                  QueryBuildDataSource = query.addDataSource(tableNum(HcmJob));                QueryBuildDataSource.addGroupByField(fieldNum(HcmJob,JobId));                //join the translation table so we can get a description of the UOM                QueryBuildDataSource1 = QueryBuildDataSource.addDataSource(tableNum(HcmJobDetail));                QueryBuildDataSource1.joinMode(JoinMode::InnerJoin);                QueryBuildDataSource1.relations(true);                QueryBuildDataSource1.addLink(fieldNum(HcmJob,RecId),fieldNum(HcmJobDetail,Job));                QueryBuildDataSource1.addGroupByField(fieldNum(HcmJobDetail,Description));                QueryBuildDataSource2 = QueryBuildDataSource1.addDataSource(tableNum(HcmPositionDetail));                QueryBuildDataSource2.joinMode(JoinMode::InnerJoin);                QueryBuildDataSource2.relations(true);                QueryBuildDataSource2.addLink(fieldNum(HcmJobDetail,Job),fieldNum(HcmPositionDetail,Job));                QueryBuildDataSource2.addGroupByField(fieldNum(HcmPositionDetail,Department));                QueryBuildDataSource2.addRange(fieldNum(HcmPositionDetail,Department)).value(queryValue(CFMJobRequisition.Dept));                QueryBuildDataSource2.addRange(fieldNum(HcmPositionDetail,Department)).status(RangeStatus::Locked);                //define multiple table lookup query                SysMultiTableLookup  = SysMultiTableLookup::newParameters(_formControl, query);                QueryBuildDataSource.addRange(fieldNum(HcmJob,JobStatus)).value(queryValue('Active'));                QueryBuildDataSource.addRange(fieldNum(HcmJob,JobStatus)).status(RangeStatus::Locked);                SysMultiTableLookup.addLookupfield(fieldNum(HcmJobDetail,Job), true);                SysMultiTableLookup.addLookupfield(fieldNum(HcmJobDetail,Description),2);                SysMultiTableLookup.addLookupfield(fieldNum(HcmPositionDetail,Department),3,true);                SysMultiTableLookup.performFormLookup();