Update Blob storage file data key value using ADF pipeline

 

Activity: Web activity

URL: @concat(pipeline().globalParameters.gp_blobServiceEndpoint,'adf/dealerPortalConfigs/ReleaseDatesConfig.json')

Method: Put

Body: @concat('{

    "value_ReleaseDate":"',variables('v_NewReleaseDate'),'"

}')

Headers:

x-ms-version => 2017-11-09

x-ms-blob-type => BlockBlob

Update ISV license on CHD D365FO

 Open command prompt:

//Enable maintenance mode

cd K:\AosService\PackagesLocalDirectory\Bin\

Microsoft.Dynamics.AX.Deployment.Setup.exe --metadatadir K:\AosService\PackagesLocalDirectory --bindir K:\AosService\PackagesLocalDirectory\Bin --sqlserver . --sqldatabase axdb --sqluser axdbadmin --sqlpwd "password from LCS" --setupmode maintenancemode --isinmaintenancemode true

//Restart IIS

 

iisreset

 

 //Import new license file comman

Microsoft.Dynamics.AX.Deployment.Setup.exe --setupmode importlicensefile --metadatadir K:\AOSService\PackagesLocalDirectory --bindir K:\AOSService\PackagesLocalDirectory --sqlserver . --sqldatabase AxDB --sqluser axdbadmin --sqlpwd "password from LCS" --licensefilename C:\Temp\<"license file name">.txt

 

 //Disbale maintenance mode

Microsoft.Dynamics.AX.Deployment.Setup.exe --metadatadir K:\AosService\PackagesLocalDirectory --bindir K:\AosService\PackagesLocalDirectory\Bin --sqlserver . --sqldatabase axdb --sqluser axdbadmin --sqlpwd "password from LCS" --setupmode maintenancemode --isinmaintenancemode false

//Restart IIS

iisreset

Build ISV model and custom model (If any).

DB Sync

Missing_index in D365FO

 Refrence:

sys.dm_db_missing_index_details (Transact-SQL) - SQL Server | Microsoft Learn

Set visibility of menu item based on Feature or parameter options in D365FO

 [SubscribesTo(classstr(SysMenuNavigationObjectFactory), staticdelegatestr(SysMenuNavigationObjectFactory, checkAddSubMenuDelegate))]

public static void menuItemVisibilityHandler(SysDictMenu _rootMenu, SysDictMenu _subMenu, SysBoxedBoolean _subMenuVisibility)

{

    if (_subMenu.isMenuItem())

    {

        var metadataElement = _subMenu.GetMenuItemMetaElement();

        if (metadataElement != null)

        {

            SAN_HelperCls::santst_setVisibilityMenuItems(metadataElement.Name, _subMenuVisibility);

        }

    }

}

public class  SAN_HelperCls
{

private static void santst_setVisibilityMenuItems(str _menuItemName, SysBoxedBoolean _subMenuVisibility)

{

    const str menuItemsPrefix = "SAN_TEST";

    if (strScan(_menuItemName, menuItemsPrefix , 0, strLen(_menuItemName)))

    {

        _subMenuVisibility.value = SAN_HelperCls::isEnabled();

    }

}

 public static boolean isEnabled()

 {

     return isConfigurationkeyEnabled(configurationkeynum(Trade)) && 

             FeatureStateProvider::isFeatureEnabled(SAN_DevFeature::instance());

 }

}

Parameter control (Yes/No)

or

Feature class:

using System.ComponentModel.Composition;

using Microsoft.Dynamics.ApplicationPlatform.FeatureExposure;

/// <summary>

/// The <c>SAN_DevFeature</c> class defines the enable advanced order hold feature.

/// </summary>

[ExportAttribute(identifierstr(Microsoft.Dynamics.ApplicationPlatform.FeatureExposure.IFeatureMetadata))]

[Microsoft.Dynamics.BusinessPlatform.SharedTypes.InternalUseOnlyAttribute]

public final class SAN_DevFeature implements IFeatureMetadata, IFeatureLifecycle

{

    private static SAN_DevFeature instance;

    private void new()

    {

    }

    [Hookable(false)]

    public WebSiteURL learnMoreUrl()

    {

        return 'https://learn.microsoft.com/en-us/';

    }

    private static void TypeNew()

    {

        instance = new SAN_DevFeature();

    }

    /// <summary>

    /// Obtains the singleton object instance.

    /// </summary>

    /// <returns>The <c>SAN_DevFeature</c> instance.</returns>

    [Hookable(false)]

    public static SAN_DevFeature instance()

    {

        return SAN_DevFeature::instance;

    }

    [Hookable(false)]

    public FeatureLabelId label()

    {

        return literalStr("SAN DEV tool features");

    }

    [Hookable(false)]

    public int module()

    {

        return FeatureModuleV0::SalesAndMarketing;

    }

    [Hookable(false)]

    public FeatureLabelId summary()

    {

        return literalStr("SAN DEV tool features");

    }

    [Hookable(false)]

    public boolean isEnabledByDefault()

    {

        return false;

    }

    [Hookable(false)]

    public boolean canDisable()

    {

        return true;

    }

    [Hookable(false)]

    public FeatureLifecycleStage FeatureStage()

    {

        return FeatureLifecycleStage::Released;

    }

}

Getting multiple row data into single SQL column

Table data:

Id | Text | Description

1 | Test1 | Test description

1 | Test2 | Test description

1 | Test3 | Test description

2 | Test2 | Test description

3 | Test3 | Test description

Expected result: System should return "Test1;Test2;Test3" for data related to Id = '1'

Query (T-SQL)

Sample 1:

SELECT  t.LegalEntity, t.ITEMNUMBER,

       replace(STUFF((SELECT '; ' + CAST(t1.Text AS VARCHAR(255)) [text()]

         FROM TESTTABLE t1

         WHERE t1.ID = t.ITEMNUMBER and t1.LEGALENTITY = t.LEGALENTITY

         FOR XML PATH(''), TYPE)

        .value('.','NVARCHAR(255)'),1,2,''),' ','') AS ComputeDescription

FROM InventTable  t

where  t.ITEMNUMBER in ('1') and t.LEGALENTITY = 'usmf'

group by t.LegalEntity, t.ITEMNUMBER

Sample 2:

SELECT it.ITEMLE, it.ITEMNUMBER,

CAST (ISNULL((SELECT STRING_AGG(t1.Text,';') AS ProductLine 

 FROM TESTTABLE t1

where t1.ID = it.ITEMNUMBER and t1.LEGALENTITY = it.DataAreaId),'') as NVARCHAR(255)) AS ComputeDescription

from InventTable it

where it.DataAreaId = 'chhq'

and it.ITEMNUMBER in ('0000505','0000511','0000497','0000499')

To Get line total charge amount & Tax Amount posted for Sales invoice lines D365FO X++

For reporting or inquiry purpose to review consolidated charges per Invoice line.

Using these two below view we can get Charge amount posted (Prorate or fixed or percent or pcs or any category) and along with Tax amount posted on it.

MarkupTransLineTotalChargeTaxAmountView - Tax Amount

MarkupTransLineTotalChargeAmountView - Charge amount

Upload file CSV/TXT using SysOperation in D365 X++

Upload CSV/TXT using SysOperation X++

Contract:

/// <summary>

/// Contract class upload through excel for PO inbound journals

/// </summary>

[DataContractAttribute,

    SysOperationContractProcessingAttribute(classStr(SAN_POImportUIBuilder))]

public class SAN_POImportContract extends SysOperationDataContractBase 

                    Implements SysOperationInitializable, SysOperationValidatable

{

    FileName                    fileName;

    private System.IO.Stream    inputDataStream;

    NoYes                       hasColumnNames;

    Str1260                     layout;

    /// <summary>

    /// Validates the parameters.

    /// </summary>

    /// <returns>

    /// true if successful; otherwise, false.

    /// </returns>

    public boolean validate()

    {

        return true;

    }

    public void initialize()

    {

        hasColumnNames = NoYes::Yes;

    }

    /// <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 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 header column") ]

    public NoYes parmHasColumnNames(NoYes _hasColumnNames = hasColumnNames)

    {

        hasColumnNames = _hasColumnNames;

        return hasColumnNames;

    }

    /// <summary>

    /// Set or get Input file data stream

    /// </summary>

    /// <param name = "_value">current value of stream</param>

    /// <returns>file memory stream to process</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 =  'TXT/CSV File delimited as |' + '\n';

        layout += strfmt("%1 | %2 : %3\n", '1', 'A', 'Column1');

        layout += strfmt("%1 | %2 : %3\n", '2', 'B', 'Column2');

        layout += strfmt("%1 | %2 : %3\n", '3', 'C', 'Column3');

        layout += strfmt("%1 | %2 : %3\n", '4', 'D', 'Column4');

        layout += strfmt("%1 | %2 : %3\n", '5', 'E', 'Column5');

        return layout;

    }

}

UI Builder:

/// <summary>

/// Class is to upload through excel for PO inbound substitution journals to handle run time contract operations

/// </summary>

class SAN_POImportUIBuilder extends SysOperationAutomaticUIBuilder

{

    #define.commandButton('CommandButton')

    #define.fileUpload('FileUpload')

    #define.fileTypesAccepted('.txt,.csv')

    

    SAN_POImportContract   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_POImportContract;

        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_POImportContract, parmHasColumnNames), contract);

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

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

        layoutTabPage.fastTabExpanded(FastTabExpanded::Yes);

        dialogLayout = this.addDialogField(methodStr(SAN_POImportContract, 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_POImportContract;

        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);

    }

}

Controller:

/// <summary>

/// Class is to handle controller logic to upload  PO journals

/// </summary>

class SAN_POImportController extends SysOperationServiceController

{

    public ClassDescription caption()

    {

        return "Upload  Purchase Journals";

    }

    /// <summary>

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

    /// </summary>

    /// <param name = "_args">

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

    /// </param>

    public static void main(Args _args)

    {

        SAN_POImportController controller;

        controller = SAN_POImportController::construct();

        controller.showBatchTab(false);

        controller.parmArgs(_args);

        controller.startOperation();

    }

    /// <summary>

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

    /// </summary>

    /// <returns>

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

    /// </returns>

    public static SAN_POImportController construct()

    {

        SAN_POImportController   controller;

        

        controller = new SAN_POImportController(classStr(SAN_POImportService),

                                                        methodStr(SAN_POImportService, Run),

                                                        SysOperationExecutionMode::Synchronous);

        return controller;

    }

}

Service:

/// <summary>

/// Class is to upload through excel for PO upload journals to handle service operations

/// </summary>

class SAN_POImportService extends SysOperationServiceBase

{

    System.IO.Stream                        stream;

    int                                     totalLinesProcessed = 0;

    SAN_POImportContract        contract;

    str                             filename, journalProcessId;

    /// <summary>

    /// process file to upload WG purchase line item substitution journals

    /// </summary>

    /// <param name = "_contract">

    /// Contract class.

    /// </param>

    public void run(SAN_POImportContract _contract)

    {

        contract            = _contract;

        stream              = _contract.parmInputDataStream();

        filename            = _contract.parmFileName();

        this.processFile(_contract);

    }

    /// <summary>

    /// Processing excel file input

    /// </summary>

    /// <param name = "_contract">

    /// Contract class.

    /// </param>

    protected void processFile(SAN_POImportContract _contract)

    {

        #File

        TextStreamIo inFile = TextStreamIo::constructForRead(stream);

        inFile.inFieldDelimiter('|'); //TODO delimiter used

        inFile.inRecordDelimiter('\r\n'); 

        container recordContainer;

        str item;

        boolean     isFirstLine = true;

        recordContainer = inFile.read();

        if(_contract.parmHasColumnNames())

        {

            recordContainer = inFile.read();

        }

        

        ttsbegin;

        while (inFile.status() == IO_Status::OK)

        {

            if(isFirstLine)

            {

journalProcessId = '';

                //Header record creation logic

                isFirstLine= false;

            }

           //line record creation logic

            //Read the next line

            recordContainer = inFile.read();

        }

        ttscommit;

        info(strFmt("Journal - %2 Total record lines created %1",totalLinesProcessed, journalProcessId));

    }

}

OData Batch request API - D365FO

 --batch_MultipleInvoices

Content-Type: multipart/mixed;boundary=changeset_123

--changeset_123

Content-Type: application/http

Content-Transfer-Encoding: binary

POST https://<d365fourl>/data/PortalSalesDocuments/Microsoft.Dynamics.DataEntities.getSalesInvoice HTTP/1.1

Content-Type: application/json

Content-ID: 1

{

    "invoiceId": "SIAA00026",

    "dataAreaId": "usmf"

}

--changeset_123

Content-Type: application/http

Content-Transfer-Encoding: binary

POST https://<d365fourl>/data/PortalSalesDocuments/Microsoft.Dynamics.DataEntities.getSalesInvoice HTTP/1.1

Content-Type: application/json

Content-ID: 2

{

    "invoiceId": "SIAA00047",

    "dataAreaId": "usmf"

}

--changeset_123--

--batch_MultipleInvoices--

Disabling the flight in D365FO (CHD - Tier 1)

 INSERT INTO dbo.SYSFLIGHTING(FLIGHTNAME, ENABLED) 

VALUES ('<FlightObjectName>_KillSwitch', 1)

 or 

INSERT INTO dbo.SYSFLIGHTING(FLIGHTNAME, ENABLED) 

VALUES ('<FlightObjectName>', 0)

Get unicode character in excel

 Excel formula

=AND(ISTEXT(C2),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(C2),"a",),"b",),"c",),"d",),"e",),"f",),"g",),"h",),"i",),"j",),"k",),"l",),"m",),"n",),"o",),"p",),"q",),"r",),"s",),"t",),"u",),"v",),"w",),"x",),"y",),"z",),"-",),",",),"",),"0",),"1",),"2",),"3",),"4",) ,"5",),"6",),"7",),"8",),"9",),"&",),")",),"(",),".",),"/",),"–",),"#",),"*",),"'",),"+",),":",),";",),"$",),"%",)= "")