SysTableLookup class

The SysTableLookup class is provided by the standard application to allow programmers to easily create their own lookup forms, in code.

The basic steps to using this class are as follows:

1. Create the sysTableLookup object
2. Create the query which will be used to select the lookup data
3. Specify the fields which will be shown on the lookup
4. Perform the lookup

In this example, we will perform a complex lookup on the customer table. We only want to include customers which are not blocked, and where one or more open transactions exists.

public void lookup()
{
  Query                   query = new Query();
  QueryBuildDataSource    dsCustTable;
  QueryBuildDataSource    dsCustTrans;
 
  // Instantiate sysTableLookup object using table which will provide the visible fields 
  SysTableLookup  sysTableLookup = sysTableLookup::newParameters(tableNum(CustTable), this);
  ;
 
  // Create the query.  Only select customers where blocked != All and one more more transactions
  // exist with no closed date
  dsCustTable = query.addDataSource(tableNum(CustTable));
  dsCustTable.addRange(fieldNum(CustTable, Blocked)).value(queryNotValue(CustVendorBlocked::All));
 
  dsCustTrans = dsCustTable.addDataSource(tableNum(CustTrans));
  dsCustTrans.relations(true);
  dsCustTrans.joinMode(JoinMode::ExistsJoin);
  dsCustTrans.addRange(fieldNum(CustTrans, Closed)).value(queryValue(dateNull()));
 
  // Set the query to be used by the lookup form
  sysTableLookup.parmQuery(query);
 
  // Specify the fields to show in the form.  In this case we have chosen
  // Account number, name, and dimension one.
  sysTableLookup.addLookupfield(fieldNum(CustTable, AccountNum));
  sysTableLookup.addLookupfield(fieldId2Ext(fieldNum(CustTable, Dimension), 1));
 
  // Perform the lookup
  sysTableLookup.performFormLookup();
}

While the above example will work perfectly, you should consider moving this code out of the lookup method on the form control itself. For ease of re-use and to maximise ease of upgrading of the forms, it is better to construct the lookup as a static method on the CustTable which can then be called from the form. Don't forget to remove the super() call from the method. Otherwise your Lookup won't work.

For example:

public static void lookupCustOpenTrans(FormControl _callingControl)
{
  Query                   query = new Query();
  QueryBuildDataSource    dsCustTable;
  QueryBuildDataSource    dsCustTrans;
 
  // Instantiate sysTableLookup object using table which will provide the visible fields 
  SysTableLookup  sysTableLookup = sysTableLookup::newParameters(tableNum(CustTable), _callingControl);
  ;
 
  ... remainder of code from above ...
}

In the form control lookup() method:

public void lookup()
{
  ;
  CustTable::lookupCustOpenTrans(this);
}

See also

SysMultiTableLookup - an extended version of SysTableLookup class, allowing lookup from multiple tables