Multi Table Lookup using Views in Microsoft Dynamics 365 for Operations

Introduction

With the release of Microsoft Dynamics 365 for Finance and Operations, many features have been deprecated, and upgraded with solutions that provide abstract and far better control to the developers than its previous counterpart. Unfortunately, with the deprecation of SysMultiTableLookup class, there was no substitute provided by Microsoft. Therefore, to handle this scenario, we are left with implementing the underlying scenario using either display methods or Views. The problem with display methods is that we can not filter columns that were defined by display methods, leaving Views as our optimal choice for implementing multi table lookups in Microsoft Dynamics 365 for Finance and Operations.

Solution

In this solution, we are going to display Worker's PersonnelNumber, Worker's Name, and the current Department that he works in. As dicussed above, we are going to start by creating a view. Therefore, we are going to use the HcmWorker, HcmPositionWorkerAssignment, HcmPositionDetail, DirPartyTable, and OMOperatingUnit tables.

The view after being configured should look like this, Note that relations and fields are visualized in the image below:

The results in the table browser after synchronizing the view looks like:

Note: Multiple records per worker are being displayed by the view, and we need to display worker's current record. To handle this, we would either handle this by using Ranges on View or the Lookup method. In this example we are going to be using the later solution.

Next up, we are going to create and design a form:

Finally, we'd override the lookup method on the LookupControl FormStringControl:

[Form] 
public class WorkerDepartmentLookup extends FormRun 
{ 
    [Control("String")] 
    public void lookup() 
    { 
        Query query = new Query(); 
        QueryBuildDataSource qbds = query.addDataSource(tableNum(WorkerDepartmentView)); 

        qbds.addRange(fieldNum(WorkerDepartmentView, ValidFrom)).value(strFmt("<%1", today())); 
        qbds.addRange(fieldNum(WorkerDepartmentView, ValidTo)).value(strFmt(">%1", today())); 

        SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(WorkerDepartmentView), this); 

        sysTableLookup.parmQuery(query); 
        sysTableLookup.addLookupfield(fieldNum(WorkerDepartmentView, PersonnelNumber)); 
        sysTableLookup.addLookupfield(fieldNum(WorkerDepartmentView, WorkerName)); 
        sysTableLookup.addLookupfield(fieldNum(WorkerDepartmentView, DepartmentName)); 
        sysTableLookup.performFormLookup(); 
    } 
}

Lastly, the results of the above query during control lookup are as:

Conclusion

In this blog, we looked at how to implement Multi Table Lookup using Views in Microsoft Dynamics 365 for Finance and Operations. If you are bummed about the work put into implementing the solution, than you should be aware about the advantages provided by views. First of all, Views are virtual tables i.e. they don't store data therefore taking up literally no space. Secondly, Views provide faster lookup than run time queries generated by the Query object. Lastly, Views allows us to filter each individual column that display method deny.