Multi Select Lookup in Forms with Filters in D365 / Custom multi select lookup in form D365FO

Standard User dimension 1 dropdown. Multi-selection will be allowed in these dropdowns. Multiple selected values will be stored in the field, separated by semi-colon (;).

LookUp code 

     

Event handler class to get the  multi select lookup   

class TRGReqTransPOEventHandler

{    

    [FormControlEventHandler(formControlStr(ReqTransPo,TRGSite), FormControlEventType::Lookup),

        SuppressBPWarning('BPParameterNotUsed','Parameter required by the event method')]

    public static void TRGSite_OnLookup(FormControl sender, FormControlEventArgs e)

    {

        Query                   query = new Query();

        QueryBuildDataSource    queryBuildDataSource;  

        container            selectFieldPurchRef = [tableNum(InventSite), fieldNum(InventSite, SiteId)];

        FormRun             fr = sender.formRun();

        queryBuildDataSource = query.addDataSource(tableNum(InventSite));

        queryBuildDataSource.addSelectionField(fieldNum(InventSite, SiteId));

        queryBuildDataSource.orderMode(ordermode::GroupBy);

        queryBuildDataSource.addSortField(FieldNum(InventSite,SiteId));

        SysLookupMultiSelectCtrl::constructWithQuery(fr,sender,query,false,selectFieldPurchRef);

    }

    [FormControlEventHandler(formControlStr(ReqTransPo, TRGSalesId), FormControlEventType::Lookup),

    SuppressBPWarning('BPParameterNotUsed','Parameter required by the event method')]

    public static void TRGSalesId_OnLookup(FormControl sender, FormControlEventArgs e)

    {

        Query                       query = new Query();

        QueryBuildDataSource        queryBuildDataSource;

        container                   selectFieldPurchRef = [tableNum(TRGReqTransView), fieldNum(TRGReqTransView, InventDimension3)];

        FormRun                     fr = sender.formRun();

        queryBuildDataSource        = query.addDataSource(tableNum(TRGReqTransView));

        queryBuildDataSource.addSelectionField(fieldNum(TRGReqTransView, InventDimension3));

        queryBuildDataSource.orderMode(ordermode::GroupBy);

        queryBuildDataSource.addSortField(FieldNum(TRGReqTransView, InventDimension3));

        queryBuildDataSource.addRange(fieldNum(HDMReqTransView, InventDimension3)).value(SysQuery::valueNotEmptyString());

        SysLookupMultiSelectCtrl::constructWithQuery(fr,sender,query,false,selectFieldPurchRef);

    }

    [FormControlEventHandler(formControlStr(ReqTransPo, TRGSite), FormControlEventType::Modified),

        SuppressBPWarning('BPParameterNotUsed','Parameter required by the event method')]

    public static void TRGSite_OnModified(FormControl sender, FormControlEventArgs e)

    {

        FormRun fr = sender.formRun();

        FormDataSource formDataSourceId  = fr.dataSource(formDataSourceStr(ReqTransPo, InventDim));

        FormDataSource formDataSource  = fr.dataSource(formDataSourceStr(ReqTransPo, ReqPO));

        formDataSourceId.executeQuery();

        formDataSource.executeQuery();

    }

}

-----------------------------------------------------------------------------------------------------------------------------

On Modified :

    

-----------------------------------------------------------------------------------------------------------------------------

[ExtensionOf(formdatasourcestr(ReqTransPO,InventDim))]

internal final class TRGReqTransPOInventDimDS_Extension

{    

    public void executeQuery()

    {           

        #characters

        FreeText1000 siteId = this.formRun().design().controlName(formControlStr(ReqTransPo,TRGSite)).valueStr();

        FreeText1000 salesId = this.formRun().design().controlName(formControlStr(ReqTransPo,TRGSalesId)).valueStr();

        QueryBuildRange siteQBR,salesIdQBR;

        siteQBR = sysquery::findorcreaterange(this.queryBuildDataSource(),fieldnum(InventDim,InventSiteId));

        salesIdQBR = sysquery::findorcreaterange(this.queryBuildDataSource(),fieldnum(InventDim,InventDimension3));

        if (siteId)

        {

            siteQBR.value(strReplace(siteId,#semicolon,#comma));

        }

        else

        {

            siteQBR.value(SysQuery::valueUnlimited());

        }

        if (salesId)

        {

            salesIdQBR.value(strReplace(salesId,#semicolon,#comma));

        }

        else

        {

            salesIdQBR.value(SysQuery::valueUnlimited());

        }

        next executeQuery();

    }

}

How to restore bacpack file in SQL

Work Around: 

You need to Download and install the latest Data Tier Application framework (18.2) (Link:https://www.microsoft.com/en-us/download/confirmation.aspx?id=58207) and run the SQLPACKAGE.exe located in C:\Program Files\Microsoft SQL Server\150\DAC\bin

Open CMD run as admin:

The command would look like below.

CD "C:\Program Files\Microsoft SQL Server\150\DAC\bin"

SqlPackage.exe /a:import /sf:"C:\temp\uatbackup.bacpac" /tsn:localhost /tdn:AxDBFromUAT /p:CommandTimeout=1200

Open Sql server change Database names using below code :

USE master;
GO
ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AxDB MODIFY NAME = AxDB_Old;
GO
ALTER DATABASE AxDB_Old SET MULTI_USER
GO

Do Database Sync ..

Thanks .....

Form string control modified method COC in D365FO / Extend Form Control methods / Chain of command form string control

Control chain of command to get the selected record in D365FO

[ExtensionOf(formControlStr(ReqTransPO,TRGSite))]

internal final class TRGReqTransPO_TRGSiteCtrl_Extension

{

    public boolean modified()

    {

        boolean ret;

    

        FormStringControl formStringControl = any2Object(this) as FormStringControl;

        FormDataSource formDatasource = formStringControl.formRun().dataSource(formDataSourceStr(ReqTransPO,InventDim));        

        ret = next modified();

        formDatasource.executeQuery();,

    

        return ret;

    }

}

Query with group by and joins in lookup D365FO

 [FormControlEventHandler(formControlStr(TRGSysMailerMessageEditor, TRGTo), FormControlEventType::Lookup)]

    public static void TRGTo_OnLookup(FormControl sender, FormControlEventArgs e)

    {

        TRGSysMailerExchange   sysMailerExchange   = sender.formRun().args().caller() as TRGSysMailerExchange;

        CustAccount         custAccount         = sysMailerExchange.parmAccountNum();        Query query = new Query();

        QueryBuildDataSource qbds,qbds1,qbds2,qbds3,qbds4,qbds5;

        qbds = query.addDataSource(tableNum(LogisticsLocationRole));

        qbds.addSelectionField(FieldNum(LogisticsLocationRole,Name));

        qbds.orderMode(ordermode::GroupBy);

        qbds.addSortField(FieldNum(LogisticsLocationRole,Name));

        qbds1 = qbds.addDataSource(tableNum(LOGISTICSELECTRONICADDRESSROLE));

        qbds1.joinMode(JoinMode::InnerJoin);

        qbds1.addLink(fieldNum(LOGISTICSLOCATIONROLE,RecId),fieldNum(LOGISTICSELECTRONICADDRESSROLE,LOCATIONROLE));

        qbds2 = qbds1.addDataSource(tableNum(CustTable));

        //qbds2.addRange(fieldNum(CustTable, AccountNum)).value(queryValue(custAccount));

        qbds3 = qbds2.addDataSource(tableNum(DIRPARTYCONTACTINFOVIEW));

        //qbds3.addRange(fieldNum(DIRPARTYCONTACTINFOVIEW, Type)).value(queryValue(LogisticsElectronicAddressMethodType::Email));

        qbds3.joinMode(JoinMode::InnerJoin);

        qbds3.addLink(fieldNum(CustTable,Party),fieldNum(DIRPARTYCONTACTINFOVIEW,party));

        qbds4 = qbds3.addDataSource(tableNum(LOGISTICSELECTRONICADDRESS));

        qbds4.joinMode(JoinMode::InnerJoin);

        qbds4.addLink(fieldNum(DIRPARTYCONTACTINFOVIEW,ELECTRONICADDRESS),fieldNum(LOGISTICSELECTRONICADDRESS,RecId));

        SysTableLookup      sysTableLookup  = SysTableLookup::newParameters(tableNum(LogisticsLocationRole), sender);

        sysTableLookup.addLookupfield(fieldNum(LogisticsLocationRole, Name));        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();        //cancel the call to super() to prevent the system from trying to show        //the lookup form twice and cause an error.

        FormControlCancelableSuperEventArgs cancelableSuperEventArgs = e as FormControlCancelableSuperEventArgs;

        cancelableSuperEventArgs.CancelSuperCall();

    }

Form multi select lookup using control in D365FO

 /// <summary>

/// 

/// </summary>

class TRGReqTransPOEventHandler

{

    

    /// <summary>

    ///

    /// </summary>

    /// <param name="sender"></param>

    /// <param name="e"></param>

    [FormControlEventHandler(formControlStr(ReqTransPo, TRGSalesId), FormControlEventType::Lookup)]

    public static void TRGSalesId_OnLookup(FormControl sender, FormControlEventArgs e)

    {

        Query                query = new Query();

        QueryBuildDataSource queryBuildDataSource;

        QueryBuildRange      queryBuildRangeOrderAcc, queryBuildRangeStatus;

        FormDataSource       formDataSource;

        SalesTable           salesTable;

        container            selectFieldPurchRef = [tableNum(SalesTable), fieldNum(SalesTable, SalesId)];

        queryBuildDataSource = query.addDataSource(tableNum(SalesTable));

        queryBuildDataSource.addSelectionField(fieldNum(SalesTable, SalesId));

        queryBuildDataSource.fields().dynamic(NoYes::No);

        queryBuildDataSource.fields().clearFieldList();

        queryBuildDataSource.fields().addField(fieldNum(SalesTable, SalesId));

        SysLookupMultiSelectCtrl::constructWithQuery(fr,sender,query,false,selectFieldPurchRef);

    }

}