Custom Lookup Function - arp6333/xplusplus GitHub Wiki

Creating a custom lookup on a grid column within a form

image

  1. Navigate to the desired column on the form design.

  2. Right-click Methods -> Override -> lookup

    CustomLookup

  3. Modify Lookup function:

Simple lookup - one table

In this example, we want to show the vendor account numbers in a lookup.

/// <summary>
/// In the newly auto-generated lookup method.
/// </summary>
/// <param name = "_formControl">Form control.</param>
/// <param name = "_filterStr">Filter string.</param>
public void lookup(FormControl _formControl, str _filterStr)
{
    SysTableLookup       sysTableLookup = SysTableLookup::newParameters(tableNum(VendTable), _formControl); // Put desired lookup table here
    Query                query          = new Query();
    QueryBuildDataSource qbds;
    QueryBuildRange      qbr;

    qbds = query.addDataSource(tableNum(VendTable)); // Put the same desired lookup table in the query build datasource

    // Add the field(s) we want to display in the lookup
    sysTableLookup.addLookupfield(fieldNum(VendTable, AccountNum), true);
    // We can also use table methods in lookup
    // sysTableLookup.addLookupMethod(tableMethodStr(VendTable, customerAccountName));

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

Complex lookup - multiple tables

In this example, we want to show both the vendor and customer names / account numbers in a lookup.

// At the top of the form, declare the temp table variable to hold the lookup values that has the fields I need to display
// For example, display CustVend Name and CustVend AccountNumber, which this table can hold
CustVendOutTmp tmp;

...

/// <summary>
/// In the newly auto-generated lookup method.
/// </summary>
/// <param name = "_formControl">Form control.</param>
/// <param name = "_filterStr">Filter string.</param>
public void lookup(FormControl _formControl, str _filterStr)
{
    SysTableLookup       sysTableLookup = SysTableLookup::newParameters(tableNum(CustVendOutTmp), _formControl); // Put custom tmp lookup table here
    Query                query          = new Query();
    QueryBuildDataSource qbds;
    QueryBuildRange      qbr;

    VendTable     vendTable;
    CustTable     custTable;
    DirPartyTable dirPartyTable;

    tmp.clear();    

    // Here we will insert AccountNum and Name into our temp table, first retrieving the Vendor Names/Nums and then the Customer Names/Nums

    // Get all AccountNums and Names from VendTable
    insert_recordset tmp (AccountNum, Name)
        select AccountNum from vendTable
        group by AccountNum
        join Name from dirPartyTable
        group by Name
            where dirPartyTable.recId == vendTable.Party;
    // Get all AccountNums and Names from CustTable
    insert_recordset tmp (AccountNum, Name)
        select AccountNum from custTable
        group by AccountNum
        join Name from dirPartyTable
        group by Name
            where dirPartyTable.recId == custTable.Party;

    // Insert into the tmp table
    tmp.insert();

    qbds = query.addDataSource(tableNum(CustVendOutTmp)); // Put the same desired tmp lookup table in the query build datasource

    // Add the field(s) we want to display in the lookup from the tmp table
    sysTableLookup.addLookupfield(fieldNum(CustVendOutTmp, Name), true);
    sysTableLookup.addLookupfield(fieldNum(CustVendOutTmp, AccountNum), true);

    sysTableLookup.parmQuery(query);
    sysTableLookup.parmTmpBuffer(tmp); // Tell the lookup that we are using a tmp table buffer
    sysTableLookup.performFormLookup();
}
⚠️ **GitHub.com Fallback** ⚠️