Loopkup filter  based on another field  in SYSQUERY form 

The requirement is to filter one lookup field value based on another field selection in SysQuery form.

Follow the below steps to filter VendorAccount field based on legal entities selection

Example:

1. Create a new Query called DESVendorSpendReport having DataArea as Root and VendTable underneath it.

create a relation under VendTable like DataArea.id == VendTable.DataAreaid

2. Set AllowCrossCompany property of the Query to true.

3. Write the below code in SysQueryForm > Range(DataSource) > RangeValue(field) > Lookup method

public void lookup(FormControl _formControl, str _filterStr)

{

   CompanyInfo          companyInfo;

   Query                query;

   QueryBuildDataSource qbds;

   SysTableLookup       lookup;

   SysDictField         sysDictField;

   VendTable            vendTable;

   TmpSysQuery          tmpSysQuery;

   TmpSysQueryCompanyRange tmpSysQueryCompanyRange;

   Container               selectedCompanies;

   

   ;

   sysDictField = new SysDictField(Range.Table_Id, Range.Field_Id);

   if (sysDictField.label() == "Vendor account")

   {

        recordsMarked = CompanyRanges_DS.recordsMarked();

        lastIndex     = recordsMarked.lastIndex();

        for (tmpSysQueryCompanyRange = CompanyRanges_DS.getFirst(); tmpSysQueryCompanyRange ; tmpSysQueryCompanyRange = CompanyRanges_DS.getNext() )

        {

            if (tmpSysQueryCompanyRange.IsCompanySelected == NoYes::Yes)

            {

                selectedCompanies = conIns(selectedCompanies, 1, tmpSysQueryCompanyRange.CompanyId);

            }

        }

        if (selectedCompanies)

        {

            lookup = SysTableLookup::newParameters(tableNum(DESVendorSpendReport),                      _formControl);

            lookup.addLookupfield(fieldNum(DESVendorSpendReport, AccountNum));

            lookup.addLookupfield(fieldNum(DESVendorSpendReport, Id));

            query = new Query();

            qbds = query.addDataSource(tableNum(DESVendorSpendReport));

            qbds.addRange(fieldNum(DESVendorSpendReport, id)).value(con2Str(selectedCompanies));

            lookup.parmQuery(query);

            lookup.performFormLookup();

          }

      else

      {

        SysLookup::lookupRange(_formControl, range, sysQueryForm.query());

      }

   }

   else

   {

     SysLookup::lookupRange(_formControl, range, sysQueryForm.query());

   }

}