54) Lookup method in table level in D365 and how to call in form using multiple range with lookup validation

Example 1 :  -

Step 1  - First create the COC

/// <summary>

/// The <c>AVAEntAssetWorkOrderTableDbt_Extension</c> xxxxxxx <c>EntAssetWorkOrderTable</c> table.

/// </summary>

[ExtensionOf(tableStr(EntAssetWorkOrderTable))]

final class AVAEntAssetWorkOrderTableDbt_Extension

{

     static void avalookupWorkOrder(FormStringControl _callingControl)

    {

        Query                   query = new Query();

        QueryBuildDataSource    qbds_WorkOrderTable;

        QueryBuildDataSource    qbds_WorkOrderLifecycleState;

        QueryBuildRange         qbr_Name;

        SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(EntAssetWorkOrderTable), _callingControl);

        // initialize query 

        qbds_WorkOrderTable = query.addDataSource(tableNum(EntAssetWorkOrderTable));

        qbds_WorkOrderLifecycleState = qbds_WorkOrderTable.addDataSource( tableNum(EntAssetWorkOrderLifecycleState));

        qbds_WorkOrderLifecycleState.relations( true);

        qbds_WorkOrderLifecycleState.fields().dynamic(NoYes::Yes);        

        qbds_WorkOrderLifecycleState.joinMode(JoinMode::InnerJoin);

        qbr_Name = qbds_WorkOrderLifecycleState.addRange(fieldNum(EntAssetWorkOrderLifecycleState,Name));

        

        // define query range value

        qbr_Name.value(strFmt('((Name == "%1") || (Name == "%2")) || (Name == "%3"))',

                        queryValue('New'),

                        queryValue('In Progress'),

                        queryValue('Template')));

        // perform lookup 

        sysTableLookup.addLookupField(fieldNum(EntAssetWorkOrderTable, WorkOrderId));

        sysTableLookup.parmQuery(query);        

        sysTableLookup.performFormLookup();

    }

}

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Step 2- How to call on the form

[ExtensionOf(formControlStr(InventTransferOrders, LineViewHeader_AvaWorkOrder_AvaWorkOrder))]

final class AVAInventTransferOrdersFrom_InventTransferTableDS_AvaWorkOrder_Extension

{

    public void lookup()

    {

        next Lookup();

        EntAssetWorkOrderTable::avalookupWorkOrder(this);

    }

}

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Step 3 - To validate the Look up : -

[ExtensionOf(formDataFieldStr(InventTransferOrders, InventTransferTable, avaWorkOrder))]
final class AvaInventTransferOrdersFrm_InventTransferTableDS_AvaWorkOrderFld_Extension
{
    public boolean validate()
    {
        FormDataObject                  fsc                     = any2Object(this) as FormDataObject;
        FormDataSource                  fdsInventTransferTable = fsc.datasource();
        InventTransferTable             transferTable          = fdsInventTransferTable.cursor();
        EntAssetWorkOrderTable          workOrderTable;
        EntAssetWorkOrderLifecycleState workOrderLifecycleState;

        boolean                         ret = next validate();;

       
        if (ret)
        {
            select firstonly  workOrderTable
                where workOrderTable.WorkOrderId == transferTable.AvaWorkOrder
            join workOrderLifecycleState
                where workOrderLifecycleState.RecId == workOrderTable.WorkOrderLifecycleState
                    && ((workOrderLifecycleState.Name == "Cancel") || (workOrderLifecycleState.Name == "Closed") || (workOrderLifecycleState.Name == "Completed"));

            if(workOrderTable)
            {
                ret = checkFailed("Enter valid work order ID");
                ret = false;
            }

        }
       

        return ret;
    }

}

====================================================================

Example 2 : -  Another example of look

 static public void avalookupAssetID(FormStringControl _formStringControl, str _workOrderId)
    {
       
        Query                   query = new Query();
        QueryBuildDataSource    qbds_WorkOrderTable;
        QueryBuildDataSource    qbds_WorkOrderLine;
        QueryBuildDataSource    qbds_AssetObjectTable;
        QueryBuildRange         qbr_Name;
        SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(EntAssetObjectTable), _formStringControl); //EntAssetObjectTable
       

        // initial
        qbds_AssetObjectTable = query.addDataSource(tableNum(EntAssetObjectTable));
        qbds_WorkOrderLine    = qbds_AssetObjectTable.addDataSource( tableNum(EntAssetWorkOrderLine));
        qbds_WorkOrderLine.relations( true);
        qbds_WorkOrderLine.fields().dynamic(NoYes::Yes);
        qbds_WorkOrderLine.joinMode(JoinMode::InnerJoin);

        qbds_WorkOrderTable = qbds_WorkOrderLine.addDataSource( tableNum(EntAssetWorkOrderTable));
        qbds_WorkOrderTable.relations( true);
        qbds_WorkOrderTable.fields().dynamic(NoYes::Yes);
        qbds_WorkOrderTable.joinMode(JoinMode::InnerJoin);

        qbr_Name = qbds_WorkOrderTable.addRange(fieldNum(EntAssetWorkOrderTable, WorkOrderId));
        // define query range value
        qbr_Name.value(_workOrderId);

        // perform lookup
        sysTableLookup.addLookupField(fieldNum(EntAssetObjectTable, ObjectID));
        sysTableLookup.parmQuery(query);
        sysTableLookup.performFormLookup();

    }

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Step 2 :- Call on form

[ExtensionOf(formControlStr(InventTransferOrders, InventTransferLine_AvaAssetNumber))]
final class AvaInventTransferOrdersFrm_AvaAssetNumberCtrl_Extension
{
    public void lookup()
    {
        FormStringControl    fsc   = any2Object(this) as FormStringControl;
        FormDataSource       fsd = fsc.formRun().dataSource(tableStr(InventTransferTable));
        InventTransferTable  transferTable = fsd.cursor();

        next lookup();
     
        EntAssetWorkOrderLine::avalookupAssetID(this, transferTable.AvaWorkOrder);
    }

}

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Step 3  :  - validate

[ExtensionOf(formDataFieldStr(InventTransferOrders, InventTransferLine, AvaAssetNumber))]
final class AvaInventTransferOrdersFrm_InventTransferLineDS_AvaAssetNumberFld_Extension
{
    public boolean validate()
    {
        FormDataObject                  fsc                   = any2Object(this) as FormDataObject;
        FormDataSource                  fdsInventTransferLine = fsc.datasource();
        InventTransferLine              transferLine          = fdsInventTransferLine.cursor();
        str                             transferTable         = InventTransferTable::find(transferLine.TransferId).AvaWorkOrder;
        EntAssetWorkOrderLine           workorderline;
        EntAssetObjectTable             assetTable;
        EntAssetWorkOrderTable          workOrderTable;

        boolean                         ret = next validate();;

       
        if (ret)
        {
            select  workOrderTable
                where workOrderTable.WorkOrderId == transferTable
            join workorderline
                where workorderline.WorkOrder == workOrderTable.RecId
            join assetTable
                where assetTable.RecId == workorderline.Object
                && assetTable.ObjectID == transferLine.AvaAssetNumber;

            if (!assetTable.RecId)
            {
                ret = checkFailed("Enter valid Asset number");
                ret = false;
            }

        }
       

        return ret;
    }

}

========================================================================

Example 3 : - Another way of look up 

    static public void avalookupMaintanceType(FormStringControl _formStringControl, str _workOrderId)

    {

        

        Query                   query = new Query();

        QueryBuildDataSource    qbds_WorkOrderTable;

        QueryBuildDataSource    qbds_WorkOrderLine;

        QueryBuildDataSource    qbds_AssetjobType;

        QueryBuildRange         qbr_Name;

        SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(EntAssetJobType), _formStringControl); //EntAssetObjectTable

        

        // initial

        qbds_AssetjobType     = query.addDataSource(tableNum(EntAssetJobType));

        qbds_WorkOrderLine    = qbds_AssetjobType.addDataSource( tableNum(EntAssetWorkOrderLine));

        qbds_WorkOrderLine.relations( true);

        qbds_WorkOrderLine.fields().dynamic(NoYes::Yes);

        qbds_WorkOrderLine.joinMode(JoinMode::InnerJoin);

        qbds_WorkOrderTable = qbds_WorkOrderLine.addDataSource( tableNum(EntAssetWorkOrderTable));

        qbds_WorkOrderTable.relations( true);

        qbds_WorkOrderTable.fields().dynamic(NoYes::Yes);

        qbds_WorkOrderTable.joinMode(JoinMode::InnerJoin);

        qbr_Name = qbds_WorkOrderTable.addRange(fieldNum(EntAssetWorkOrderTable, WorkOrderId));

        // define query range value

        qbr_Name.value(_workOrderId);

        // perform lookup

        sysTableLookup.addLookupField(fieldNum(EntAssetJobType, JobTypeID));

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();

    }

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Step 2 : - Call on the form data source fileds

[ExtensionOf(formControlStr(InventTransferOrders, InventTransferLine_AvaMaintenanceJobType))]

final class AvaInventTransferOrdersFrm_AvaMaintenanceJobTypeCtrl_Extension

{

    public void lookup()

    {

        FormStringControl    fsc   = any2Object(this) as FormStringControl;

        FormDataSource       fsd = fsc.formRun().dataSource(tableStr(InventTransferTable));

        InventTransferTable  transferTable = fsd.cursor();

        next lookup();

       

        EntAssetWorkOrderLine::avalookupMaintanceType(this, transferTable.AvaWorkOrder);

    }

}

======================================================================

Happy Daxing