How to create Lookups through x++ in D365 FO

Lookup is one of the most commonly used  functionalities in X++ for populating the desired values from a particular master depending on the requirements given.

In this post we will be discussing about different types of Lookup.


1) Lookup without Join

            Query                                    query = new Query();
            QueryBuildDataSource        queryBuildDataSource;
            QueryBuildRange                 queryBuildRange;
            SysTableLookup                  sysTableLookup =                                                                         
            SysTableLookup::newParameters(tableNum(Table1), sender);

            sysTableLookup.addLookupField(fieldNum(Table1, Field1));
            sysTableLookup.addLookupField(fieldNum(Table1,Field2));
           
            queryBuildDataSource = query.addDataSource(tableNum(Table1));
            sysTableLookup.parmQuery(query);
            sysTableLookup.performFormLookup();


2) Lookup with Join


          Query                                      query = new Query();
          QueryBuildDataSource           qbds;
          QueryBuildDataSource           qbdsJoin;
          SysTableLookup                      sysTableLookup = sysTableLookup::newParameters(                                                                                                                       tableNum(VendTable), this);
   

         qbds= query.addDataSource( tableNum(VendTable));
         qbdsJoin= qbds.addDataSource( tableNum(DirPartyTable));
         qbdsJoin.relations( false);
         qbdsJoin.fields().dynamic(NoYes::Yes);
         qbdsJoin.addLink( fieldNum(VendTable, Party), fieldNum(DirPartyTable, RecId));
         qbdsJoin.joinMode(JoinMode::InnerJoin);

         sysTableLookup.parmQuery(query);
         sysTableLookup.addLookupfield( fieldNum(VendTable, AccountNum), true);
         sysTableLookup.addLookupfield( fieldNum(VendTable, VendGroup), true);
         sysTableLookup.addLookupfield( fieldNum(VendTable, Party));
         sysTableLookup.performFormLookup();


3) Lookup with Multiple Joins

   
    Query                   query = new Query();
    QueryBuildDataSource    qbdsPurchTable,QbdsInvntQO, qbdsInventDim;
    QueryBuildRange         qbrVendId,qbrPurchStatus,qbrPurchType;
    QueryBuildLink          qblink1, QbLink2;

    SysTableLookup  sysTableLookup = sysTableLookup::newParameters(tableNum(PurchTable),                                                                                                                                                           this);
   
    qbdsPurchTable = query.addDataSource(tableNum(PurchTable));

    QbdsInvntQO = qbdsPurchTable.addDataSource(tableNum(InventQuarantineOrder));
    QbdsInvntQO.relations(false);
    QbdsInvntQO.joinMode(JoinMode::NoExistsJoin);
    qblink1 =  QbdsInvntQO.addLink(FieldNum(PurchTable,PurchId),FieldNum(InventQuarantineOrder,TransRefId));

    qbdsInventDim = QbdsInvntQO.addDataSource(tableNum(InventDim));
    qbdsInventDim.relations(false);
    qbdsInventDim.joinMode(JoinMode::NoExistsJoin);
    QbLink2 = qbdsInventDim.addLink(FieldNum(InventQuarantineOrder,InventDimId),FieldNum(InventDim,InventDimId));

    qbdsPurchTable.addRange(fieldNum(PurchTable, OrderAccount)).value(PurchTable_InvoiceAccountGrid.valueStr());
    qbdsPurchTable.addRange(FieldNum(PurchTable, PurchStatus)).value("Invoiced,Received");
    QbdsInvntQO.addRange(fieldNum(InventQuarantineOrder, Status)).value(queryValue(InventQuarantineStatus::Ended));
    qbdsInventDim.addRange(fieldNum(InventDim, InventLocationId)).value("102018");

    sysTableLookup.addLookupfield(fieldNum(PurchTable,OrderAccount));
    sysTableLookup.addLookupfield(fieldNum(PurchTable,PurchId),true);
    sysTableLookup.addLookupfield(fieldNum(PurchTable,PurchStatus));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();



Happy Coding !!!