Apply Data Area Id filter in lookup using SysRecordTmpTable table

(2) Share

Report

Posted on by Sangram Shinde

2,120

Hi Techies -

A new lookup is creating with SysRecordTmpTable (Product templates), in which we need to apply a data area Id filter/range, it should show values based on company value of first field below for selected record.

Whereas while populating a temp table, there might be a code required to populate a data area Id so that it would appear in data area id field of temp table, below is the code. If anyone can suggest the solution that would be helpful.

public void lookup()
{
    //super();

    //========================================================
    SysRecordTemplateTable  templateTable;
    SysRecordTmpTemplate    sysRecordTemplateTmp;
    container               dataContainer;
    int                     templateDataPosition;
    container               legalEntities;

    templateTable = SysRecordTemplateTable::find(tableNum(InventTable));            

    MultipleLEProdReleaseDirectDelivery prodReleaseLocal = MultipleLEProdReleaseDirectDelivery_ds.getFirst();
    int prodReleasePosition = 1;

    //Insert all legal entities in container
    if (prodReleaseLocal)
    {
        while (prodReleaseLocal)
        {
            legalEntities = conIns(legalEntities, prodReleasePosition, prodReleaseLocal.DataArea);
            prodReleasePosition++;
            prodReleaseLocal = MultipleLEProdReleaseDirectDelivery_ds.getNext();
        }
    }
    //Get data from template table from all legal entities
    while select crosscompany :legalEntities  templateTable
        index hint TableIdIdx
        where templateTable.Table == tableNum(InventTable)
    {
        dataContainer   =  dataContainer + templateTable.Data;
    }

    int len = conLen(dataContainer);
    
     //Retrieve and insert data in SysRecordTmpTemplate table
    for (templateDataPosition = conlen(dataContainer); templateDataPosition > 1; templateDataPosition--)
   //for (templateDataPosition = 2; templateDataPosition <= len; templateDataPosition++)
    {
        [sysRecordTemplateTmp.Description, sysRecordTemplateTmp.DefaultRecord, sysRecordTemplateTmp.Data, sysRecordTemplateTmp.Details] = conpeek(dataContainer, templateDataPosition);

        sysRecordTemplateTmp.OrgDescription = sysRecordTemplateTmp.Description;
        sysRecordTemplateTmp.insert();               
    }            
    //========================================================

    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SysRecordTmpTemplate), this);
       
    sysTableLookup.addLookupField(fieldNum(SysRecordTmpTemplate, Description), true);
    sysTableLookup.addLookupfield(fieldNum(SysRecordTmpTemplate, DataAreaId));

    queryBuildDataSource = query.addDataSource(tableNum(SysRecordTmpTemplate));
    query.allowCrossCompany(true);
    //A range to populate only record for current record.
    MultipleLEProdReleaseDirectDelivery prodReleaseLE = MultipleLEProdReleaseDirectDelivery_ds.cursor();
    query.addCompanyRange(prodReleaseLE.DataArea);
    //queryBuildDataSource.addRange(fieldNum(SysRecordTmpTemplate, DataAreaId)).value(queryValue(prodReleaseLE.DataArea));

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

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Martin Dráb 239,451 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

I don't understand the sentence it should show values based on company value of first field below for selected record. Could you rephrase it, please? A concrete example could help too.

Was this reply helpful? Yes No
Sangram Shinde 2,120 on at

Like (1)

Report
Copy link

Link copied!
Thank you Martin for reply -

In below screenshot, first field is Company which has value NESP and second is CHPV, a lookup should filter based on this active record, when user clicks lookup for first record, the lookup should display product templates only in NESP and second for CHPV.

but I am unable to figure out how can I fetch the exact company/data area Id value similar to other values I am getting like below -

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

The actual data in NESP, but somehow, the buffer I am getting to fill the temp table returning the dat company, there is a miss somewhere, I hope I am able to explain it -

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,451 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
For each data source, a form has several automatically created variables. In your case, you're interested in the variable of the same as the data source. If the data source is called MultipleLEProdReleaseDirectDelivery, the variable is multipleLEProdReleaseDirectDelivery and it contains the active record of the data source.

Don't use MultipleLEProdReleaseDirectDelivery_ds.getFirst(); that's not what you want.

The company of a SysRecordTemplateTable record is in the DataAreaId field, therefore it has nothing to do with the container.

Note that you want to get data for a single company, but you put data for all companies to the temporary table and then filter our all others. That's a waste of resources. It's easier and more efficient if you simply put data just for the single company to the temporary table and then show all records without further ranges.

Therefore you code could be something like this:

public void lookup() { SysRecordTmpTemplate sysRecordTemplateTmp; changeCompany(multipleLEProdReleaseDirectDelivery.DataArea) { ... sysRecordTemplateTmp.insert(); } Query query = new Query(); query.addDataSource(tableNum(SysRecordTmpTemplate)); SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(SysRecordTmpTemplate), this); sysTableLookup.parmQuery(query); sysTableLookup.addLookupField(fieldNum(SysRecordTmpTemplate, Description)); sysTableLookup.parmTmpBuffer(sysRecordTemplateTmp); sysTableLookup.performFormLookup(); }

Was this reply helpful? Yes No
Sangram Shinde 2,120 on at

Like (0)

Report
Copy link

Link copied!

Thank you Martin - Yes, it is easier and more efficient if I simply put data just for the single company to the temporary table -

The catch here is - while inserting data in SysRecordTmpTemplate table table itself, I don't see any specific identifier to get only values specific to selected company from SysRecordTemplateTable. (DataAreaId has 'dat' values for all templates as shown in previous discussions)

I checked the standard functionality, it also shows all cross company templates. I am wondering is it even feasible/valid to filter the template based on company..

Was this reply helpful? Yes No
Martin Dráb 239,451 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

My experience is different. SysRecordTemplateTable stores data per company and when I create a template, it's created with the current company in DataAreaId field. Can you please try it too and if you get dat instead of the current company, can you show us how you create templates?

When checking DataAreaId field, look into the table. Don't depends on the lookup form, because that could be showing anything.

Was this reply helpful? Yes No
Sangram Shinde 2,120 on at

Like (0)

Report
Copy link

Link copied!
Thank you Martin for suggestion, made a modification in code -

legalEntities = inserted only current selected record's company Id in container (previously inserting all LEs of selected records), and inserted data only specific to that legal entity and it worked.

Observed one thing here, somehow changeCompany() did not work here, it used to return data only from current company that user is performing the release and bypassing the changeCompany() code. Not sure what was the reason, but below code works - inserted data only specific to current selected record.

//Get data from template table from selected legal entity while select crosscompany :legalEntities templateTable index hint TableIdIdx where templateTable.Table == tableNum(InventTable) { dataContainer = dataContainer + templateTable.Data; }

Was this reply helpful? Yes No