Modifying a DMF data entity query dynamically, from another query, for filteration in D365FO

 

Problem statement 

Have you ever came across a situation where you needed to run your data entity through a batch, and in your batch you have a very complex composite query (where the query ranges could be anything -- the user can keep adding new ranges to the query) and you need pass on the query range values to your data entity query. I bet you must have faced this: say, your data contract is having an AOT query called PurchContractQry where you have PurchTable, Purchline, InventDim, PurchParmLine and a litter of related tables. You have an entity that does have a different query (as you know Data entities store queries as packed data in DMFDefinitionGroupEntity)-- how could you possibly make the ranges flow to your Entity, then?

The following blog explains the same.

As an example: we have created a Data entity called D365DataEntityPurchExport, which exports records based on the ranges supplied in a query coming from a contract Class, called PurchContractQry. We need to get the ranges which user is selecting/enetering and pass them on to the entity and run it accordingly. 

Step 1: in your service class, you can get query from dataContract:

_contract.getQuery();

You can also get the query of your entity by simply saying:

Query    purchEnttQuery = new Query(DMFUtil::getDefaultQueryForEntityV3(_entityName));

Where _entityName is the name of the Entity you are using (in our case, it's DMFDefinitionGroupEntity).

Hence you can create a new query for your entity and get the contents of your query like this:

purchEnttQuery = this.addAdditionalRanges(_contract.getQuery(), purchEnttQuery ) ;

We are going to create a new method to get the ranges from our contract class, like this:

private Query addAdditionalRanges(Query _contractQry, Query    _entityQuery)

    {

        Counter totalDSources = _contractQry.dataSourceCount();

        QueryBuildDataSource qbdsPurch = _entityQuery.dataSourceTable(tableNum(D365DataEntityPurchExport));

        for(int ds =1; ds<= totalDSources ; ds++)

        {

            QueryBuildDataSource    qbds = _contractQry.dataSourceNo(ds);

            Counter totalRanges = qbds.rangeCount();

            for(int r =1; r<= totalRanges; r++)

            {

                QueryBuildRange qbr =  qbds.range(r);

                SysQuery::findOrCreateRange(qbdsPurch , fieldName2Id(tableNum(D365DataEntityPurchExport), qbr.fieldName())).value(qbr.value());

            }

        }

        return _entityQuery;

    }

Where dynamically your are setting the name of the query ranges in your data entity, by getting the values from your contract class Query.

Step2: We can execute an entity like this very easily:

            DMFEntityExporter exporter = new DMFEntityExporter();

            SharedServiceUnitFileID fileId = exporter.exportToFile(

                                           entityName,

                                           _dataContract.parmDefinitionGroupName(),

                                           '',

                                           DXCMVTExportMacros::csvFile,

                                           #FieldGroupName_AllFields,

                                           conNull(),                                           

                                           curExt(),

                                           null,               

                                           true,                

                                           false

                                           );

We can add this in the process method of our Service class. But the problem with this method is: it doesn't update the query you want it to execute -- it will make the entity to execute default version of the query. 

Why?

Well, look at the following code inside the above method:

Which means it will update the query of the entity, only when the definition group provided is blank. But what if, your definiton group is not blank -- purposely you want to execute your entity against any defintion group? 

Step 3: For this -- you can force the Definiton group entity to accept the packed query like this, before hand:

DMFDefinitionGroupEntity definitionGroupEntity = DMFDefinitionGroupEntity::find(_contract.parmDefinitionGroupName(), 

                                                                                        _entityName, 

                                                                                        true);

        if (definitionGroupEntity)

        {

            ttsbegin;

            definitionGroupEntity.QueryData = _query.pack();

            definitionGroupEntity.update();

            ttsCommit;

        }

As a result, the defintion group will be updated with the query value and be assigned to the entity: