Filtering Company-Specific Product Templates - SysRecordTmpTemplate lookup

Hi Techies -

Recently I have come across a requirement where I needed to display product templates specific to a selected company for a given record.

So, as shown in below screenshot there are two records with companies - CHC and NES. Lookup is showing product templets only from NES.

Product templates use two main tables SysRecordTemplateTable and SysRecordTmpTemplate. First table stores the record templates data in such format which is not readable and directly usable to show the user.

There is a standard code how to fetch the values form SysRecordTemplateTable and insert them into SysRecordTmpTemplate to use them to show on UI.

Retrieve Legal Entity Information:

First, I obtained the legal entity associated with the current product release:

MultipleProdRelease prodReleaseLocal = MultipleProdRelease_ds.cursor();

int prodReleasePosition = 1;

container legalEntities;

if (prodReleaseLocal)

{

    legalEntities = conIns(legalEntities, prodReleasePosition, prodReleaseLocal.DataArea);

}

                      

Fetch Template Data Across Companies:

Here DataAreaId condition in where clause didn't work, so had to insert required dataAreaId into a container and use with crossCompany as below. This query will select all record templates from given legal entity and insert data into dataContainer.

 container dataContainer;

 SysRecordTemplateTable templateTable;

while select crossCompany :legalEntities templateTable

        index hint TableIdIdx

        where templateTable.Table == tableNum(InventTable)

{

    dataContainer = dataContainer + templateTable.Data;

}

Populate Temporary Template Table:

Once we have a data of all record templates, we need to iterate through dataContainer and fill in the SysRecordTempTemplate table so that can be used as a lookup table.

    for (templateDataPosition = conlen(dataContainer); templateDataPosition > 1; templateDataPosition--)

    {

        [sysRecordTemplateTmp.Description, sysRecordTemplateTmp.DefaultRecord, sysRecordTemplateTmp.Data, sysRecordTemplateTmp.Details] = conpeek(dataContainer, templateDataPosition);

        sysRecordTemplateTmp.OrgDescription = sysRecordTemplateTmp.Description;

        sysRecordTemplateTmp.insert();               

    }            

Configure the Lookup:

Next, create a new lookup query and set a filled temp table with parmTmpBuffer () method.

    Query query = new Query();

    QueryBuildDataSource queryBuildDataSource;

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SysRecordTmpTemplate), this);

       

    sysTableLookup.addLookupField(fieldNum(SysRecordTmpTemplate, Description), true);

    queryBuildDataSource =         query.addDataSource(tableNum(SysRecordTmpTemplate));

    query.allowCrossCompany(true);

    MultipleProdRelease prodReleaseLE = MultipleProdRelease_ds.cursor();

    query.addCompanyRange(prodReleaseLE.DataArea);

    sysTableLookup.parmQuery(query);

    sysTableLookup.parmTmpBuffer(sysRecordTemplateTmp);

    sysTableLookup.performFormLookup();   

There are many examples available for filtering a lookup, but the product template lookup has unique challenges as it uses temp table to fill in the data and company filter works with crossCompany query.