Form control lookup event handler with additonal field and query range X++

Hi folks,

I am writing this blog to share a real scenario that I came across recently related to Project ID lookup field on the Free Text Invoice form. This blog will be useful for D365 Fin Ops Tech guys to learn overriding Microsoft standard control lookups with the additional lookup field and range.

Background:

As you guys might be aware of that Projects are connected with Project Contracts that are connected with Customers. Project Contract has one to one relation with Customer and each Project Contract can be linked to multiple projects but a Project can have only one Project Contract so there is a relation of Many to One between Projects and Project Contracts.

Requirement:

I got the requirement from the client where in they only wanted to see the list of those projects to be filtered on the basis of Customer account against which Free Text Invoice is raised. 

For example: There are two projects "PROJ00001" and "PROJ00002" attached to Project Contract "PROJCON00001" that is connected with Customer "CUST00001". If Free Text Invoice will be raised against "CUST00001", since "CUST00001" is connected with "PROJCON00001" that is associated with "PROJ00001" and "PROJ00002" therefore only "PROJ00001" and "PROJ00002" will be displayed on the Project ID lookup field, rest of the projects will not be shown on the lookup to avoid user in selecting wrong projects that are not related to the customer while raising the Free Text Invoice.

Additional Customer account field was also required on the lookup for the reference purpose as Standard Microsoft Project ID lookup field only shows Project ID and Project Name fields.

Solution:

I created event handler class because MS standard fields lookup can't be overridden without using event handlers since overlayering concept has been obsolete from Dynamics 365 Fin Ops developments.

Below is the code snippet to fulfill requirement:

class CustInvoiceTable_EventHandler

{

  [FormControlEventHandler(formControlStr(CustFreeInvoice, CustInvoiceTable_freeTextProjID), FormControlEventType::Lookup)]

 public static void CustInvoiceTable_freeTextProjID_OnLookup(FormControl sender,  FormControlEventArgs e)

    {

        FormDataSource    CustInvoiceTable_ds = sender.formRun().dataSource();

        CustInvoiceTable    CustInvoiceTable = CustInvoiceTable_ds.cursor();

        SysTableLookup    sysTableLookup;

        QueryBuildDataSource    queryBuildDataSource;

        QueryBuildRange    queryBuildRange;

        Query    query;

        sysTableLookup = SysTableLookup::newParameters(tablenum(ProjTable),sender);

        sysTableLookup.addLookupfield(fieldnum(ProjTable, ProjId));

        sysTableLookup.addLookupfield(fieldnum(ProjTable, Name));

        // Added Customer Account field on the lookup as part of the requirement

        sysTableLookup.addLookupfield(fieldnum(ProjTable, CustAccount));

        query = new Query();

        queryBuildDataSource = query.addDataSource(tablenum(ProjTable));

        // Applied the range of Customer account for only showing projects that are associated with the selected customer

        queryBuildRange = queryBuildDataSource.addRange(fieldnum(ProjTable, CustAccount));

        queryBuildRange.value(CustInvoiceTable.OrderAccount);

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();

        FormControlCancelableSuperEventArgs event = e as FormControlCancelableSuperEventArgs;

        event.CancelSuperCall();   

    }

}