Skip to main content

Lookup (or) Drop-down for field in Dynamic AX using query


void lookupField(FormControl  control)
{
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource;
    QueryBuildRange         queryBuildRange;
    SysTableLookup          sysTableLookup;
    ;

    //Create an instance of SysTableLookup with the form control passed in
    sysTableLookup = SysTableLookup::newParameters(tablenum(<table name>), control);

    //Add the fields to be shown in the lookup form
    sysTableLookup.addLookupfield(fieldnum(<table name>, <field name>), true);
    sysTableLookup.addLookupfield(fieldnum(<table name>, <field name>), false);

    //create the query datasource
    queryBuildDataSource = query.addDataSource(tablenum(<table name>));
    queryBuildRange = queryBuildDataSource.addRange(fieldnum(<table name>, <field name>));
    queryBuildRange.value(enum2str(NoYes::Yes)); //Example of Enum value or you can specify any range

    //add the query to the lookup form
    sysTableLookup.parmQuery(query);

    // Perform lookup
    sysTableLookup.performFormLookup();
}

Lookup using temporary table:

Writing the lookup under form > datasource > field  > method:
public void lookup(FormControl _formControl, str _filterStr)
{
    Query                       query          = new Query();
    QueryBuildDataSource        qds1;
    QueryBuildDataSource        qds2;
    QueryBuildRange             qr1;
    QueryBuildRange             qr2;
    QueryBuildRange             qr3;
    SysTableLookup              sysTableLookup;
    VendInvoiceJour             VendInvoiceJour;
    WHT_WithholdingTaxTrans     WHT_WithholdingTaxTrans;
    WHT_KRALookuptable          WHT_KRALookuptable; // Temporary table
    QueryRun                    qr;
    ;

    delete_from WHT_KRALookuptable;
    qds1 = query.addDataSource(tableNum(VendInvoiceJour));
    qds2 = qds1.addDataSource(tableNum(WHT_WithholdingTaxTrans));
    qds2.joinMode(JoinMode::InnerJoin);
    qds2.addLink(fieldNum(WHT_WithholdingTaxTrans,Invoice), fieldNum(VendInvoiceJour,InvoiceId));
    qds2.addLink(fieldNum(WHT_WithholdingTaxTrans,InvoiceAccount), fieldNum(VendInvoiceJour,InvoiceAccount));
    qr1 = qds1.addRange(fieldNum(VendInvoiceJour, InvoiceAccount));
    qr1.value(WHT_KRAreceiptUpdate.AccountNum);
    qr2 = qds2.addRange(fieldNum(WHT_WithholdingTaxTrans, WHT_KRAStatus));
    qr2.value(int2str(WHT_KRAStatus::Paid));
    qr3 = qds2.addRange(fieldNum(WHT_WithholdingTaxTrans,WHT_DTA));
    qr3.value(int2str(NoYes::No));
    qr = new QueryRun(query);
    while (qr.next())
    {
        VendInvoiceJour = qr.get(tablenum(VendInvoiceJour));
        select WHT_KRALookuptable
            where WHT_KRALookuptable.InvoiceId == VendInvoiceJour.InvoiceId
            && WHT_KRALookuptable.InvoiceAmount == VendInvoiceJour.InvoiceAmount;
        if (!WHT_KRALookuptable)
        {
            select sum(WithholdingTaxAmount) from  WHT_WithholdingTaxTrans
                group by InvoiceAccount,Invoice
                where WHT_WithholdingTaxTrans.InvoiceAccount == VendInvoiceJour.InvoiceAccount
                && WHT_WithholdingTaxTrans.Invoice == VendInvoiceJour.InvoiceId;
            if (WHT_WithholdingTaxTrans)
            {
                WHT_KRALookuptable.WithholdingTaxAmount = WHT_WithholdingTaxTrans.WithholdingTaxAmount;
            }
            WHT_KRALookuptable.InvoiceId = VendInvoiceJour.InvoiceId;
            WHT_KRALookuptable.InvoiceAmount = VendInvoiceJour.InvoiceAmount;
            WHT_KRALookuptable.AccountNum = VendInvoiceJour.InvoiceAccount;
            WHT_KRALookuptable.insert();
        }
    }
    sysTableLookup = SysTableLookup::newParameters(tableNum(WHT_KRALookuptable),_formControl);
    sysTableLookup.addLookupField(fieldNum(WHT_KRALookuptable, InvoiceId));
    sysTableLookup.addLookupField(fieldNum(WHT_KRALookuptable, InvoiceAmount));
    sysTableLookup.addLookupField(fieldNum(WHT_KRALookuptable, WithholdingTaxAmount));

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

Comments

  1. This is excellent drop down in dynamic AX.

    Join our three-part technical briefing series, ‘Simplifying the Big Migration: Dynamics AX to Dynamics 365

    http://info.macrosoftinc.com/webinar-dynamics-ax-dynamics-365

    ReplyDelete

Post a Comment