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.

Color Picker Control in Microsoft Dynamics 365 for Operations

Introduction

Microsoft Dynamics 365 for Finance and Operations provides many out-of-the-box controls, but even after covering a vast amount, some controls need to be implemented by our self. Among those controls is a Color picker control, which allows a user to select a color from a selection of approximate 16 million colors.

Solution

In this solution, we would be implementing a basic color picker control with the goal of looking up 16 million colors, and allowing a user to select a single one.

To start, we'd create a form with the following form control:

Note that we are using a FormIntControl to specify a color picker control. An alternative to this approach is to use FormStringControl instead.

Now we will override the lookup method on Color picker control, this would allow us to view the colors in a circular wheel when the drop down button on FormIntegerControl is clicked.

public class ColorPicker extends FormRun
{
    [Control("Integer")]
    public void lookup()
    {
        int color = this.value();
        color = ColorSelection::selectColor(this, color);
        this.value(color);
        this.backgroundColor(color);
    }
}

Finally, we would run the form to view the results as:

Conclusion

In this blog, we looked at creating a color picker control. Even though we didn't perform operations on it to control the amount of colors it can produce, or display a subset of colors to the user, but we got an overview of its working. This can be helpful in scenarios in which colors are important such as tint of a car or paint of the house at a particular portion.