vlookup in excel

 

• =VLOOKUP(A2,MDM!$A$2:$A$308971,1,FALSE)
• 
  
• Argument 1 -> lookup value
• Argument 2 -> Table array. Note: if it is different sheet then SheetName!Table array
• Argument 3 -> Column index to compare
• Argument 4 -> FALSE (Looking for exact match)

Adding custom field on customer payment generation in D365FO X++

Sample code only for reference.

[ExtensionOf(classStr(CustOverPaym))]

final class CustOverPaymCls_san_Extension

{

    private  container      sanCustCRCTypes;

    #define.CurrentVersion(3)

    #localmacro.CurrentList

        sanCustCRCTypes

    #endmacro

    /// <summary>

    /// Set and get Parm customer credit management types

    /// </summary>

    /// <param name = "_sanParams">current params</param>

    /// <returns>CustCRCTypes</returns>

    public container san_parmCustCRCTypes(container _sanParams = sanCustCRCTypes)

    {

        sanCustCRCTypes = _sanParams;

        return sanCustCRCTypes;

    }

    public container pack()

    {

        container packedClass = next pack();

        return SysPackExtensions::appendExtension(packedClass, classStr(CustOverPaymCls_san_Extension), this.myPack());

    }

    private container myPack()

    {

        return [#CurrentVersion, #CurrentList];

    }

    public boolean unpack(container _packedClass)

    {

        boolean result = next unpack(_packedClass);

        if (result)

        {

            container myState = SysPackExtensions::findExtension(_packedClass, classStr(CustOverPaymCls_san_Extension));

            if (!this.myUnpack(myState))

            {

                result = false;

            }

        }

        return result;

    }

    private boolean myUnpack(container packedClass)

    {

        Integer version = RunBase::getVersion(packedClass);

        switch (version)

        {

            case #CurrentVersion:

                [version, #currentList] = packedClass;

                break;

            default:

                return false;

        }

        return true;

    }

    /// <summary>

    /// Builds the cross company query used to calculate summarized totals. COC

    /// </summary>

    /// <param name = "_calcArgs">Contains arguments used to construct the query</param>

    /// <returns>The generated query.</returns>

    protected Query buildCrossCompanyQueryForCalcTotalSummarized(CustOverPaymCalcTotalArgs _calcArgs)

    {

        Query       sanQuery;

        sanQuery = next buildCrossCompanyQueryForCalcTotalSummarized(_calcArgs);

        if(sanCustCRCTypes != conNull())

        {

            this.san_setQueryRangeCustCRCTypes(sanQuery);

        }

        return sanQuery;

    }

    /// <summary>

    /// Builds the cross company query used to calculate not summarized totals. COC

    /// </summary>

    /// <param name = "_calcArgs">Contains arguments used to construct the query</param>

    /// <returns>The generated query.</returns>

    protected Query buildCrossCompanyQueryForCalcTotalNonSummarized(CustOverPaymCalcTotalArgs _calcArgs)

    {

        Query       sanQuery;

        sanQuery = next buildCrossCompanyQueryForCalcTotalNonSummarized(_calcArgs);

        if(sanCustCRCTypes != conNull())

        {

            this.san_setQueryRangeCustCRCTypes(sanQuery);

        }

        return sanQuery;

    }

    /// <summary>

    /// Builds the query used in methods <c>findTransactionsForReimbursementNonSummarized</c> and <c>findTransactionsForReimbursementSummarized</c>. COC

    /// </summary>

    /// <param name = "_findTransArgs">Contains arguments used to build the query.</param>

    /// <returns>The generated query.</returns>

    protected Query buildFindTransactionsForReimbursementQuery(CustOverPaymFindTransactionsForReimburseArgs _findTransArgs)

    {

        Query       sanQuery;

        sanQuery = next buildFindTransactionsForReimbursementQuery(_findTransArgs);

        if(sanCustCRCTypes != conNull())

        {

            this.san_setQueryRangeCustCRCTypes(sanQuery);

        }

        return sanQuery;

    }

    /// <summary>

    /// setting query range for customer credit management types to existing query

    /// </summary>

    /// <param name = "_query">Query</param>

    public void san_setQueryRangeCustCRCTypes(Query  _query)

    {

        QueryBuildDataSource    qbdsCustTable;

        str                     CustCRCTypesIds;

        san_CustCreditManagementTypes       custCreditManagementTypes;

        #define.sanSemicolon(';')

        #define.sanComma(',')

        qbdsCustTable = _query.dataSourceTable(tableNum(CustTable));

        if(qbdsCustTable == null)

        {

            qbdsCustTable = _query.addDataSource(tableNum(CustTable));

        }

        CustCRCTypesIds = con2StrUnlimited(sanCustCRCTypes,#sanComma);

        if (CustCRCTypesIds)

        {

            CustCRCTypesIds = Global::strReplace(CustCRCTypesIds, #sanSemicolon, #sanComma);

            qbdsCustTable.addRange(fieldNum(CustTrans, san_CustCreditMgmtType)).value(CustCRCTypesIds);

        }

    }

}

Read value from XML (Attribute based) in D365FO

     public void readXML_Test(str _invoiceId)

    {

        str xmlData = @'<?xml version="1.0" encoding="UTF-8"?>

<cfdi:Comprobante Moneda="MXN" xsi:schemaLocation="<URL>" Version="4.0" Serie="MI" Folio="22" Fecha="2024-05-07T15:55:22" NoCertificado="01101111100504511177" Certificado="Q1MDkwNzcwDQYJKo/mgAwIBAgIUMDAwMD5on" CondicionesDePago="Neto 30 Dias" Total="1160.00" TipoDeComprobante="I" MetodoPago="PPP" FormaPago="99" LugarExpedicion="66269" Exportacion="01" SubTotal="1000.00" Sello="Rpf" xmlns:cfdi="<URL>" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <cfdi:Emisor Rfc="TTTX6" Nombre="TEST SALES MEXICO" RegimenFiscal="601"/>

    <cfdi:Receptor Rfc="TT10427D90" Nombre="TEST INTERNATIONAL" UsoCFDI="G01" DomicilioFiscalReceptor="6310" RegimenFiscalReceptor="601"/>

    <cfdi:Conceptos>

        <cfdi:Concepto ClaveProdServ="10111300" NoIdentificacion="10111300" Cantidad="1" ClaveUnidad="ACT" Unidad="un" Descripcion="test" ValorUnitario="1000.000000" Importe="1000" ObjetoImp="02">

            <cfdi:Impuestos>

                <cfdi:Traslados>

                    <cfdi:Traslado Base="1000.00" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="160.00"/>

                </cfdi:Traslados>

            </cfdi:Impuestos>

        </cfdi:Concepto>

 <cfdi:Concepto ClaveProdServ="10111300" NoIdentificacion="10111300" Cantidad="1" ClaveUnidad="ACT" Unidad="un" Descripcion="test" ValorUnitario="1000.000000" Importe="1000" ObjetoImp="02">

            <cfdi:Impuestos>

                <cfdi:Traslados>

                    <cfdi:Traslado Base="1000.00" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="160.00"/>

                </cfdi:Traslados>

            </cfdi:Impuestos>

        </cfdi:Concepto>

    </cfdi:Conceptos>

    <cfdi:Impuestos TotalImpuestosTrasladados="160.00">

        <cfdi:Traslados>

            <cfdi:Traslado Base="1000.00" Impuesto="002" TipoFactor="Tasa" TasaOCuota="0.160000" Importe="160.00"/>

        </cfdi:Traslados>

    </cfdi:Impuestos>

    <cfdi:Complemento>

        <tfd:TimbreFiscalDigital Version="1.1" UUID="27230B06-966F-4F0B-8111-3F57EEBF4841" FechaTimbrado="2024-05-07T15:56:38" RfcProvCertif="INT020124V62" SelloCFD="Rpf2O==" xsi:schemaLocation="<>URL3" xmlns:tfd="<URL1>"/>

    </cfdi:Complemento>

</cfdi:Comprobante>';

        XmlDocument xmlDocument;

        XmlNodeList xmlRecords, xmlFields, xmlFields1, xmlFields2, xmlFields3;

        XmlNode xmlRecord, xmlField, xmlField1, xmlField2, xmlField3;

        XMLParseError xmlError;

        XmlElement xmlParentRecord;

        xmlDocument = new XmlDocument();

        xmlDocument.loadXml(xmlData);

        xmlError = xmlDocument.parseError();

        Map         mapHeader = new Map(Types::String, Types::Container);

        Map         mapParentNode = new Map(Types::String, Types::Container);

        Map         mapSummary = new Map(Types::String, Types::Container);

        Map         mapItemInfo = new Map(Types::String, Types::Container);

        str     einvoiceId = _invoiceId;

        xmlParentRecord = xmlDocument.root();

        var name = xmlParentRecord.name();

        xmlRecords  = xmlDocument.root().childNodes();

        xmlRecord   = xmlRecords.nextNode();

        XmlNamedNodeMap mapValue;

        XmlNode     curNode;

        mapValue = xmlParentRecord.attributes();

        

        if(!mapParentNode.exists(strFmt("ParentNode%1", einvoiceId)))

        {

            mapParentNode.insert(strFmt("ParentNode%1", einvoiceId), [mapValue.getNamedItem('CondicionesDePago').nodeValue(),mapValue.getNamedItem('Moneda').nodeValue(), mapValue.getNamedItem('TipoDeComprobante').nodeValue()]);

        }

        while (xmlRecord)

        {

            mapValue = null;

            switch(xmlRecord.name())

            {

                case "cfdi:Receptor" : //XML without any sub XML tags

                    mapValue = xmlRecord.attributes();

                    if(!mapHeader.exists(strFmt("Header%1%2", einvoiceId,xmlRecord.name())))

                    {

                        mapHeader.insert(strFmt("Header%1%2", einvoiceId,xmlRecord.name()), [mapValue.getNamedItem('UsoCFDI').nodeValue(),mapValue.getNamedItem('Nombre').nodeValue(), mapValue.getNamedItem('DomicilioFiscalReceptor').nodeValue()]);

                    }

                    break;

                case "cfdi:Emisor" : //XML without any sub XML tags

                    mapValue = xmlRecord.attributes();

                    if(!mapHeader.exists(strFmt("Header%1%2", einvoiceId,xmlRecord.name())))

                    {

                        mapHeader.insert(strFmt("Header%1%2", einvoiceId,xmlRecord.name()), [mapValue.getNamedItem('RegimenFiscal').nodeValue()]);

                    }

                    break;

                case "cfdi:Conceptos" : //XML with sub XML tags

                    xmlFields = xmlRecord.childNodes();

                    xmlField = xmlFields.nextNode();

                    int counter;

                    while (xmlField)

                    {

                        counter++;

                        anytype     claveProdServ,claveUnidad,netAmountImporte,description, base,impuesto,tipoFactor,TasaOcuota,actualSalesTaxImporte,descuento;

                        mapValue = xmlField.attributes();

                        claveProdServ = mapValue.getNamedItem('ClaveProdServ').nodeValue();

                        claveUnidad = mapValue.getNamedItem('ClaveUnidad').nodeValue();

                        netAmountImporte = mapValue.getNamedItem('Importe').nodeValue();

                        description = mapValue.getNamedItem('Descripcion').nodeValue();

                        if(mapValue.getNamedItem('Descuento'))

                        {

                            descuento = mapValue.getNamedItem('Descuento').nodeValue();

                        }

                        xmlFields1 = xmlField.childNodes();

                        xmlField1 =xmlFields1.nextNode();

                        while (xmlField1)

                        {

                            xmlFields2 = xmlField1.childNodes();

                            xmlField2 =xmlFields2.nextNode();

                            while (xmlField2)

                            {

                                xmlFields3 = xmlField2.childNodes();

                                xmlField3 =xmlFields3.nextNode();

                                while (xmlField3)

                                {

                                    mapValue = xmlField3.attributes();

                                    base = mapValue.getNamedItem('Base').nodeValue();

                                    impuesto = mapValue.getNamedItem('Impuesto').nodeValue();

                                    tipoFactor = mapValue.getNamedItem('TipoFactor').nodeValue();

                                    TasaOCuota = mapValue.getNamedItem('TasaOCuota').nodeValue();

                                    actualSalesTaxImporte = mapValue.getNamedItem('Importe').nodeValue();

                                    xmlField3 = xmlFields3.nextNode();

                                }

                                xmlField2 = xmlFields2.nextNode();

                            }

                            xmlField1 = xmlFields1.nextNode();

                        }

                        if(!mapItemInfo.exists(strFmt("Lines%1%2", einvoiceId,counter)))

                        {

                            mapItemInfo.insert(strFmt("Lines%1%2", einvoiceId,counter), [claveProdServ,claveUnidad,netAmountImporte,description, base,impuesto,tipoFactor,TasaOcuota,actualSalesTaxImporte,descuento]);

                        }

                        xmlField =xmlFields.nextNode();

                    }

                    break;

            }

            xmlRecord = xmlRecords.nextNode();

        }

        //To read values

        //if(mapItemInfo.exists(strFmt("Lines%1%2",InvoiceId,lineCounter)))

        //{

        //    container   itemInfoLines = mapItemInfo.lookup(strFmt("Lines%1%2",eInvoiceJourMapping.invoiceId(),lineCounter));

        //    conPeek(itemInfoLines,1);

        //    conPeek(itemInfoLines,2);

        //    conPeek(itemInfoLines,3);

        //    conPeek(itemInfoLines,4);

        //}

    }

Upload data from Excel in D365FO X++

 Action Menu Item: SAN_UploadExcelData

Object type: Class

Object: <Controller class name>

Label: <>

Class:

Controller class

class SAN_UploadExcelDataController extends SysOperationServiceController

{

    /// <summary>

    /// Sets the class caption.

    /// </summary>

    public ClassDescription caption()

    {

        return "Upload excel data";

    }

    /// <summary>

    /// Provides entry point for the instance of <c>SAN_UploadExcelDataController</c>.

    /// </summary>

    /// <param name = "_args">

    /// The arguments passed to the class <c>SAN_UploadExcelDataController</c>.

    /// </param>

    public static void main(Args _args)

    {

        SAN_UploadExcelDataController controller;

        controller = SAN_UploadExcelDataController::construct();

        controller.showBatchTab(false);

        controller.parmArgs(_args);

        controller.startOperation();

    }

    /// <summary>

    /// Initializes new instance of <c>SAN_UploadExcelDataController</c>.

    /// </summary>

    /// <returns>

    /// Return object of <c>SAN_UploadExcelDataController</c>.

    /// </returns>

    public static SAN_UploadExcelDataController construct()

    {

        SAN_UploadExcelDataController   controller;

        

        controller = new SAN_UploadExcelDataController(classStr(SAN_UploadExcelDataService),

                                                        methodStr(SAN_UploadExcelDataService, Run),

                                                        SysOperationExecutionMode::Synchronous);

        return controller;

    }

}

Contract class:

[DataContractAttribute,

SysOperationContractProcessingAttribute(classStr(SAN_UploadExcelDataUIBuilder))]

public class SAN_UploadExcelDataContract extends SysOperationDataContractBase implements SysOperationValidatable

{

    Str1260                     layout;

    NoYes                       hasColumnNames;

    FileName                    fileName;

    private System.IO.Stream    inputDataStream;

    public boolean validate()

    {

        boolean isValid = true;

        return isValid;

    }

    /// <summary>

    /// Gets or sets the value of the data contract parameter hasColumnNames.

    /// </summary>

    /// <param name="_hasColumnNames">

    /// The new value of the data contract parameter hasColumnNames.

    /// </param>

    /// <returns>

    /// Returns the current value of data contract parameter hasColumnNames.

    /// </returns>

    [DataMemberAttribute,

        SysOperationLabelAttribute("File has column name in first row") ]

    public NoYes parmHasColumnNames(NoYes _hasColumnNames = hasColumnNames)

    {

        hasColumnNames = _hasColumnNames;

        return hasColumnNames;

    }

    /// <summary>

    /// Gets or sets the value of the data contract parameter fileName.

    /// </summary>

    /// <param name="_fileName">

    /// The new value of the data contract parameter fileName.

    /// </param>

    /// <returns>

    /// Returns the current value of data contract parameter fileName.

    /// </returns>

    public Filename parmFileName(FileName _fileName = fileName)

    {

        fileName = _fileName;

        return fileName;

    }

    /// <summary>

    /// Gets or sets the value of inputDataStream.

    /// </summary>

    /// <param name="_value">

    /// The new value of the parameter inputDataStream.

    /// </param>

    /// <returns>

    /// Returns the current value of data contract parameter inputDataStream.

    /// </returns>

    [Hookable(false)]

    public System.IO.Stream parmInputDataStream(System.IO.Stream _value = inputDataStream)

    {

        inputDataStream = _value;

        return inputDataStream;

    }

    /// <summary>

    /// Gets or sets the value of the data contract parameter fileLayout.

    /// </summary>

    /// <param name="_layout">

    /// The new value of the data contract parameter fileLayout.

    /// </param>

    /// <returns>

    /// Returns the current value of data contract parameter fileLayout.

    /// </returns>

    [DataMemberAttribute,

        SysOperationLabelAttribute("File layout") ]

    public Str1260 parmLayout(Str1260 _layout = layout)

    {

        layout =  '';

        layout += strfmt("%1 | %2 - %3\n", '1', 'A', "@SYS12128");  //Invoice

        layout += strfmt("%1 | %2 - %3\n", '2', 'B', "Number");  //Item number

        layout += strfmt("%1 | %2 - %3\n", '3', 'C', "Date");//Date

        layout += strfmt("%1 | %2 - %3\n", '4', 'D', "Status");  //Status ENUM

        layout += strfmt("%1 | %2 - %3\n", '5', 'E', "@SYS14578");    //Quantity

        

        return layout;

    }

}

UI builder class:

class SAN_UploadExcelDataUIBuilder extends SysOperationAutomaticUIBuilder

{

    #define.commandButton('CommandButton')

    #define.fileUpload('FileUpload')

    #define.fileTypesAccepted('.xlsx,.xls')

    

    SAN_UploadExcelDataContract   contract;

    /// <summary>

    /// Adds a button to the dialog to import a file.

    /// </summary>

    [Hookable(false)]

    public void build()

    {

        DialogGroup             dialogGroup;

        DialogTabPage           dialogTabPage;

        Dialog                  dlg;

        FormBuildControl        formBuildControl;

        FileUploadBuild         dialogFileUpload;

        DialogField             dialogLayout;

        FormBuildTabPageControl parametersTabPage;

        FormBuildTabPageControl layoutTabPage;

        dlg = this.dialog();

        contract = this.dataContractObject() as SAN_UploadExcelDataContract;

        dialogTabPage = dlg.addTabPage("@SYS7764");

        dialogTabPage.columns(2);

        parametersTabPage = dlg.formBuildDesign().control(dialogTabPage.name());

        parametersTabPage.fastTabExpanded(FastTabExpanded::Yes);

        dialogGroup = dlg.addGroup('Upload file');

        formBuildControl = dlg.formBuildDesign().control(dialogGroup.name());

        dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), #fileUpload);

        dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);

        dialogFileUpload.baseFileUploadStrategyClassName(classstr(FileUploadTemporaryStorageStrategy));

        dialogFileUpload.fileTypesAccepted(#fileTypesAccepted);

        dialogFileUpload.fileNameLabel("@SYS308842");

       

        dlg.addGroup("@SYS339526");

        this.addDialogField(methodStr(SAN_UploadExcelDataContract, parmHasColumnNames), contract);

 

        dialogTabPage = dlg.addTabPage("@Polaris:FileLayout");

        layoutTabPage = dlg.formBuildDesign().control(dialogTabPage.name());

        layoutTabPage.fastTabExpanded(FastTabExpanded::Yes);

        dialogLayout = this.addDialogField(methodStr(SAN_UploadExcelDataContract, parmLayout), contract);

        dialogLayout.showLabel(false);

        dialogLayout.enabled(false);

        dialogLayout.widthMode(1);

        dialogLayout.displayHeight(15);

    }

    /// <summary>

    /// Handles dialog closing events.

    /// </summary>

    /// <param name = "sender">

    /// xFormRun class.

    /// </param>

    /// <param name = "e">

    /// FormEventArgs class.

    /// </param>

    [SuppressBPWarningAttribute('BPParametersNotUsed', 'This event parameter is not used')]

    private void dialogClosing(xFormRun sender, FormEventArgs e)

    {

        FormEventArgs formEventArgs;

        formEventArgs = e;

        this.dialogEventsUnsubscribe(sender as FormRun);

    }

    /// <summary>

    /// Handles dialog events.

    /// </summary>

    /// <param name = "_formRun">

    /// FormRun class type.

    /// </param>

    private void dialogEventsSubscribe(FormRun _formRun)

    {

        FileUpload fileUpload = _formRun.control(_formRun.controlId(#fileUpload));

        fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);

        fileUpload.notifyUploadAttemptStarted += eventhandler(this.uploadStarted);

        _formRun.onClosing += eventhandler(this.dialogClosing);

    }

    /// <summary>

    /// Handles dialog event.

    /// </summary>

    /// <param name = "_formRun">

    /// FormRun class type.

    /// </param>

    private void dialogEventsUnsubscribe(FormRun _formRun)

    {

        FileUpload fileUpload = _formRun.control(_formRun.controlId(#fileUpload));

        fileUpload.notifyUploadCompleted -= eventhandler(this.uploadCompleted);

        fileUpload.notifyUploadAttemptStarted -= eventhandler(this.uploadStarted);

        _formRun.onClosing -= eventhandler(this.dialogClosing);

    }

    /// <summary>

    /// Handles dialog event post run.

    /// </summary>

    [Hookable(false)]

    public void postRun()

    {

        super();

        FormRun formRun = this.dialog().dialogForm().formRun();

        this.dialogEventsSubscribe(formRun);

        this.setDialogOkButtonEnabled(formRun, false);

    }

    /// <summary>

    /// Enables or disables the dialog Ok button.

    /// </summary>

    /// <param name = "_formRun">

    /// The <c>FormRun</c> object.

    /// </param>

    /// <param name = "_isEnabled">

    /// Indicates to enable or disable the Ok button.

    /// </param>

    protected void setDialogOkButtonEnabled(FormRun _formRun, boolean _isEnabled)

    {

        FormControl okButtonControl = _formRun.control(_formRun.controlId(#commandButton));

        if (okButtonControl)

        {

            okButtonControl.enabled(_isEnabled);

        }

    }

    /// <summary>

    /// After the file has been uploaded, the Ok button is enabled.

    /// </summary>

    protected void uploadCompleted()

    {

        contract = this.dataContractObject() as SAN_UploadExcelDataContract;

        var formRun = this.dialog().dialogForm().formRun();

        FileUpload fileUpload = formRun.control(formRun.controlId(#fileUpload));

        contract.parmFileName(fileUpload.fileName());

        using (System.IO.Stream stream = fileUpload.getUploadedFile(true))

        {

            if (stream)

            {

                System.IO.MemoryStream copiedStream = new System.IO.MemoryStream();

                stream.CopyTo(copiedStream);

                

                contract.parmInputDataStream(copiedStream);

            }

        }

        this.setDialogOkButtonEnabled(formRun, contract.parmInputDataStream() != null);

    }

    /// <summary>

    /// During file upload, the Ok button is disabled.

    /// </summary>

    private void uploadStarted()

    {

        var formRun = this.dialog().dialogForm().formRun();

        this.setDialogOkButtonEnabled(formRun, false);

    }

}

Service class:

class SAN_UploadExcelDataService extends SysOperationServiceBase

{

    OfficeOpenXml.ExcelRange        cells;

    System.IO.Stream                stream;

    NoYes                           hasColumnNames;

    boolean                         validated = false;

    container                       rowData;

    int                             rowCounter;

    int                             rowsWithError;

    boolean                         error = false;

    int                             totalRows;

    int                             rowStarter;

    int                             lineNumber;

    InventSiteId                    lineInventSiteId;

    FileName                        fileNameWithExt;

    FileName                        fileName;

    TextStreamIo                    streamIO;

    boolean                         exceptionOccurred;

    str                             lineNumberText;

    boolean                         emptyRow;

    TransDate                       today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());

    container                       orderUnique;

    str                             invoiceId, itemId, number;

    PurchStatus                     statusValue;

    TransDate                       dateValue;

    Qty                             quantity;

    PurchStatus                     purchStatus;

    #file

    #define.totalCells(5) // TODO need to be changed as per layout column nos

    /// <summary>

    /// Creates and initializes the file object.

    /// </summary>

    public void initFile()

    {

        #define.validationFileSuffix('-data-validation-errors.txt')

        InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);

        

        perm.assert();

        streamIO = TextStreamIO::constructForWrite(#utf8format);

        streamIO.outRecordDelimiter(#delimiterCRLF);

        fileName += #validationFileSuffix;

    }

    /// <summary>

    /// The excel file objects are initialized here.

    /// </summary>

    public void initFileRead()

    {

        OfficeOpenXml.ExcelWorksheet worksheet;

        OfficeOpenXml.ExcelPackage   package;

        int                          totalCells;

        rowStarter = hasColumnNames ? 2 : 1;

        

        try

        {

            package = new OfficeOpenXml.ExcelPackage(stream);

            worksheet       = package.get_Workbook().get_Worksheets().get_Item(1);

            cells           = worksheet.Cells;

            totalRows       = worksheet.Dimension.End.Row;

            totalCells      = worksheet.Dimension.End.Column;

            if (totalCells != #totalCells)

            {

                error = true;

                error(strFmt("File should contain %1 columns but contains %2 columns.", #totalCells, totalCells));

            }

        }

        catch

        {

            error = true;

            error("@SYS79574" + ' ' + "@SYS25338"+'  '+"An unexpected error occured while importing order data from excel file");

        }

    }

    /// <summary>

    /// Called after data has been processed and file has been generated. Closes the file created.

    /// </summary>

    public void downloadFile()

    {

        if (streamIo != null)

        {

            File::SendFileToUser(streamIo.getStream(), fileName);

        }

        CodeAccessPermission::revertAssert();

    }

    /// <summary>

    /// Display upload summary after operation is executed.

    /// </summary>

    public void displaySummary()

    {

        if (error)

        {

            if (!streamIO)

            {

                return;

            }

            this.downloadFile();

            setPrefix("Data validation summary");

            info(strFmt("File name: %1",fileNamewithExt));

            info(strFmt("Number of lines in the file: %1", totalRows));

            info(strFmt("Number of valid lines: %1", totalRows-rowsWithError));

            info(strfmt("Number of lines with errors: %1",rowsWithError));

            error("Import data from Excel stopped because of the data validation errors in the Excel file.");

            error("Fix the data validation errors and then upload the Excel file to run the data validation again.");

            error("Data validation error file has automatically downloaded to your machine. Please save the file and review the errors to make the appropriate corrections and then upload the updated file.");

            error(fileName);

        }

        else

        {

            setPrefix("Import data summary");

            info(strFmt("File name: %1", fileNameWithExt));

            info(strfmt("Number of lines in the file: %1t", totalRows));

        }

    }

    /// <summary>

    /// Process purchase order creation after file is validated.

    /// </summary>

    public void importFile()

    {

        if (error)

        {

            return;

        }

        try

        {

            orderUnique = conNull();

            ttsbegin;

            for (rowCounter = rowStarter; rowCounter<= totalRows; rowCounter++)

            {

                this.setRowData();

                this.uploadData();

            }

            ttscommit;

        }

        catch

        {

            error = true;

            error("An unexpected error occured while importing order data from excel file");

            this.displaySummary();

        }

    }

    public void uploadData()

    {

        //TODO

        //Logic to be written on Table

    }

    /// <summary>

    /// Process the purchase order import functionality.

    /// </summary>

    /// <param name = "_contract">

    /// Contract class.

    /// </param>

    public void run(SAN_UploadExcelDataContract    _contract)

    {

        SAN_UploadExcelDataContract   contract;

        int                                     position;

        try

        { 

            contract          = _contract;

            stream              = contract.parmInputDataStream();

            hasColumnNames      = contract.parmHasColumnNames();

            fileNameWithExt     = contract.parmFileName();

              

            position = strFind(fileNameWithExt, '.', 0, strlen(fileNameWithExt));

            FileName = strDel(fileNameWithExt, position, strlen(fileNameWithExt));

            FileName = strReplace(FileName, ' ', '-');

        

            this.initFileRead();

            this.validateFile();

            this.importFile();

            this.displaySummary();

        }

        catch

        {

            error("An unexpected error occured while importing order data from excel file");

        }

    }

    private void resetValues()

    {

        rowData             = conNull();

        exceptionOccurred   = false;

        emptyRow            = true;

        invoiceId = '';

        itemId = '';

        statusValue = PurchStatus::Backorder;

        dateValue = dateNull();

        quantity = 0;

    }

    /// <summary>

    /// Set values by reading excel row data.

    /// </summary>

    public void setRowData()

    {

        boolean             ret = true;

        NoYes               noYes;

        

        try

        {

            int     i;

            

            this.resetValues();

            lineNumberText = strFmt("Line number %1 >>", rowCounter);

            for (i = 1; i <= #totalCells; i++)

            {

                rowData = conIns(rowData, i, cells.get_Item(rowCounter, i).Value);

                

                if (emptyRow && conPeek(rowData, i))

                {

                    emptyRow = false;

                }

            }

            if (emptyRow)

            {

                ret = this.writeToLogFile(strFmt(lineNumberText + "The row contains no data."));

                return;

            }

            if (typeOf(conPeek(rowData, 1)) == Types::String ||

                typeOf(conPeek(rowData, 1)) == Types::Real)     //InvoiceId

            {

                invoiceId = strLRTrim(conPeek(rowData, 1).ToString());

            }

            else if (conPeek(rowData, 1) != null)

            {

                ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeString: %1 must be a string but the value does not meet the expected format.", "Invoice number"));

            }

            if (typeOf(conPeek(rowData, 2)) == Types::String ||

                typeOf(conPeek(rowData, 2)) == Types::Real)      //Item number

            {

                itemId = strLRTrim(conPeek(rowData, 2).ToString());

            }

            else if (conPeek(rowData, 2) != null)

            {

                ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeString: %1 must be a string but the value does not meet the expected format.", "Item number"));

            }

            if (typeOf(conPeek(rowData, 3)) == Types::Date ||

                typeOf(conPeek(rowData, 3)) == Types::Class)  //Date

            {

                dateValue = conPeek(rowData, 3);

            }

            else if (typeOf(conPeek(rowData, 3)) == Types::String)

            {

                dateValue = str2Date(conPeek(rowData, 3), -1);

                if (!dateValue)

                {

                    ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeDate: %1 must be a date but the value does not meet the expected format. Sample date format is %2.", "Pedimento date", DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone())));

                }

            }

            if (typeOf(conPeek(rowData, 4)) == Types::String ||

                typeOf(conPeek(rowData, 4)) == Types::Real)      // Status

            {

                statusValue = str2Enum(purchStatus, strLRTrim(conPeek(rowData, 4).ToString()));

            }

            else if (conPeek(rowData, 4) != null)

            {

                ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeString: %1 must be a string but the value does not meet the expected format.", "Status"));

            }

            if (typeOf(conPeek(rowData, 5)) == Types::Integer ||

                typeOf(conPeek(rowData, 5)) == Types::Real)     //Quantity

            {

                quantity = conPeek(rowData, 5);

            }

            else if (conPeek(rowData, 5) != null)

            {

                ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeNumber: %1 must be a number but the value does not meet the expected format.", "Quantity"));

            }

            

            //error = !ret;

        }

        catch

        {

            error = true;

            ret = this.writeToLogFile(strFmt(lineNumberText+"@Polaris:ImportSalesOrderIllegalValue"));

        }

    }

    /// <summary>

    /// Validates Purchase order import file by looping through rows.

    /// </summary>

    /// <returns>

    /// Returns true or false based on validation.

    /// </returns>

    public boolean validateFile()

    {

        if (error)

        {

            return error;

        }

        orderUnique = conNull();

        for (rowCounter = rowStarter; rowCounter<= totalRows; rowCounter++)

        {

            this.setRowData();

            if(!this.validateRowData())

            {

                error = true;

                rowsWithError++;

            }

        }

        return error;

    }

    /// <summary>

    /// Validates purchase order line from excel

    /// </summary>

    /// <returns>

    /// Returns true or false based on validation.

    /// </returns>

    boolean validateRowData()

    {

        boolean   ret;

        

        ret = true;

        if (emptyRow)

        {

            return false;

        }

        return ret;

    }

    private boolean writeToLogFile(FreeTxt _errorTxt)

    {

        if(!streamIO)

        {

            this.initFile();

        }

            

        streamIO.write(_errorTxt);

        return false;

    }

}

SQL Delete statement with Not exists join

Sample SQL: 

Requirement: Delete with Not exsits join in where condition

DELETE  ITM FROM  InventItemInventSetup ITM 

WHERE  NOT EXISTS (SELECT 1 FROM   INVENTTABLE IT WHERE IT.DataAreaId =  ITM.DataAreaId 

AND IT.ITEMID = ITM.ITEMID)

       AND ITM.DATAAREAID in ('USMF')

Get Feature is enabled or disabled in D365FO X++

 Issue: Feature is enabled or disabled to get in customize ISV/VAR/USR model. Since it was defined as internal class by MS, and which can't be accessible in reference model.

Workaround solution (X++) in ISV/VAR/USR model: Without using MS OOB class.

IdentifierName featureEnableFeature = 'Dynamics.AX.Application.<>';

        FeatureManagementState featureManagementState;

boolean isFeatureEnabled = false;

        select firstonly featureManagementState

            where featureManagementState.Name == featureEnableFeature ;

        if (featureManagementState.IsEnabled)

        {

            isFeatureEnabled  = true;

        }

Data maintenance portal in D365FO (Process automation)

 Issue: After DB restore from PROD to NON-PROD environment, Data maintenance activity and Process automation wasn't working

Solution: Change batch job "Process automation background processes system job" status from WITHHOLD to Waiting

Convert Stream to Base64String in D365FO X++

using Microsoft.Dynamics365.LocalizationFramework;

String getBase64FromString(

            System.IO.Stream        _pdfStream)

    {

        str base64data = '';

        if (_pdfStream != null)

        {

            using (System.IO.Stream fileStream = _pdfStream)

            {

                using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())

                {

                    fileStream.CopyTo(memoryStream);

                    base64data =  ERConversionUtils::StreamToBase64(memoryStream);

                }

            }

        return base64data ;

    }

Condition:

Feature "Utilize application resources to perform CBD documents conversion from Word to PDF format" is enabled,

Then stream aligned to get Base64String.

Workaround logic:

str     fileDownloadURL;

            System.IO.Stream        tempPdfStream;

            fileDownloadURL = File::SendFileToTempStore(_pdfStream, this.parmSaveFilePath());

            tempPdfStream = File::UseFileFromURL(fileDownloadURL);

Consume External API in D365FO using .Net library

 using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Net.Http;

using System.Net.Http.Headers;

using System.Text;

using System.Threading.Tasks;

using Newtonsoft.Json;

namespace SAN_NotificationsLibrary

{

    public class SAN_NotificationService

    {

        public void ProcessNotification(string userName, string password, string url, string clientId, string clientSecretKey, string customerId, string subject, int days, string body)

        {

            string tokenPath = "/identity/connect/token";

            var uri = new Uri(url); 

            var accessToken = GetAccessToken($"{uri.Scheme}:

            var client = new HttpClient();

            client.BaseAddress = new Uri($"{uri.Scheme}:

            var request = new HttpRequestMessage(HttpMethod.Post, uri.AbsolutePath);

            client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken);

            var data = new

            {

                subject = subject,

                body = body,

                customerNumber = customerId,

                duration = days

            };

            request.Content = new StringContent(JsonConvert.SerializeObject(data), Encoding.UTF8, "application/json");

            var response = client.SendAsync(request).Result;

           

        }

        private string GetAccessToken(string baseUrl, string tokenPath, string clientId, string clientSecret, string userName, string password)

        {

            var client = new HttpClient();

            client.BaseAddress = new Uri(baseUrl);

            var request = new HttpRequestMessage(HttpMethod.Post, tokenPath);

            var byteArray = new UTF8Encoding().GetBytes($"{clientId}:{clientSecret}");

            client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));

            var postData = new List<KeyValuePair<string, string>>();

            postData.Add(new KeyValuePair<string, string>("grant_type", "password"));

            postData.Add(new KeyValuePair<string, string>("username", userName));

            postData.Add(new KeyValuePair<string, string>("password", password));

            request.Content = new FormUrlEncodedContent(postData);

            var response = client.SendAsync(request).Result;

            var t = response.Content.ReadAsStringAsync().Result;

            var token = JsonConvert.DeserializeObject<Token>(t);

            return token?.AccessToken;

        }

        internal class Token

        {

            [JsonProperty("access_token")]

            public string AccessToken { get; set; }

            [JsonProperty("token_type")]

            public string TokenType { get; set; }

            [JsonProperty("expires_in")]

            public int ExpiresIn { get; set; }

            [JsonProperty("refresh_token")]

            public string RefreshToken { get; set; }

        }

    }

}

RemoveRecIdSuspension in D365FO X++

     protected void removeRecIdSuspension()

    {

        TableId         tableId= <Table num>;

appl.sysRecIdSequence().removeRecIdSuspension(tableId);

    }