Wednesday, June 27, 2018

What is the "Quantity Adjustment" Button in Inventory management In D365/Axapta.

It is very important to know the Quantity Adjustment through Inventory Management-Onhand Form.

It appears to create a single transaction with no Journal ID.

1. What is the purpose of this form?

Using this form for adjusting inventory quantity is basically identical to posting an counting journal for a specific item.

2. Can we add logging to it? what journal is used?

You can identify the transaction through the item transaction form but cannot open the counting journal as an ordinary user from the AX client.

3. What will be impact on costing if we adjust quantity using this form?

The impact on your costing will be that the inventory value is reduced or increased based on the number of items you specified to be on stock.

The technical information such as the trace log and classes related to this posting below:

\Data Dictionary\Tables\InventSite\Methods\changeDimension 29

\Classes\InventMov_Vir_Counting\defaultDimension                3

\Classes\InventUpdate\initInventTransPosting                    8

\Classes\InventUpdate\updateInventTransPosting                  5

\Classes\InventUpd_Physical\updateInventTransPosting            18

\Classes\InventUpd_Physical\updateNow                           69

\Classes\InventUpd_Financial\updateNow                          49

\Classes\WMSOnlineCountingServer\handleLine                     73

\Classes\WMSOnlineCountingServer\run                            19

\Classes\WMSOnlineCountingServer\validateRun                    3

\Classes\WMSOnlineCounting\validateRun                          3

\Forms\WMSOnlineCounting\Methods\canClose                       9

\Classes\FormRun\closeOk

After updating the quantity and clicking OK button the above trace log is generated.

It will update the quantity and financial dimension will update if settings exist on item or site. But, no journal number exist in counting journal.

Monday, June 25, 2018

Create a Translate button on new form in D365/Ax2012:

In standard we have translation in many places in D365. For example here I am showing in General Leger-Char of Accounts-Accounts-Main accounts.

 
 

Similarly, I have to develop the translation on another standard form called Questions as below:

Create a new table, class as below.
Table-KMQuestionTranslation. You need to write the table method. It is already there in the other standard Translation Tables.

Table properties as below:
 
Create a new class as below:
 
 
 
Write below code in Tables->KMQuestion->Insert():
 
 
 
Create a new Action Menu Item and add the menu item to the form extension as below:
 
 
 
Final project Area as below:
 
  Finally, the translation added to the Question Form.
 
 

Enjoy :)
 

User Date Sequence in D365 and Ax2012:

The below method will return the date sequence automatically.
Instead of hard coding as 123 or 213

str2Date(DateValue,123);

use the below code.


static int userDateSequence()
{
    int     ds1, ds2, ds3;
    date    d = str2Date('2015/12/18', 321);
    str     s = strFmt('%1', d);


    void datepart(str strDate, str dval, int part)
    {
        if (strEndsWith(strDate, dval))
            ds3 = part;
        else if (strStartsWith(strDate, dval))
            ds1 = part;
        else
            ds2 = part;
    }


    datepart(s, '18', 1);
    datepart(s, '12', 2);
    datepart(s, '2015', 3);

    return ds1 * 100 + ds2 * 10 + ds3;
}

Wednesday, June 6, 2018

Add new field in the Trade Agreements journal lines "Standard Price" and update item purchase price using the batch job based on the conditions.

Add new field in the Trade Agreements journal lines "Standard Price" and update the item purchase price using the batch job based on the conditions.

Procurement and sourcing > Prices and discounts > Trade agreement journals -> Create new PurchPrice trade agreement journal and go to lines.

1. Add the New field -> PriceDiscAdmTrans.Extension (Table)
2. Create a SysOperatoin - Contract class:

[DataContractAttribute]
class PurchPriceDataContract 
{
    TransDate   transDate;
    str         packedQuery;

    [DataMemberAttribute ,SysOperationLabelAttribute(literalStr("@ABC:PurchTradeAgreementLabel"))]
    public TransDate parmTransDate(TransDate _transDate = transDate)
    {
        transDate = _transDate;

        return transDate;
    }

    [DataMemberAttribute, AifQueryTypeAttribute('_packedQuery', querystr(ItemPurchPrice))]
    public str parmQuery(str _packedQuery = packedQuery)
    {
        packedQuery = _packedQuery;
        return packedQuery;
    }

    public Query getQuery()
    {
        return new Query(SysOperationHelper::base64Decode(packedQuery));
    }

    public void setQuery(Query _query)
    {
        packedQuery = SysOperationHelper::base64Encode(_query.pack());
    }

}
3. Created a Service class - 

class PurchPriceDataService 
{
    QueryRun                queryRun;
    InventTable             inventTable;
    InventTableModule       inventTableModule;
    PriceConvertFactory     priceConvertFactory;
    PriceConvert            priceConvert;
    TransDate               transDate;
    PriceDiscAdmTrans       priceDiscAdmTrans;
    PriceDiscAdmTable       priceDiscAdmTable;
    PriceCur                price;
    PriceUnit               priceUnit;
    
    //
    #ISOCountryRegionCodes
    boolean countryRegion_RU;
    //
    
    [SysEntryPointAttribute]
    public void updateItemPurchPrice(PurchPriceDataContract _purchPriceDataContract)
    {
        // Query objects
        Query                   query;
        QueryRun                queryRunpriceDiscAdmTrans;
        QueryBuildDataSource    qbdspriceDiscAdmTrans;
        QueryBuildDataSource    qbdspriceDiscAdmTable;
        QueryBuildRange         qbrpriceDiscAdmTrans;
        QueryBuildRange         qbrpriceDiscAdmTable;

        // create a new queryrun object
        queryRun = new queryRun(ItemPurchPriceDataContract.getQuery());

        // loop all results from the query
        while(queryRun.next())
        {
            inventTable = queryRun.get(tableNum(InventTable));
            transDate = _purchPriceDataContract.parmTransDate();

            if(inventTable.PrimaryVendorId)
            {
                query = new query();

                qbdspriceDiscAdmTable = query.addDataSource(tablenum(PriceDiscAdmTable));
                qbrpriceDiscAdmTable  = qbdspriceDiscAdmTable.addRange(fieldNum(PriceDiscAdmTable,DefaultRelation));
                qbrpriceDiscAdmTable.value(enum2Str(PriceType::PricePurch));

                qbrpriceDiscAdmTable  = qbdspriceDiscAdmTable.addRange(fieldNum(PriceDiscAdmTable,Posted));
                qbrpriceDiscAdmTable.value(enum2Str(NoYes::Yes));

                qbdspriceDiscAdmTrans = qbdspriceDiscAdmTable.addDataSource(tablenum(PriceDiscAdmTrans));

                qbdspriceDiscAdmTrans.addLink(fieldNum(PriceDiscAdmTable, JournalNum), fieldNum(PriceDiscAdmTrans, JournalNum));
        
                qbrpriceDiscAdmTrans = qbdspriceDiscAdmTrans.addRange(fieldNum(PriceDiscAdmTrans, Relation));
                qbrpriceDiscAdmTrans.value(enum2Str(PriceType::PricePurch));

                qbrpriceDiscAdmTrans = qbdspriceDiscAdmTrans.addRange(fieldNum(PriceDiscAdmTrans, ItemRelation));
                qbrpriceDiscAdmTrans.value(inventTable.itemId);

                qbrpriceDiscAdmTrans = qbdspriceDiscAdmTrans.addRange(fieldNum(PriceDiscAdmTrans, FromDate));
                qbrpriceDiscAdmTrans.value(SysQuery::range(transDate, dateNull()));

                qbrpriceDiscAdmTrans = qbdspriceDiscAdmTrans.addRange(fieldNum(PriceDiscAdmTrans, ToDate));
                qbrpriceDiscAdmTrans.value(SysQuery::range(dateNull(), transDate));

                // Results sorting
                qbdspriceDiscAdmTrans.addSortField(fieldNum(PriceDiscAdmTrans, StandardPurchPrice), SortOrder::Descending);
                qbdspriceDiscAdmTrans.addSortField(fieldNum(PriceDiscAdmTrans, Amount), SortOrder::Descending);
                qbdspriceDiscAdmTrans.firstOnly(true);
            
                queryRunpriceDiscAdmTrans = new QueryRun(query);
                if(queryRunpriceDiscAdmTrans.next())
                {
                    priceDiscAdmTrans = queryRunpriceDiscAdmTrans.get(tableNum(PriceDiscAdmTrans));
                    inventTableModule = InventTableModule::find(inventTable.ItemId,ModuleInventPurchSales::Purch);
                    priceConvertFactory = PriceConvertFactory::newItemPrice(priceDiscAdmTrans.ItemRelation,
                                                                        inventTableModule.UnitId,
                                                                        priceDiscAdmTrans.Amount,//inventTableModule.price(),
                                                                        inventTableModule.priceUnit(),
                                                                        inventTableModule.markup(),
                                                                        inventTableModule.priceQty(),
                                                                        '',
                                                                        countryRegion_RU ? inventTableModule.priceSecCur_RU() : 0,
                                                                        countryRegion_RU ? inventTableModule.markupSecCur_RU() : 0);

                    priceConvertFactory.parmNewCurrencyCode(priceDiscAdmTrans.Currency);
                    priceConvertFactory.parmTransDate(transDate);
                    priceConvertFactory.parmNewUnitId(priceDiscAdmTrans.UnitId);

                    priceConvert = priceConvertFactory.priceConvert();

                    price       = priceConvert.parmPrice();
                    ttsbegin;
                    inventTableModule.selectForUpdate(true);
                    inventTableModule.price = price;
                    inventTableModule.update();
                    ttscommit;
                }
            }
        }
    }

}

4. Create a AOT query for the contract class.




finally, update the price on the item purchase price where primary vendor is mentioned.

The above code will fetch the record from the trade agreements base on the dialog box itemID and date selection (which is a AOT Query) and then convert using the standard logic currency conversion, in case the company currency and trade agreements currency are different and then update the item purchase price.

Create Production Picking List Journal through code in D365:

The below class will create the production picking list journal header and lines through code based on the proposal and consumption quantity.

Let us assume for an item - Item001. The BOM proposal quantity = 5 and consumption quantity = 3 then the below code will create picking list journal for the production order - Item001- 3(qty).

In case the Production order has BOM lines more than 1 item then it will create for all the items.

//Create new picking list journal when difference between proposal and consumption on production order.
class ProdPickingListJourBOM
{
    ProdJournalTable            prodJournalTable;
    ProdJournalBOM              prodJournalBOM;
    ProdBOMConsumpProposal      consumpProposal;
    BOMAutoConsump              bomAutoConsump;
    ProdBOM                     consumptionProdBOM;
    NoYes                       consumpAsProposal;
    NoYes                       proposalAsConsump;

    NoYes                       drawNegative;
    NoYes                       skipAccError;
    NoYes                       endUpdate;

    OprNum                      oprNumFrom;
    OprNum                      oprNumTo;

    InventQty                   qtyCalc;
    BOMQty                      qtyConsumption;

    boolean                     isStart;
    boolean                     isFinish;// Reported as finished
    ProdId                      prodId;
    ProdJournalNameId           journalNameId;
    boolean                     createNoYes = false;
    ProdJournalTable            prodJournalOrig;

    public void create(ProdJournalTable _prodJournalTable)
    {
        #OCCRetryCount
        prodJournalOrig = _prodJournalTable;
        try
        {
            ttsbegin;

            this.initialValues();

            if (! this.validate())
            {
                throw error("@SYS18447");
            }

            this.check();

            if(createNoYes)
            {
                this.createHeader();
            }

            ttscommit;

        }
        catch (Exception::Deadlock)
        {
            retry;
        }
        catch (Exception::UpdateConflict)
        {
            if (appl.ttsLevel() == 0)
            {
                if (xSession::currentRetryCount() >= #RetryNum)
                {
                    throw Exception::UpdateConflictNotRecovered;
                }
                else
                {
                    retry;
                }
            }
            else
            {
                throw Exception::UpdateConflict;
            }
        }
    }

    void initialValues()
    {
        oprNumTo            =   0;
        bomAutoConsump      =    BOMAutoConsump::Never;
        consumpProposal     =    ProdBOMConsumpProposal::RemainQty;
        consumpAsProposal   =    NoYes::Yes;
        oprNumFrom          =    0;
        qtyCalc             =    0;
        endUpdate           =   NoYes::No;
        prodId              = prodJournalOrig.ProdId;
        journalNameId       = prodJournalOrig.JournalNameId;
    }

    boolean validate()
    {
        boolean ok = true;

        if (!prodId)
        {
            ok = checkFailed("@SYS26122");
        }

        if (!ProdTable::checkExist(prodId))
        {
            ok = false;
        }

        if (! ProdTable::find(prodId).status().isBefore(ProdStatus::Completed))
        {
            ok = checkFailed(strfmt("@SYS22041",ProdTable::find(prodId).ProdStatus));
        }
     
        if (!journalNameId)
        {
            ok = checkFailed("@SYS26138");
        }

        if (!ProdJournalName::checkExist(journalNameId))
        {
            ok = false;
        }

        if (consumpProposal == ProdBOMConsumpProposal::Reversed)
        {
            ok = ok && ProdJournalBOM::checkNotMissingPosting(prodId);
        }

        ok = ok && this.checkCreateLinesProdBOM();

        return ok;
    }

    boolean check()
    {
        ProdBOM prodBOM;
        ProdJournalBOM      prodJournalBomOrig;
        BOMCalcData         bomCalcData     = BOMCalcData::newProdTable(qtyCalc,ProdTable::find(prodJournalOrig.ProdId));

        while select forupdate prodBOM
            index hint NumIdx
            join prodJournalBomOrig
            where   prodBOM.ProdId == prodJournalOrig.ProdId
                && prodJournalBomOrig.JournalId     == prodJournalOrig.JournalId
                && prodJournalBomOrig.InventTransId == prodBOM.InventTransId
            {
                ProdBOMCalc         prodBOMCalc     = ProdBOMCalc::newBOMCalcData(bomCalcData, prodBOM, consumpProposal, bomAutoConsump, !prodBOM.ConstantReleased, skipAccError, isStart, isFinish);

              if (prodBOMCalc.bomConsump() || prodBOMCalc.bomProposal() || prodBOMCalc.inventConsump() || prodBOMCalc.inventProposal()
                || ((endUpdate
                        &&  bomAutoConsump != BOMAutoConsump::FlushingPrincip || prodBOM.ProdFlushingPrincip != ProdFlushingPrincipBOM::Manual)
                        &&  prodBOM.BackorderStatus == ProdBackStatus::Release))
                {
                    createNoYes = true;
                    break;
                }
            }

        return createNoYes;
    }

    boolean checkCreateLinesProdBOM()
    {
        boolean     ret                     = true;
        ProdBOM     prodBOM;
        //
        ProdTable   prodTable               = ProdTable::find(prodId);
        boolean     isProjMethodConsumed    = prodTable.isProjMethodConsumed();
        boolean     calculatingWIP_RU       = prodTable.calculatingWIP_RU();
        //

        //
        if (isProjMethodConsumed || calculatingWIP_RU)
        //
        {
            while select prodBOM
                index hint NumIdx
                where prodBOM.ProdId == prodId
                    &&  prodBOM.OprNum >= oprNumFrom
                    &&  (prodBOM.OprNum <= oprNumTo    || !oprNumTo)
            {
                //
                setprefix(strfmt("@SYS24300", prodBOM.ItemId));

                if (isProjMethodConsumed)
                {
                    //
                    ret = ret && prodBOM.validateProject();
                    //
                }

                if (calculatingWIP_RU)
                {
                    ret = ret && prodBOM.checkBOMQty_RU(true);
                }
                //
            }
        }

        return ret;
    }

    public ProdJournalTable createHeader()
    {
        ProdTable           prodTable = prodTable::find(prodJournalOrig.ProdId);

        prodJournalTable.clear();
        prodJournalTable.initValue();
        prodJournalTable.JournalType    = ProdJournalType::Picklist;
        prodJournalTable.JournalNameId  = prodJournalOrig.JournalNameId;
        prodJournalTable.Description    = prodJournalOrig.Description;
        prodJournalTable.ProdId         = prodTable.ProdId;
        prodJournalTable.VoucherDraw    = JournalVoucherDraw::Post;
        prodJournalTable.VoucherSeqRecId = NumberSequenceTable::find(ProdParameters::numRefProdJournalVoucherId().NumberSequenceId).RecId;
        if (!prodJournalTable.JournalId)
        {
            prodJournalTable.DrawNegative = drawNegative;
        }
        prodJournalTable.AutoCreateDiff = NoYes::Yes;
        prodJournalTable.insert();

        this.createLines();
     
        prodJournalTable.selectForUpdate(true);
        prodJournalTable.NumOfLines = ProdJournalBOM::lastLineNum(prodJournalTable.JournalId);
        prodJournalTable.update();

        return prodJournalTable;
     
    }

    public void createLines()
    {
        ProdBOM             prodBOM;
        ProdJournalBOM      prodJournalBomOrig;
        BOMCalcData         bomCalcData    = BOMCalcData::newProdTable(qtyCalc,ProdTable::find(prodJournalOrig.ProdId));
     
        setPrefix("@SYS28536");

        if (!oprNumTo)
        {
            oprNumTo         = oprNumFrom;
        }

        while select forupdate prodBOM
            index hint NumIdx
            join prodJournalBomOrig
            where   prodBOM.ProdId == prodJournalOrig.ProdId
                && prodJournalBomOrig.JournalId == prodJournalOrig.JournalId
                && prodJournalBomOrig.InventTransId == prodBOM.InventTransId
                &&  prodBOM.OprNum >= oprNumFrom
                &&  (prodBOM.OprNum <= oprNumTo    || !oprNumTo)
        {
            ProdBOMCalc prodBOMCalc = ProdBOMCalc::newBOMCalcData(bomCalcData, prodBOM, consumpProposal, bomAutoConsump, !prodBOM.ConstantReleased, skipAccError, isStart, isFinish);

            this.createSingleLineProdBOM(prodBOM,
                                            prodBOMCalc.bomConsump(),
                                            prodBOMCalc.bomProposal(),
                                            prodBOMCalc.inventConsump(),
                                            prodBOMCalc.inventProposal(),
                                            prodBOMCalc.pdsCWInventConsump(),
                                            prodBOMCalc.pdsCWInventProposal());
        }
    }

    public void createSingleLineProdBOM(
        ProdBOM     _prodBOM,
        UnitQty     _bomConsump,
        UnitQty     _bomProposal,
        UnitQty     _inventConsump,
        UnitQty     _inventProposal,
        UnitQty     _pdsCWInventConsump,
        UnitQty     _pdsCWInventProposal)
    {

        if (_bomConsump || _bomProposal || _inventConsump || _inventProposal
                || ((endUpdate
                        &&  bomAutoConsump != BOMAutoConsump::FlushingPrincip || _prodBOM.ProdFlushingPrincip != ProdFlushingPrincipBOM::Manual)
                        &&  _prodBOM.BackorderStatus == ProdBackStatus::Release))
        {

            prodJournalBOM.clear();
            prodJournalBOM.initValue();

            prodJournalBOM.JournalId        = prodJournalTable.JournalId;

            prodJournalBOM.initFromProdBOM(_prodBOM);

            prodJournalBOM.EndConsump     = endUpdate;

            //
            if (TaxParameters::isExciseEnable_IN())
            {
                TransTaxInformation transTaxInformation_Current;
                TransTaxInformation transTaxInformation_Origin;

                transTaxInformation_Current = TransTaxInformationHelper_IN::findOrCreateTransTaxInformation(prodJournalBOM.TableId, prodJournalBOM.RecId, true);
                transTaxInformation_Origin  = TransTaxInformationHelper_IN::findOrCreateTransTaxInformation(_prodBOM.prodTable().TableId, _prodBOM.prodTable().RecId);

                transTaxInformation_Current.CompanyLocation = transTaxInformation_Origin.CompanyLocation;
                transTaxInformation_Current.TaxInformation  = transTaxInformation_Origin.TaxInformation;
                TransTaxInformationHelper_IN::initFromTaxInformation(transTaxInformation_Current);
                transTaxInformation_Current.ExciseType = ExciseType_IN::Manufacturer;
                ttsbegin;
                transTaxInformation_Current.doUpdate();
                ttscommit;
            }
            //

            prodJournalBOM.bomConsump      = _bomConsump;
            prodJournalBOM.bomProposal     = _bomProposal;
            prodJournalBOM.InventConsump   = _inventConsump;
            prodJournalBOM.InventProposal  = _inventProposal;

            prodJournalBOM.PdsCWInventConsump = _pdsCWInventConsump;
            prodJournalBOM.PdsCWInventProposal= _pdsCWInventProposal;

             
            if (consumpAsProposal)
            {
                prodJournalBOM.bomConsump           = prodJournalBOM.bomProposal;
                prodJournalBOM.InventConsump        = prodJournalBOM.InventProposal;
                prodJournalBOM.PdsCWInventConsump   = prodJournalBOM.PdsCWInventProposal;
            }

            if (proposalAsConsump)
            {
                prodJournalBOM.bomProposal          = prodJournalBOM.bomConsump;
                prodJournalBOM.InventProposal       = prodJournalBOM.InventConsump;
                prodJournalBOM.PdsCWInventProposal  = prodJournalBOM.PdsCWInventConsump;
            }
            prodJournalBOM.insert();
        }
    }

}

Form datasource OnActivated and OnModified method in D365:

OnActivated - DataSource Method:

///

    ///
    ///
    ///
    ///
    [FormDataSourceEventHandler(formDataSourceStr(InventJournalName, InventJournalName), FormDataSourceEventType::Activated)]
    public static void InventJournalName_OnActivated(FormDataSource sender, FormDataSourceEventArgs e)
    {
        FormRun                 formRun          = sender.formRun();
        FormDataSource          inventJournalName_ds   = formRun.dataSource(formDataSourceStr(InventJournalName, InventJournalName)) as FormDataSource;
        InventJournalName       inventJournalName= inventJournalName_ds   .cursor();

        FormTabPageControl      TabFinancialDimensions = formRun.design(0).controlName("TabFinancialDimensions");

        switch (inventJournalName.JournalType)
        {
            case InventJournalType::Movement:
                inventJournalName_ds.object(fieldNum(InventJournalName, LocationDimensionLink)).visible(true);
                TabFinancialDimensions.visible(true);
                break;

            default:
                inventJournalName_ds.object(fieldNum(InventJournalName, LocationDimensionLink)).visible(false);
                TabFinancialDimensions.visible(false);
        }
    }

OnModified - DataSource Method:

///

    ///
    ///
    ///
    ///
    [FormDataFieldEventHandler(formDataFieldStr(InventJournalName, InventJournalName, JournalType), FormDataFieldEventType::Modified)]
    public static void JournalType_OnModified(FormDataObject sender, FormDataFieldEventArgs e)
    {
        FormDataSource          inventJournalName_ds    = sender.datasource();
        FormRun                 formRun                 = sender.datasource().formRun();
        InventJournalName       inventJournalName       = inventJournalName_ds.cursor();

        FormTabPageControl      TabFinancialDimensions = formRun.design(0).controlName("TabFinancialDimensions");

        switch (inventJournalName.JournalType)
        {
            case InventJournalType::Movement:
                inventJournalName_ds.object(fieldNum(InventJournalName, LocationDimensionLink)).visible(true);
                TabFinancialDimensions.visible(true);
                break;

            default:
                inventJournalName_ds.object(fieldNum(InventJournalName, LocationDimensionLink)).visible(false);
                TabFinancialDimensions.visible(false);
        }
    }

Lookup method for Financial Dimension:

Financial dimension lookup :

 ///
    ///
    ///
    ///
    ///
    [FormControlEventHandler(formControlStr(InventParameters, GroupSetup_LocationDimensionAttribute), FormControlEventType::Lookup)]
    public static void GroupSetup_LocationDimensionAttribute_OnLookup(FormControl sender, FormControlEventArgs e)
    {
        InventParametersEventHandlers::createLookup(sender);

        FormControlCancelableSuperEventArgs cancelableArgs = e as FormControlCancelableSuperEventArgs;
        cancelableArgs.CancelSuperCall();
    }

    private static void createLookup(FormReferenceGroupControl _ctrl)
    {     

        SysReferenceTableLookup sysReferenceTableLookup;
        Query                   query;
        QueryBuildDataSource    qbdsDimensionAttribute;
        QueryBuildDataSource    qbdsDimensionAttributeSetItem;

        sysReferenceTableLookup = SysReferenceTableLookup::newParameters(tableNum(DimensionAttribute), _ctrl);
        sysReferenceTableLookup.addLookupfield(fieldNum(DimensionAttribute, Name));

        // This query must be kept in sync with the one used on the DimensionDefaultingControllerBase.setupEditAreaControls() method
        query = new Query();

        qbdsDimensionAttribute = query.addDataSource(tableNum(DimensionAttribute));
        qbdsDimensionAttribute.addRange(fieldNum(DimensionAttribute, Type)).value(SysQuery::valueNot(DimensionAttributeType::MainAccount));
        qbdsDimensionAttribute.addOrderByField(fieldNum(DimensionAttribute, Name));

        qbdsDimensionAttributeSetItem = qbdsDimensionAttribute.addDataSource(tableNum(DimensionAttributeSetItem));
        qbdsDimensionAttributeSetItem.joinMode(JoinMode::ExistsJoin);
        qbdsDimensionAttributeSetItem.fetchMode(QueryFetchMode::One2One);
        qbdsDimensionAttributeSetItem.relations(true);
        qbdsDimensionAttributeSetItem.addRange(fieldNum(DimensionAttributeSetItem, DimensionAttributeSet)).value(
                                        queryValue(DimensionCache::getDimensionAttributeSetForLedger()));

        sysReferenceTableLookup.parmQuery(query);

        sysReferenceTableLookup.performFormLookup();
    }

Tuesday, June 5, 2018

MICROSOFT DYNAMICS 365: DATA ENTITY FOR A TABLE WITHOUT A NATURAL KEY

Problem definition

Data entities have been introduced in Dynamics 365 for Finance and Operations (formally Dynamics AX 7) and can be regarded as an abstraction from the physical implementation of database tables. Implemented as de-normalized views (a data entity is actually stored as a view in SQL Server) with their own set of methods, they are now key elements in data import/export and integration scenarios.
A data entity can be quickly created by adding a new item of type Data entity to the project or by right clicking on a table and selecting Create data entity Addins option. However, if the primary data source of the data entity does not have a unique index (default Rec Id index is not taken into account) you are expected to get the "The natural key not found" error message.
For this post, a simple table TestTable has been created. The table contains three fields and does not have a unique index for now.
 Picture1.png

Generating a new data entity for it ends up with the error shown below.
 Picture2.png

 Picture3.png

The error comes from the system requirement that every data entity must have a primary key defined. So, it is possible to uniquely identify each row of the data that is conveyed through a data entity. For instance, Data Management Framework always evaluates data rows coming in for uniqueness using the data entity primary key. If a data row already exists in the system then it gets selected for update, otherwise the data is inserted.
Most often, changing of an already existing index on a table to being unique cannot be considered as a solution to the problem. This post is aimed at outlining several workarounds of how the issue can be overcome with "little blood".

Solving "The natural key not found" error

First approach is based on adding a new LineNum field (incremental identifier of Real type) to a table and building up a new table unique index based on it.

Picture4.png

This makes a difference and allows generating the data entity by standard means of Visual Studio for Dynamics 365, for instance by Data entity wizard.

Picture5.png

Table TestTable should also be supplied with a piece of code for the LineNum field default initialization, similar to that in the screenshot below.
 Picture6.png

Additionally, if there is any data in the original table, SQL update script needs to be prepared and executed in order to correctly initialize the missing LineNum values and eliminate DB synchronization errors.
Second approach is premised on the assumption that a predefined combination of input columns (or one column) can be used to uniquely identify each row of the input data consumed by a data entity. Data entity is much of a View object and its primary key may include different set of fields than that found in the unique index of its primary data source table. In other words, we can build up a data entity with a primary key that contains almost any valid field from its data source(s) and get it working properly. However, there are a couple of important prerequisites:
1) Combination of values in columns that correspond to the data entity primary key fields must be unique for each row of the incoming data
2) Incoming data must not violate data integrity enforced by the updated data sources that a data entity is built on.
In our case, we’ll start with an assumption that the Name field (or combination of other fields) can be taken as a data entity primary key, even though it is part of the non-unique table index. Provided that incoming names are unique for each data row, we are not expected to have troubles during import.
Standard "The natural key not found" validation check can be bypassed by using the following simple workarounds:
1) Take any existing data entity in the Application Explorer and create a new one by duplicating it (right clicking on the data entity and selecting Duplicate in project from the context menu). Rename and adjust/re-implement the newly created data entity by changing its data sources, fields, primary keys, properties, code, etc. Create the staging table and associate it with the data entity.
2) Start the Data Entity Wizard, specify appropriate properties but take any other simple table that has at least one unique index on it for the Primary datasource property to pass the validation check.

Picture7.png

Proceed with the wizard and then adjust appropriately the newly created data entity and staging table.
Third approach is based on the fact that a data entity primary key can be built on the primary data source RecId field. Standard data entity GeneralJournalAccountEntryEntity is a vivid example of applying such a technique.

Picture8.png

Although that conceptually contradicts to the main data entity paradigm, the workaround may be useful when there is a need to prepare a data entity for data import/export without troubling to much about changing the primary data source table or manipulating with a set of fields for the data entity primary key.
Thank you.