D365 FO: ER Diagrams (Entity Relationship Diagrams)

                  D365 FO: ER Diagrams (Entity Relationship Diagrams)

 

 

Today we went to learn about the Visual Studio extension tool which is used to Visualize the D365FO data models.

The tool is called dbdiagram.io, the Database Markup Language (DBML), and the Generate Entity Relation Schema extension for Visual Studio, we can interrogate and transform the metamodel into a visual representation of our entity relationships.

We can download the tool from github.

 

Process of installation:

1.      Download the Waywo.DbSchema.Addin.dll from Releases.

2.      

2.      Unblock the dll. Once the download is done, right-click and view the properties and check Unblock and click ok.

4.      In your Documents\Visual Studio Dynamics 365 folder there is a DynamicsDevConfig.xml XML file. like this:

<?xml version="1.0" encoding="utf-8"?>

<DynamicsDevConfig xmlns:i="http://www.w3.org/2001/XMLSchema-instance"

                   xmlns="http://schemas.microsoft.com/dynamics/2012/03/development/configuration">       

  <AddInPaths xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">            

      <d2p1:string>C:\D365CustomAddins</d2p1:string>       

  </AddInPaths>  

</DynamicsDevConfig>

5.     Edit the XML in note pad and find the tag “AddInPaths”

6.     Copy the Waywo.DBSchema.AddIn.dll to the AddInPath folder (create the folder or change the path to where the "Waywo.DbSchema.AddIn.dll" is downloaded.) and then restart Visual Studio.

7.     In the Visual studio from the table designer, right-click àAddinsàGenerate entity relation schema.

8.     

 

Data Entity: PostTargetProcess and PostGetStagingData Methods to Update Target Table D365 FO X++

 Data Entity: Methods to Update Target Table D365 FO X++

 

 

Recently we got a requirement to update the target table with the execution id and show the status of the Entity status on the Dynamics AX Form.

 

In this case, we can use the method “PostTargetProcess” method, which allows you to use DMFDefinitionGroupExecution from where we can get the Entity DefinitionGroup and Execution Id.

This method is automatically called by the Framework class DMFEntityWriter at the end of processRecords() method.

ProcessRecords() method will process all the records transferred from staging to target.

In the below example, we are updating the LedgerJournalTable with ExecutionID, which is used to display the status (error/ completed, etc ) based on execution id.

[ExtensionOf(tableStr(DataEntity))]
final public class DataEntity_Extension
{

  public static void postTargetProcess(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)

    {

        LedgerJournalEntityStaging staging;

        LedgerJournalTable ledgerJournalTableloc;

        select firstonly JournalBatchNumber,ExecutionId from staging

            where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

                && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId;

       

        if (staging.JournalBatchNumber)

        {

            ledgerJournalTableloc = LedgerJournalTable::find(staging.JournalBatchNumber);

            if (ledgerJournalTableloc.RecId && ledgerJournalTableloc.MessageId != staging.ExecutionId)

            {

                ttsbegin;

                ledgerJournalTableloc.selectForUpdate(true);

                ledgerJournalTableloc.MessageId = _dmfDefinitionGroupExecution.ExecutionId;

                ledgerJournalTableloc.update();

                ttscommit;

            }

        }

    }

}

 

Like postTargetProcess, we have a method called “PostGetStagingData” this method is used to update the data before it gets inserted into the staging table.

public static void postGetStagingData(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)

{

        //logic here

}

D365 FO DB restore in to cloud hosted Development environment using backup (.bacpac) file

 DB restore in to cloud hosted environment.

 

Import Database from UAT/Production to cloud-hosted or dev environment | D365 Finance and operations

 

To import a database to a developer environment after you've downloaded a database backup (.bacpac) file, you can begin the manual import operation on your Tier 1 environment. When you import the database, we recommend that you follow these guidelines:

 

·        Keep a copy of the existing AxDB database, so that you can revert to it later if needed.

·        Import the new database under a new name, such as AxDB_fromProd.

 

To ensure the best performance, copy the *.bacpac file to the local computer that you're importing from. Download sqlpackage .NET Core for Windows from Get sqlpackage .NET Core for Windows. Open a Command Prompt window, and run the following commands from the sqlpackage .NET Core folder.

 Note: 

Stop all the related dynamics services and w3 services.

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200  (old)

or 

SqlPackage /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"AdventureWorksLT" /TargetUser:"your_username" /TargetPassword:"your_password" /TargetTrustServerCertificate:True /SourceFile:"C:\AdventureWorksLT.bacpac" (new)

Eg: 

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\ERPREPROD.bacpac" /p:CommandTimeout=6200

 

·        tsn (target server name) – The name of the Microsoft SQL Server instance to import into.

·        tdn (target database name) – The name of the database to import into. The database should not already exist.

·        sf (source file) – The path and name of the file to import from.

Eg: 

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\DFDSPRODCOPY.bacpac" /p:CommandTimeout=6200

(https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15)

Update the database

Run the following SQL script against the imported database. This script adds back the users that you deleted from the source database and correctly links them to the SQL logins for this SQL Server instance. The script also turns change tracking back on. Remember to edit the final ALTER DATABASE statement so that it uses the name of your database.

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember 'db_owner', 'axdeployuser'

 

CREATE USER axdbadmin FROM LOGIN axdbadmin

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

 

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

 

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser

 

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

 

CREATE USER axdeployextuser FROM LOGIN axdeployextuser

 

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]

EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

 

UPDATE T1

SET T1.storageproviderid = 0

    , T1.accessinformation = ''

    , T1.modifiedby = 'Admin'

    , T1.modifieddatetime = getdate()

FROM docuvalue T1

WHERE T1.storageproviderid = 1 --Azure storage

 

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

GO

-- Begin Refresh Retail FullText Catalogs

DECLARE @RFTXNAME NVARCHAR(MAX);

DECLARE @RFTXSQL NVARCHAR(MAX);

DECLARE retail_ftx CURSOR FOR

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES

    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');

OPEN retail_ftx;

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

 

BEGIN TRY

    WHILE @@FETCH_STATUS = 0

    BEGIN

        PRINT 'Refreshing Full Text Index ' + @RFTXNAME;

        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';

        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';

        EXEC SP_EXECUTESQL @RFTXSQL;

        FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

    END

END TRY

BEGIN CATCH

    PRINT error_message()

END CATCH

 

CLOSE retail_ftx;

DEALLOCATE retail_ftx;

-- End Refresh Retail FullText Catalogs

 

--Begin create retail channel database record--

declare @ExpectedDatabaseName nvarchar(64) = 'Default';

declare @DefaultDataGroupRecId BIGINT;

declare @ExpectedDatabaseRecId BIGINT;

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)

BEGIN

               select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default';

               insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)

               values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0);

               select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName;

               insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)

               select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT

               inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID

        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0

END;

--End create retail channel database record

 

Turn on change tracking

If change tracking was turned on in the source database, be sure to turn it on in the newly provisioned database in the target environment. To turn on change tracking, use the ALTER DATABASE command.

ALTER DATABASE [your database name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);

Start to use the new database

To switch environments and use the new database, first stop the following services:

·        World Wide Web Publishing Service

·        Microsoft Dynamics 365 Unified Operations: Batch Management Service

·        Management Reporter 2012 Process Service

After these services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services.

 

Example

 

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_orig

GO

ALTER DATABASE AxDB_orig SET MULTI_USER;

GO

 ------------------------------------------------------------------------------------------------

USE master;

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AXDB MODIFY NAME = AXDB_Orig;

ALTER DATABASE AXDB_Orig SET MULTI_USER;

--------------------------------------------------------------------------------------------

 

MS reference :

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#import-the-database

  

Override An Existing Lookup Method: Chain Of Command D365 X++

 

Override An Existing Lookup Method: Chain Of Command

Here in this example, we will see, how we can override the form control lookup method using a chain of commands.

Let's take an example of the Purch order form and we will try to override the punchline item ID control lookup.

1. Step one create new Classes as shown below.

[ExtensionOf(FormStr(PurchTable))]

final class PurchTableItemIdLookup_Extension

{

   

}

2. Step two, create a new method with customized logic to override the standard lookup. I call this function "OverrideItemIdLookup" as shown below.

    public void overrideItemIdLookup(FormStringControl _formControl)

    {

        Query   qr = new Query();

        QueryBuildDataSource    qbdsInventTableExpanded;

        QueryBuildDataSource    qbdEcoResLS;

        SysTableLookup systablelookup = SysTableLookup::newParameters(tableNum(InventTable),_formControl);

        qbdsInventTableExpanded = qr.addDataSource(tableNum(InventTable));

        qbdEcoResLS = qr.dataSourceTable(tableNum(InventTable)).addDataSource(tableNum( EcoResProductLifecycleState))   ;

        qbdEcoResLS.addLink(fieldNum(InventTable, ProductLifeCycleStateId), fieldNum(EcoResProductLifecycleState, StateId));

        qbdEcoResLS.addRange(fieldNum(EcoResProductLifecycleState, ItemsHideDropDownPurch)).value(queryValue(NoYes::No));

       

        systablelookup.parmQuery(qr);

        systablelookup.addLookupfield(fieldNum(InventTable,ItemId));

        systablelookup.addLookupfield(fieldNum(InventTable,NameAlias));

        systablelookup.performFormLookup();

    }

3. step three, this is the Main point, as we have created a new method, the same is to be registered. for doing this, we need to override the Init method as shown below.

    public void init()

    {

        next init();

//<<controlname. -->PurchLine_ItemId

        PurchLine_ItemId.registerOverrideMethod(methodStr(FormDataObject,lookup), FormMethodStr(PurchTable, overrideItemIdLookup));

    }

Now build and run.. 

Calling REST API from AX 2012

As we know, JSON is not supported by AX 2012, and calling REST API is challenging.  The alternate way is to use XML webRequest below is the code, which calls the API and gets the response as a string. The code also reads the response and displays it in the info log.

 Here is the code to call REST API from AX 2012

static void Send_RestAPI(Args _args)

{

    System.IO.Stream streamstr,responsestr;

    System.IO.StreamWriter streamWriter;

    System.Net.HttpWebRequest request;

    System.Net.WebHeaderCollection headers;

    System.Net.HttpWebResponse response;

    System.IO.StreamReader reader;

    str wsResponse;

    str xml;

    str                                 xmlOut;

    //

    XmlDocument     xmlDocument;

    XmlNodeList     xmlScriptList;

    XmlNodeList     xmlResponseList;

    XmlElement      nodeScript;

    XmlElement      nodeResponse;

    XMLParseError   xmlError;

     // Define temporary variables

    str _xmlMsg;

    int i, j;

    ;

    new InteropPermission(InteropKind::ClrInterop).assert();

    try

    {

        // request url

        request = System.Net.WebRequest::Create('https://api.xxxxxx.net/1.0/Tokenxxx') as  System.Net.HttpWebRequest;

        // http method

        request.set_Method('Post');

        // headers

        request.set_ContentType("application/xml; charset=utf-8");

        // request body

         xml =

            "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +

            "<RequestToken xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +

            "<grant_type>asaasass</grant_type>" +

            "<username>asasadsdsdd</username>" +

            "<password>******</password>" +

            "</RequestToken>";

        streamstr = request.GetRequestStream();

        streamWriter = new System.IO.StreamWriter(streamstr);

        streamWriter.Write(xml);

        streamWriter.Flush();

        streamWriter.Close();

        streamWriter.Dispose();

        // response

        response = request.GetResponse();

        reader = new System.IO.StreamReader(response.GetResponseStream());

        wsResponse = reader.ReadToEnd();

       // --------------------- READ FROM XML-----------------

       xmlDocument             = XmlDocument::newXml(wsResponse);

         // Get the root element and its child nodes

       nodeScript = xmlDocument.getNamedElement("Tokenxx");

       xmlScriptList = nodeScript.childNodes();

       for(i=0; i < xmlScriptList.length(); i++)

        {

            nodeResponse = xmlScriptList.item(i);

            if (nodeResponse.nodeName() == 'rexxxToken')

            {

                 info(strFmt("rToken: %1", nodeResponse.text()));

            }

            if (nodeResponse.nodeName() == 'axxxtoken')

            {

                info(strFmt("axxxtoken: %1", nodeResponse.text()));

            }

            if (nodeResponse.nodeName() == 'expiresIn')

            {

                  info(strFmt("exxxIn: %1", nodeResponse.text()));

            }

            //xmlResponseList = nodeResponse.childNodes();

            //if(nodeResponse.selectSingleNode('refreshToken'))

          /*  if (i == 0)

                info(strFmt("refreshToken: %1", nodeResponse.text()));//innerText()));//text()));

            if (i == 1)

                 info(strFmt("access_token: %1", nodeResponse.text()));

            if (i == 2)

                 info(strFmt("expiresIn: %1", nodeResponse.text()));*/

        }

        // --------------------- READ FROM XML-----------------

        //

        reader.Close();

        //info(wsResponse);

    }

    catch (Exception::CLRError)

    {

        throw error(AifUtil::getClrErrorMessage());

    }

    CodeAccessPermission::revertAssert();

}

Get default dimension from GeneralJournalAccountEntry

Here we will see how to get the default Dimension from the table GeneralJournalAccountEntryEntity.

1. Add DimensionCombinationEntity as DS 

2. Add a relation with LedgerDimension and Recid as shown below.

3. Add the field Display value from DimensionCombination DS. This will give you the Default Dimension for GeneralJournalAccountEntryEntity.

 

Dynamic query ranges to run queries in Microsoft Dynamics AX

 Use the standard flexibility of the queries

The standard queries already support a level of flexibility with predefined functions. You can read more about that in this blog post: 

http://daxmusings.codecrib.com/2013/07/custom-query-range-functions-using.html

The class “SysQueryRangeUtil” contains several predefined methods which can be used to make your query more dynamic. The format is plain and simple: ( some code ), you only need the parentheses around the method.

Just to explain how this can be used, here are some samples:

Many other scenarios are possible. This second option gives you the ultimate flexibility to create dynamic queries.