Create agreement header & lines and link with Sales order X++

            //Process Started

  select firstOnly agreementClassification

                where agreementClassification.AgreementRelationType == tableNum(SalesAgreementHeader);

            row =1;

            worksheetSysHeader = worksheets.itemFromName(worksheetHeader);

            cells = worksheetSysHeader.cells();

            do

            {

                row++;

                salesAgreementHeader = SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());

                if(!salesAgreementHeader)

                {

                    salesAgreementHeader.clear();

                    salesAgreementHeader.initValue();

                    salesAgreementHeader.SalesNumberSequence   = cells.item(row, 1).value().bStr();

                    salesAgreementHeader.CustAccount   =  cells.item(row, 2).value().bStr();

                    salesAgreementHeader.initFromCustTable();

                    salesAgreementHeader.AgreementClassification = agreementClassification.RecId;

                    salesAgreementHeader.Currency  = cells.item(row, 9).value().bStr();

                    salesAgreementHeader.AgreementState = str2enum(agreementState, cells.item(row, 10).value().bStr());

                    salesAgreementHeader.DefaultAgreementLineEffectiveDate = str2Date(this.COMVariant2Str(cells.item(row, 5).value(), 0),213);

                    salesAgreementHeader.DefaultAgreementLineExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 6).value(), 0),213);

                    if (salesAgreementHeader.validateWrite())

                    {

                        salesAgreementHeader.insert();

                    }

                }

                else

                {

                    salesAgreementHeader.selectForUpdate(true);

                    salesAgreementHeader.Currency  = cells.item(row, 9).value().bStr();

                    salesAgreementHeader.AgreementState = str2enum(agreementState, cells.item(row, 10).value().bStr());

                    salesAgreementHeader.DefaultAgreementLineEffectiveDate = str2Date(this.COMVariant2Str(cells.item(row, 5).value(), 0),213);

                    salesAgreementHeader.DefaultAgreementLineExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 6).value(), 0),213);

                    if (salesAgreementHeader.validateWrite())

                    {

                        salesAgreementHeader.update();

                    }

                }

                if(salesAgreementHeader)

                {

                    if(!conFind(salesAgreementIds, salesAgreementHeader.SalesNumberSequence))

                    {

                        salesAgreementIds += salesAgreementHeader.SalesNumberSequence;

                    }

                    salesAgreementHeaderDefault = SalesAgreementHeaderDefault::findSalesAgreementHeader(salesAgreementHeader.RecId);

                    if(salesAgreementHeaderDefault)

                    {

                        salesAgreementHeaderDefault.selectForUpdate(true);

                        salesAgreementHeaderDefault.CustomerRequisitionNumber = cells.item(row, 12).value().bStr();

                        salesAgreementHeaderDefault.update();

                    }

                    else

                    {

                        salesAgreementHeaderDefault.clear();

                        salesAgreementHeaderDefault.initValue();

                        salesAgreementHeaderDefault.SalesAgreementHeader = salesAgreementHeader.RecId;

                        salesAgreementHeaderDefault.CustomerRequisitionNumber = cells.item(row, 12).value().bStr();

                        salesAgreementHeaderDefault.insert();

                    }

                    agreementHeaderDefault  = AgreementHeaderDefault::findAgreementHeader(salesAgreementHeader.RecId);

                    if(agreementHeaderDefault)

                    {

                        agreementHeaderDefault.selectForUpdate(true);

                        agreementHeaderDefault.DeliveryPostalAddress = CustTable::find(cells.item(row, 2).value().bStr()).postalAddress().RecId;

                        agreementHeaderDefault.DeliveryName = CustTable::find(cells.item(row, 2).value().bStr()).name();

                        agreementHeaderDefault.ContactPerson = cells.item(row, 4).value().bStr();

                        agreementHeaderDefault.ExternalReference = cells.item(row, 11).value().bStr();

                        agreementHeaderDefault.DeliveryTerm = cells.item(row, 8).value().bStr();

                        agreementHeaderDefault.PaymentTerms = cells.item(row, 7).value().bStr();

                        agreementHeaderDefault.ContactPersonDataAreaId = curext();

                        agreementHeaderDefault.PaymentTermsDataAreaId = curext();

                        agreementHeaderDefault.DeliveryTermDataAreaId = curext();

                        agreementHeaderDefault.ContactPersonDataAreaId = curext();

                        agreementHeaderDefault.update();

                    }

                    else

                    {

                        agreementHeaderDefault.clear();

                        agreementHeaderDefault.initValue();

                        agreementHeaderDefault.AgreementHeader = salesAgreementHeader.RecId;

                        agreementHeaderDefault.ContactPerson = cells.item(row, 4).value().bStr();

                        agreementHeaderDefault.ExternalReference = cells.item(row, 11).value().bStr();

                        agreementHeaderDefault.DeliveryTerm = cells.item(row, 8).value().bStr();

                        agreementHeaderDefault.PaymentTerms = cells.item(row, 7).value().bStr();

                        agreementHeaderDefault.DeliveryPostalAddress = CustTable::find(cells.item(row, 2).value().bStr()).postalAddress().RecId;

                        agreementHeaderDefault.DeliveryName = CustTable::find(cells.item(row, 2).value().bStr()).name();

                        agreementHeaderDefault.ContactPersonDataAreaId = curext();

                        agreementHeaderDefault.PaymentTermsDataAreaId = curext();

                        agreementHeaderDefault.DeliveryTermDataAreaId = curext();

                        agreementHeaderDefault.insert();

                    }

                }

                type = cells.item(row+1, 1).value().variantType();

            }

            while (type != COMVariantType::VT_EMPTY);

            row =1;

            cells = null;

            worksheetSysHeader = worksheets.itemFromName(worksheetLine);

            cells = worksheetSysHeader.cells();

            do

            {

                row++;

                salesAgreementHeader =  SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());

                agreementHeader = AgreementHeader::find(salesAgreementHeader.RecId);

                agreementHeaderDefault = AgreementHeaderDefault::findAgreementHeader(agreementHeader.RecId);

                salesAgreementHeaderDefault = SalesAgreementHeaderDefault::findSalesAgreementHeader(salesAgreementHeader.RecId);

                if(salesAgreementHeader && agreementHeader)

                {

                     select firstonly agreementLineQty

                        where agreementLineQty.Agreement == agreementHeader.RecId

                            && agreementLineQty.ItemId == cells.item(row, 2).value().bStr();

                    if(!agreementLineQty)

                    {

                        agreementLineQty.clear();

                        agreementLineQty.initValue();

                        agreementLineQty.initFromAgreementHeader(agreementHeader);

                        agreementLineQty.Agreement = agreementHeader.RecId;

                        agreementLineQty.AgreementLineProduct   = AgreementLineProduct::Item;

                        agreementLineQty.AgreementLineType = CommitmentType::ProductQuantity;

                        agreementLineQty.ItemId = cells.item(row, 2).value().bStr();

                        agreementLineQty.initFromInventTable();

                        inventDim = agreementLineQty.inventDim();

                        switch (agreementLineQty.agreementModuleType())

                        {

                            case ModuleSalesPurch::Sales:

                                inventDim.initFromInventTable(agreementLineQty.inventTable(), InventItemOrderSetupType::Sales, inventDim);

                                break;

                            case ModuleSalesPurch::Purch:

                                inventDim.initFromInventTable(agreementLineQty.inventTable(), InventItemOrderSetupType::Purch, inventDim);

                                break;

                        }

                        inventDim.InventDimId = InventDim::findOrCreate(inventDim).InventDimId;

                        agreementLineQty.setInventDimId(inventDim.InventDimId);

                        agreementLineQty.InventDimDataAreaId = curext();

                        agreementLineQty.ExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 14).value(), 0),213);

                        agreementLineQty.CommitedQuantity =  str2num(this.COMVariant2Str(cells.item(row, 4).value(), 2));

                        agreementLineQty.ProductUnitOfMeasure = cells.item(row, 5).value().bStr();

                        agreementLineQty.PricePerUnit = str2num(this.COMVariant2Str(cells.item(row, 6).value(), 2));

                        agreementLineQty.LineDiscountAmount =str2num(this.COMVariant2Str(cells.item(row, 7).value(), 2));

                        agreementLineQty.LineDiscountPercent =str2num(this.COMVariant2Str(cells.item(row, 8).value(), 2));

                        agreementLineQty.LineNumber = AgreementLine::lastLineNum(agreementHeader.RecId) + 1;

                        if (agreementLineQty.validateWrite())

                        {

                            agreementLineQty.insert();

                            agreementLineQty.salesAgreementHeader().update();

                        }

                    }

                    else

                    {

                        agreementLineQty.selectForUpdate(true);

                        agreementLineQty.ExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 14).value(), 0),213);

                        agreementLineQty.CommitedQuantity =  str2num(this.COMVariant2Str(cells.item(row, 4).value(), 2));

                        agreementLineQty.ProductUnitOfMeasure = cells.item(row, 5).value().bStr();

                        agreementLineQty.PricePerUnit = str2num(this.COMVariant2Str(cells.item(row, 6).value(), 2));

                        agreementLineQty.LineDiscountAmount =str2num(this.COMVariant2Str(cells.item(row, 7).value(), 2));

                        agreementLineQty.LineDiscountPercent =str2num(this.COMVariant2Str(cells.item(row, 8).value(), 2));

                        agreementLineQty.update();

                        agreementLineQty.salesAgreementHeader().update();

                    }

                    

                    if(agreementLineQty)

                    {

                        agreementLineDefault = AgreementLineDefault::findAgreementLine(agreementLineQty.RecId);

                        if(!agreementLineDefault)

                        {

                            agreementLineDefault.clear();

                            agreementLineDefault.initValue();

                            agreementLineDefault.AgreementLine = agreementLineQty.RecId;

                            agreementLineDefault.initFromAgreementHeaderDefault(agreementHeaderDefault);

                            agreementLineDefault.initFromSalesAgreementHeaderDefault(salesAgreementHeaderDefault);

                            agreementLineDefault.insert();

                        }

                    }

                }

                else

                {

                    info(strFmt("%1 Invalid sales agreement header reference",cells.item(row, 1).value().bStr()));

                }

                type = cells.item(row+1, 1).value().variantType();

            }

            while (type != COMVariantType::VT_EMPTY);

            row =1;

            cells = null;

            worksheetSysHeader = worksheets.itemFromName(worksheetRef);

            cells = worksheetSysHeader.cells();

            do

            {

                row++;

                salesAgreementHeader.clear();

                agreementHeader.clear();

                salesAgreementHeader =  SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());

                agreementHeader = AgreementHeader::find(salesAgreementHeader.RecId);

                

                agreementLine.clear();

                select firstOnly agreementLine

                    where agreementLine.Agreement == agreementHeader.RecId

                        && agreementLine.ItemId == cells.item(row, 3).value().bStr();

                

                if(agreementLine)

                {

                    salesLine.clear();

                    select firstOnly salesLine

                        where salesLine.SalesId == cells.item(row, 2).value().bStr()

                            && salesLine.ItemId == cells.item(row, 3).value().bStr(); 

                    if(salesLine)

                    {

                        salesTable = salesLine.salesTable();

                        if(salesTable)

                        {

                            salesTable.selectForUpdate(true);

                            salesTable.initFromSalesAgreementHeader(salesAgreementHeader);

                            salesTable.update();

                        }

                        SalesTableForm::createAgreementLinkServer(salesLine, agreementLine);

                    }

                    else

                    {

                        info(strFmt("%1 %2 combination of Sales order and item number doesnot exist", 

                        cells.item(row, 2).value().bStr(),

                        cells.item(row, 3).value().bStr()));

                    }

                }

                else

                {

                    info(strFmt("%1 %2 combination of Sales agreement and item number doesnot exist", 

                        cells.item(row, 1).value().bStr(),

                        cells.item(row, 3).value().bStr()));

                }

                type = cells.item(row+1, 1).value().variantType();

            }

            while (type != COMVariantType::VT_EMPTY);

            ttsCommit;

            //Process ended

SQL script to delete all staging table data in D365FO

 declare cu cursor for SELECT [name] FROM SYSTABLEIDVIEW where [name] like '%Staging'

declare @table varchar(100)

declare @sql nvarchar(1000)

OPEN cu  

FETCH NEXT FROM cu INTO @table 

WHILE @@FETCH_STATUS = 0  

BEGIN  

    set @sql = N'delete from ' + @table

    PRINT @table

    EXEC sp_executesql @sql

    FETCH NEXT FROM cu INTO @table 

END   

CLOSE cu;  

DEALLOCATE cu;

Get Category Hierarchy levels X++ D365FO

 class GetCategoryLevels

{

    public static void main(Args _args)

    {

// TOP to BOTTOM hierarchy level data

        void loopchild(RefRecId     _recId)

        {

            EcoResCategory   ecoResCategoryChk, ecoResCategoryChild;

            while select ecoResCategoryChk

                where ecoResCategoryChk.ParentCategory ==  _recId

            {

                //Child query insert to table Write ur logic

                Info(ecoResCategoryChk.Name);

                ecoResCategoryChild.clear();

                select firstonly  ecoResCategoryChild

                    where ecoResCategoryChild.ParentCategory ==  ecoResCategoryChk.RecId;

                if(ecoResCategoryChild)

                {

                    loopchild(ecoResCategoryChild.ParentCategory);

                }

            }

        }

        EcoResCategory  ecoResCategory;

        EcoResProductCategory   ecoResProductCategory, ecoResProductCategoryCHk;

        RefRecId parentCategory;

        select firstonly  ecoResCategory

            where ecoResCategory.Name == "Refrigeration";

        parentCategory = EcoResCategory.RecId;

        if(parentCategory)

        {

            //Parent query insert  - Write ur logic

            Info(ecoResCategory.Name);

            loopchild(parentCategory);

        }

    }

}

Set range for SrsReportRunController through X++ D365FO

 Map queryContracts  = reportController.parmReportContract().parmQueryContracts();

        MapEnumerator mapEnumerator = queryContracts.getEnumerator();

        while(mapEnumerator.moveNext())

        {

            Query query = mapEnumerator.currentValue();

            if(query)

            {

                QueryBuildDataSource qbds = query.dataSourceTable(tableNum(WHSWorkLine)); 

                if(qbds)

                {

                    qbds.addRange(fieldNum(WHSWorkLine, ShipmentId)).value(queryValue(_shipmentTable.ShipmentId));

                }

            }

        }

Function to check whether DS is exists or not in QUERY D365FO

QueryBuildDataSource findFirstDsForTable(Query _q, TableId _tableId)

{

    QueryBuildDataSource ds;

    int i;

    for (i = 1; i <= _q.dataSourceCount(); i++)

    {

        ds = _q.dataSourceNo(i);

        if (ds.table() == _tableId)

        {

            return ds;

        }

    }

    return null;

}

To ignore Special character("$Id"="1") on JSON serialize using data contract in D365FO

Decorate contract class with this attribute. 

[Newtonsoft.Json.JsonObjectAttribute(IsReference = false)]

Dynamically FORM CONTROLS - Generate FormDataSource fields on FORM based on setup and sequence in D365FO

Form Method:

Init -> method

    public void init()

    {

 

        DictTable                   dictTable;

        FormBuildDataSource         fds;

        FormBuildDesign             formBuildDesign;

 

        #DEFINE.FormWidth(500)

        #DEFINE.FormHeight(300)

 

        this.dictTable = new DictTable(tableNum(<TableName>));

 

        this.fds = this.form().dataSource(formDataSourceStr(<FormName>,<TableName>));

 

        formBuildDesign = this.form().design();

        formBuildDesign.widthMode(-1);

        formBuildDesign.widthValue(#FormWidth);

        formBuildDesign.heightMode(-1);

        formBuildDesign.heightValue(#FormHeight);

 

 FormBuildGroupControl  tabGroupCtrl = formBuildDesign.control(‘GroupName’);

        tabGroupCtrl.dataSource(fds.id());

        tabGroupCtrl.allowEdit(true);

        tabGroupCtrl.hideIfEmpty(false);

 

        //We can loop it as well, if requires for all fields

        this.addField(dictTable.id(), fieldName2Id(dictTable.id(), configFields.FieldName), fds, tabGroupCtrl, true);

 

        super(); //All build control should generate before init super call

    }

Addfield -> method

protected void addField(tableId _tableId,

        fieldId _fieldId,

        FormBuildDataSource _formBuildDataSource,

        FormBuildGroupControl _formBuildGroupControl,

        boolean _allowControlEdit)

    {

        FormBuildStringControl formBuildStringControl;

        FormBuildInt64Control formBuildInt64Control;

        FormBuildIntControl formBuildIntControl;

        FormBuildComboBoxControl formBuildComboBoxControl;

        FormBuildCheckBoxControl formBuildCheckBoxControl;

        FormBuildTimeControl formBuildTimeControl;

        FormBuildDateControl formBuildDateControl;

        FormBuildGuidControl formBuildGuidControl;

        FormBuildDateTimeControl formBuildDateTimeControl;

        FormBuildRealControl formBuildRealControl;

        FormBuildSegmentedEntryControl formBuildSegmentedEntryControl;

        FormBuildReferenceGroupControl formBuildReferenceGroupControl;

 

        FormBuildControl formBuildControl;

       

        SysDictField dictField;

        int j;

        tableId rootTableId;

        str controlName;

        UtilElementName utilElmName;

        Types baseType;

 

        dictField = new SysDictField(_tableId, _fieldId);

        if(dictField.isSystem())

        {

            //System fields are only stored on the root table, so get the information from there.

            rootTableId = SysDictTable::getRootTable(_tableId);

            dictField = new SysDictField(rootTableId, _fieldId);

        }

 

        if (dictField.saveContents())

        {

            for(j = 1;j <= dictField.arraySize(); j++)

            {

                formBuildControl = null;

 

                controlName = strfmt('%1_%2', dictField.name(), j);

                baseType = dictField.baseType();

                utilElmName = dictField.typeName();

 

                switch (baseType)

                {

                    case Types::String:

                    case Types::RString:

                    case Types::VarString:

                        formBuildStringControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                        formBuildStringControl.autoDeclaration(true);

                        formBuildStringControl.label(dictField.name());

 

                        formBuildStringControl.displayHeightMode(0);

                        formBuildStringControl.displayHeightValue(1);

 

                        formBuildControl = formBuildStringControl;

                        break;

 

                    case Types::Integer:

 

                        if (Global::isTypeTime(utilElmName))

                        {

                            formBuildTimeControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                            formBuildTimeControl.autoDeclaration(true);

                            formBuildTimeControl.label(dictField.name());

 

                            formBuildControl = formBuildTimeControl;

                        }

                        else

                        {

                            formBuildIntControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                            formBuildIntControl.autoDeclaration(true);

                            formBuildIntControl.label(dictField.name());

 

                            formBuildControl = formBuildIntControl;

                        }

                        break;

 

                    case Types::Int64:

                        // Reference Controls are created by default for surrogate foreign keys. However, the raw 64-bit

                        // integer should always be displayed. Force all Int64 type fields to use an Int64 Control.

                        formBuildControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

 

                        formBuildInt64Control = formBuildControl as formBuildInt64Control;

                        if (formBuildInt64Control != null)

                        {

                            formBuildInt64Control.autoDeclaration(true);

                            formBuildInt64Control.label(dictField.name());

                           

                            formBuildControl = formBuildInt64Control;

                        }

                        else

                        {

                            formBuildReferenceGroupControl = formbuildControl as FormBuildReferenceGroupControl;

                            if (formBuildReferenceGroupControl != null)

                            {

                                formBuildReferenceGroupControl.autoDeclaration(true);

                                formBuildReferenceGroupControl.label(dictField.name());

 

                                formBuildControl = formBuildReferenceGroupControl;

                            }

                            else

                            {

                                formBuildSegmentedEntryControl = formBuildControl as formBuildSegmentedEntryControl;

                                if (formBuildSegmentedEntryControl != null)

                                {

                                    formBuildSegmentedEntryControl.autoDeclaration(true);

                                    formBuildSegmentedEntryControl.label(dictField.name());

 

                                    formBuildControl = formBuildSegmentedEntryControl;

                                }

                            }

                        }

 

                        break;

 

                    case Types::Enum:

                        formBuildControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

 

                        formBuildComboBoxControl = formBuildControl as formBuildComboBoxControl;

                        if (formBuildComboBoxControl != null)

                        {

                            formBuildComboBoxControl.autoDeclaration(true);

                            formBuildComboBoxControl.label(dictField.name());

 

                            formBuildControl = formBuildComboBoxControl;

                        }

                        else

                        {

                            formBuildCheckBoxControl = formBuildControl as FormBuildCheckBoxControl;

                            if (formBuildCheckBoxControl != null)

                            {

                                formBuildCheckBoxControl.autoDeclaration(true);

                                formBuildCheckBoxControl.label(dictField.name());

 

                                formBuildControl = formBuildCheckBoxControl;

                            }

                        }

 

                        break;

 

                    case Types::Real:

                        formBuildRealControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                        formBuildRealControl.autoDeclaration(true);

                        formBuildRealControl.label(dictField.name());

                       

                        formBuildControl = formBuildComboBoxControl;

 

                        break;

 

                    case Types::Guid:

                        formBuildGuidControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                        formBuildGuidControl.autoDeclaration(true);

                        formBuildGuidControl.label(dictField.name());

 

                        formBuildControl = formBuildGuidControl;

 

                        break;

 

                    case Types::Date:

                        formBuildDateControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                        formBuildDateControl.autoDeclaration(true);

                        formBuildDateControl.label(dictField.name());

                      

                        formBuildControl = formBuildDateControl;

                       

                        break;

 

                    case Types::UtcDateTime:

                        formBuildDateTimeControl = _formBuildGroupControl.addDataField(_formBuildDataSource.id(), _fieldId, j);

                        formBuildDateTimeControl.autoDeclaration(true);

                        formBuildDateTimeControl.label(dictField.name());

 

                        formBuildControl = formBuildDateTimeControl;

 

                        break;

                }

 

 

                if (formbuildControl != null)

                {

                    formbuildControl.name(controlName);

                    formbuildControl.helpText(dictField.label());

 

                    formBuildSegmentedEntryControl = formBuildControl as FormBuildSegmentedEntryControl;

                    if (formBuildSegmentedEntryControl != null)

                    {

                        // Segmented Entry controls should never be editable in the Table Browser.

                        // There is no way to specify a Controller class so the user would see errors.

                        formBuildSegmentedEntryControl.allowEdit(false);

                    }

                    else

                    {

                        formbuildControl.allowEdit(_allowControlEdit);

                    }

                }

            }

        }

    }